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 '(GROUPCONCAT(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.tableid 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