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 mit's not correct, how can I add to query special command?
Try this:
$query->columns("LEFT(GROUP_CONCAT(title ORDER BY title SEPARATOR ', '), 250) AS c");
$query->columns("COUNT(*) AS n");Tried, return:
SELECT m.LEFT(GROUP_CONCAT(title ORDER BY title SEPARATOR ', '), 250) AS c FROM table AS m
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?
Yes it was my fault, but right now I have another problem
Syntax error, unexpected token LEFT, near to '(GROUP_CONCAT(table2.title ORDER BY table2.position SEPARATOR ', '), 250)) AS rows FROM table_one AS table LEFT JOIN table_two AS table2 ON table.id = table2.menu_id GROUP BY table.id ORDER BY table.name ASC LIMIT 100', when parsing: SELECT table.id, table.name AS title, (LEFT(GROUP_CONCAT(table2.title ORDER BY table2.position SEPARATOR ', '), 250)) AS rows FROM table_one AS table LEFT JOIN table_two AS table2 ON table.id = table2.table_id GROUP BY table.id
but if I execute this query manually, I haven't any errors
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