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.

How add special query column to query builder

I'm trying to add to a query object a new not standard column:

$query->columns(["c" => "LEFT(GROUP_CONCAT(title ORDER BY title SEPARATOR ', '), 250)"]);

$query->getPhql() returns

SELECT m.LEFT(GROUP_CONCAT(title SEPARATOR ', '), 250) AS c FROM table AS m'

or

$query->columns(["n" => "COUNT(*)"]);

$query->getPhql() returns

SELECT m.COUNT(*) AS n FROM table AS m

it's not correct, how can I add to query special command?

edited Mar '14

Try this:

$query->columns("LEFT(GROUP_CONCAT(title ORDER BY title SEPARATOR ', '), 250) AS c");
$query->columns("COUNT(*) AS n");
edited Mar '14

Tried, return:

> SELECT m.LEFT(GROUP_CONCAT(title ORDER BY title SEPARATOR ', '), 250) AS c FROM table AS m



2.6k
Accepted
answer
edited Mar '14

I'm using Phalcon\Mvc\Model\Query\Builder with a call to AVG and i have no issues:

$query = new Phalcon\Mvc\Model\Query\Builder();
$query->addFrom("UtenteValutazioneSingola"); //works even if i add an alias (as second parameter)
$query->columns("AVG(valutazione) media");
$query->where("valutazione_id = ?1", array(1 => $this->id));
$query->groupBy("valutazione_id");
return $query->getQuery()->execute()->getFirst()->media;

Can you show me the complete query build code?

edited Mar '14

Yes it was my fault, but right now I have another problem

> Syntax error, unexpected token LEFT, near to '(GROUP%5FCONCAT(table2.title ORDER BY table2.position SEPARATOR ', '), 250)) AS rows FROM tableone AS table LEFT JOIN tabletwo AS table2 ON table.id = table2.menuid GROUP BY table.id ORDER BY table.name ASC LIMIT 100', when parsing: SELECT table.id, table.name AS title, (LEFT(GROUPCONCAT(table2.title ORDER BY table2.position SEPARATOR ', '), 250)) AS rows FROM tableone AS table LEFT JOIN tabletwo AS table2 ON table.id = table2.table_id GROUP BY table.id

but if I execute this query manually, I haven't any errors

edited Mar '14

TABLE is a reserved word in sql language, try to use another alias for your table (or is this an example?)

>Table

It my query table alias for this topic, in real query I'm using another alias of course