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();


12.6k
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.phalconphp.com/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html



12.6k
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



12.6k
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



12.6k

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.