Doctrine SQL query: works on MySQL, on PostgreSQL won't
This is a query automatically generated by Taggable extension for Doctrine ORM.
SELECT t.id AS t__id, t.name AS t__name, COUNT(DISTINCT i.id) AS i__0,
(COUNT(DI开发者_JAVA百科STINCT i.id)) AS i__1
FROM taggable_tag t
LEFT JOIN cms__model__image_taggable_tag c ON (t.id = c.tag_id)
LEFT JOIN image i ON i.id = c.id
WHERE t.id IN
(SELECT doctrine_subquery_alias.id
FROM
(SELECT DISTINCT t2.id, (COUNT(DISTINCT i2.id)) AS i2__1
FROM taggable_tag t2
LEFT JOIN cms__model__image_taggable_tag c2 ON (t2.id = c2.tag_id)
LEFT JOIN image i2 ON i2.id = c2.id
GROUP BY t2.id HAVING i2__1 > 0
ORDER BY i2__1 DESC LIMIT 10) AS doctrine_subquery_alias)
GROUP BY t.id HAVING i__1 > 0
ORDER BY i__1 DESC
It works when using MySql, but won't work with PostgreSql.
I get: column i2__1 not found
or column i__2 not found
.
Are aliases disallowed when using COUNT(DISTINCT)?
How this query should look like to work on PostgreSql?You could try to replace i2__1
by COUNT(DISTINCT i2.id)
in the HAVING
-clause of the sub-select, or remove the parenthesis around COUNT(DISTINCT i2.id)
.
You might also have to add t__name
to the GROUP BY
-clause of the main select.
精彩评论