Columns disappear from select statement

Question

Columns disappear from select statement

branyas Jul '15

Created Jul '15	Last Reply Jul '15	Replies 1	Views 354	Votes 0

branyas 435

Jul '15

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`

Paulius
4.0k

Accepted
answer

Jul '15

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;
}

branyas · Answer 1 · 2015-07-13T00:10:20-07:00

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:

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;
}