Table name can not use the alias

[jinmarcus]

        $lists = $this->db->table('role as r')
                          ->select('r.name, u.username')
                          ->join('user as u', 'u.id = r.id')
                          ->where([
                              'r.status' => 1
                          ])
                          ->get()
                          ->getResult();

Table name can not use the alias
Wrong

print sql :

SELECT `hero_r`.`name`, `hero_u`.`username`
FROM `hero_role` as `r`
JOIN `hero_user` as `u` ON `hero_u`.`id` = `hero_r`.`id`
WHERE `hero_r`.`status` = 1

"hero_" is DBPrefix

I find system\Database\BaseConnection.php
in 1089 line
$this->qb_aliased_tables Should it be $this->QBAliasedTables ?

but I don't know how to go to fixed

[rrajesh011]

yes, $this->qb_aliased_tables should be renamed to $this->QBAliasedTables of BaseConnection.php in protectIdentifiers method line no 1028.
variable QBAliasedTables is defined in BaseBuilder.php but not accessibe in BaseConnection.php.

[jinmarcus]

How can I repair it?

[lonnieezell]

Doh! That's totally my fault, from when I refactored to allow unique instances of the query builder. I didn't even think of that. Since it's a holiday this week in the United States, I won't have any time to look into this until next week, but will dig in early next week, if no one else has submitted a PR to fix it just yet. If you don't hear from me by Wednesday next week be sure to ping me, since I've also got a paying side project eating up my available time right now.

[rrajesh011]

@lonnieezell as you said in above comment, it's just a reminder on this issue.

[lonnieezell]

Looking at the tests that feature is tested and should be working fine.

It looks like you're setting the table alias incorrectly - there shouldn't be any use of as in there. So it should be $this->db->table('role r') instead of $this->db->table('role as r').

[rrajesh011]

yes agree, but people use AS in their select or join query.

[lonnieezell]

And that would be correct sql for select or join but not for table aliases.

…

Sent from my iPhone

On Dec 2, 2017, at 6:36 AM, rrajesh011 ***@***.***> wrote: yes agree, but people use AS in their select or join query. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[jinmarcus]

@lonnieezell
i use

$this->db->table('role r')
     ->select('r.power')
     ->get()
     ->getResult();

but error

Unknown column 'r.power' in 'field list'	

it doesn't work

I modified BaseBuilder.php and BaseConnection.php

like this:
http://wx2.sinaimg.cn/large/82c9caaegy1fmp9mekf5cj21060wgak0.jpg

and it's ok, But it's not a good way to do it, but it can be solved.

I think you should have a better solution

[lonnieezell]

I was just looking at our tests and realized we don’t test aliases with live databases only against expected strings so I will need to check into that. What database are you using?

[jinmarcus]

I using mysql

[cenxun]

Perhaps we can determine whether there is table alias to add table prefix processing.
Maybe we can use an alias in the join operation

//BaseBuilder.php   The protectIdentifiers method adds the table prefix
    public function join($table, $cond, $type = '', $escape = null)
    {
	......//Omit some code
        $cond = ' ON ';
        for ($i = 0, $c = count($conditions); $i < $c; $i ++ )
        {
            $operator = $this->getOperator($conditions[$i]);
            $cond .= $joints[$i];
            $cond .= preg_match("/(\(*)?([\[\]\w\.'-]+)" . preg_quote($operator) . "(.*)/i", $conditions[$i], $match) ? $match[1] . $this->db->protectIdentifiers($match[2]) . $operator . $this->db->protectIdentifiers($match[3]) : $conditions[$i];
        }
        ......//Omit some code
    }

//BaseConnection.php
    public function protectIdentifiers($item, $prefixSingle = false, $protectIdentifiers = null, $fieldExists = true)
    {
        ......//Omit some code
        // Break the string apart if it contains periods, then insert the table prefix
        // in the correct location, assuming the period doesn't indicate that we're dealing
        // with an alias. While we're at it, we will escape the components
        if (strpos($item, '.') !== false)
        {
            $parts = explode('.', $item);

            ......//Omit some code

            // Is there a table prefix defined in the config file? If not, no need to do anything
            if ($this->DBPrefix !== '')
            {
                // We now add the table prefix based on some logic.
                // Do we have 4 segments (hostname.database.table.column)?
                // If so, we add the table prefix to the column name in the 3rd segment.
                if (isset($parts[3]))
                {
                    $i = 2;
                }
                // Do we have 3 segments (database.table.column)?
                // If so, we add the table prefix to the column name in 2nd position
                elseif (isset($parts[2]))
                {
                    $i = 1;
                }
                // Do we have 2 segments (table.column)?
                // If so, we add the table prefix to the column name in 1st segment
                else
                {
                    $i = 0;
                }

                // This flag is set when the supplied $item does not contain a field name.
                // This can happen when this function is being called from a JOIN.
                if ($fieldExists === false)
                {
                    $i ++;
                }

                // Verify table prefix and replace if necessary
                if ($this->swapPre !== '' && strpos($parts[$i], $this->swapPre) === 0)
                {
                    $parts[$i] = preg_replace('/^' . $this->swapPre . '(\S+?)/', $this->DBPrefix . '\\1', $parts[$i]);
                }
                // We only add the table prefix if it does not already exist
                elseif (strpos($parts[$i], $this->DBPrefix) !== 0)
                {
                    // !!! important //
                    //!!! add table prefix//
                    $parts[$i] = $this->DBPrefix . $parts[$i];
                }

                // Put the parts back together
                $item = implode('.', $parts);
            }
            ......//Omit some code
        return $item . $alias;
    }

[jinmarcus]

I think that only the $this->qb_aliased_tables in BaseConnection.php is needed to get the value

[jinmarcus]

@lonnieezell

I modified BaseBuilder.php and BaseConnection.php

like this:

http://wx4.sinaimg.cn/large/82c9caaegy1fnfzb1ghsjj211u125wl2.jpg

Is this the best solution?

[lonnieezell]

@jinmarcus Yup. You basically had it. I did it slightly differently, but your fix was right on. Thanks.