We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

QueryBuilder Columns Alias

Hi,

How it's possible to set an alias in the query select using QueryBuilder ? For example :

SELECT id as tid, name as tname, date as tdate... FROM table1

This way

$robots = $this->modelsManager->createBuilder()
    ->columns(array("tid" => "id", "tname" => "name"))
    ->from('Robots')
    ->getQuery()
    ->execute();
edited Aug '15

Ok thanks :). And do you know why my SQL query doesn't work when i'm using innerjoin ?

  if ($this->request->isAjax()) {

$builder = $this->modelsManager->createBuilder() ->columns("l.id as id, l.agency_code as agency_code, l.date as date, l.name as lname, l.version as version, "CONCAT( s.name, ', ', s.city, ' (', s.zipcode, ') ' ) as fullname") ->from(array("l" => "Logs")) ->innerJoin("Stations", "l.agency_code = s.code", "s"); $dataTables = new DataTable(); $dataTables->fromBuilder($builder)->sendResponse(); }

Edit : I got this error :

Syntax error, unexpected token IDENTIFIER(agency_code), near to....

from($array) will load the Models listed in the array, try addFrom($model, $alias)

See docs... https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html

edited Aug '15

I should put the "l.agency_code = s.code"in the where condition ?

I have a new error :

Syntax error, unexpected token LIMIT, near to ' :AP0:', when parsing

Could you please post the full query builder code?

I should put the "l.agency_code = s.code"in the where condition ?

I have a new error :

Syntax error, unexpected token LIMIT, near to ' :AP0:', when parsing

edited Aug '15

I didn't change my code.

  if ($this->request->isAjax()) {

$builder = $this->modelsManager->createBuilder() ->columns("l.id as id, l.agency_code as agency_code, l.date as date, l.name as lname, l.version as version, "CONCAT( s.name, ', ', s.city, ' (', s.zipcode, ') ' ) as fullname") ->from(array("l" => "Logs")) ->innerJoin("Stations", "l.agency_code = s.code", "s"); $dataTables = new DataTable(); $dataTables->fromBuilder($builder)->sendResponse(); }

I'm trying to update phalcon to 2.0.6 to get this :

Fixed LIMIT/OFFSET SQL generation in Mvc\Model\Query\Builder

Ok it's fixed with the version 2.0.6 ! :). But now there is a problem with the alias. The QueryBuilder's library doesn't support the alias :(. If I use the sort or the filter on a column who has a SQL alias there is an error. I must modify the code's library.