Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Columns disappear from select statement

Hi all,

I have a problem joining tables with querybuilder. If I use the same column name in different tables, then only the last occurrence of the selected fields is keeped in the sql code. Aliasing column names is not a solution since they are can't be used in WHERE clause. Am I missing something, or this is a bug?

This is my controller:

    namespace Gmt\Controllers;

    use \Phalcon\Mvc\Model\Query\Builder;
    use Gmt\Models\FirstTable;
    use Gmt\Models\SecondTable;

    class TableJoinBugController extends ControllerBase
    {

        public function initialize()
        {
            parent::initialize();
        }

        public function indexAction()
        {
            $qb = $this->createBuilder();
            echo "phql is: ".$qb->getPhql()."<br>";
            echo "sql is: ".$qb->getQuery()->getSQL()['sql']."<br>";
        }

        private function createBuilder()
        {   
            $qb = new \Phalcon\Mvc\Model\Query\Builder();
            $qb->addFrom('Gmt\Models\FirstTable', 'f')        
                ->columns([
                    'f.idFirstTable',
                    's.idSecondTable',
                    'f.name',
                    's.name'
                ])
            ->join('Gmt\Models\SecondTable', 'f.secondId = s.idSecondTable', 's');

            return $qb;
        }

    }

The result is (formatted by hand to be more readable):

phql is: 
    SELECT  f.idFirstTable,  s.idSecondTable, f.name, s.name
    FROM [Gmt\Models\FirstTable] AS [f]                 
    JOIN [Gmt\Models\SecondTable] AS [s] ON f.secondId = s.idSecondTable

sql is: 
    SELECT  `f`.`idFirstTable` AS `idFirstTable`,
            `s`.`idSecondTable` AS `idSecondTable`,
            `s`.`name` AS `name` 
    FROM `first_table` AS `f` 
    INNER JOIN `second_table` AS `s` ON `f`.`secondId` = `s`.`idSecondTable`


4.0k
Accepted
answer

Try:

private function createBuilder()
{
    $qb = new \Phalcon\Mvc\Model\Query\Builder();
    $qb->addFrom('Gmt\Models\FirstTable', 'f')
        ->columns([
            'f.idFirstTable',
            's.idSecondTable',
            'f_name' => 'f.name',
            's_name' => 's.name'
        ])
        ->join('Gmt\Models\SecondTable', 'f.secondId = s.idSecondTable', 's');

    return $qb;
}


241

Thank you for your reply. I've aliased the columns, and did not use them in where condition. Anyway, silently removing fields from a select statement is an undesired behaviour, an exception would be a better solution in this case.

Try:

```php private function createBuilder() { $qb = new \Phalcon\Mvc\Model\Query\Builder(); $qb->addFrom('Gmt\Models\FirstTable', 'f') ->columns([ 'f.idFirstTable', 's.idSecondTable', 'fname' => 'f.name', 'sname' => 's.name' ]) ->join('Gmt\Models\SecondTable', 'f.secondId = s.idSecondTable', 's');

return $qb; } ```