Doctrine 1
  1. Doctrine 1
  2. DC-634

getPopularTags() query fails on Postgresql

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: Extensions
    • Labels:
      None
    • Environment:
      Windows, Postgresql

      Description

      When I run:
      Doctrine::getTable('TaggableTag')->getPopularTags();
      I get: ERROR: column "i2__1" does not exist

      The failing query on PostgreSql is:

      SELECT t.id AS t_id, t.name AS tname, COUNT(DISTINCT i.id) AS i_0,
      (COUNT(DISTINCT 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

      Seems that this just a PostgreSql issue.
      On Mysql it runs just fine.

      The corrected, working query is:

      SELECT t.id AS t_id, t.name AS tname, COUNT(DISTINCT i.id) AS i_0,
      (COUNT(DISTINCT 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 COUNT(DISTINCT i2.id) > 0
      ORDER BY i2__1 DESC LIMIT 10) AS doctrine_subquery_alias)
      GROUP BY t.id, t.name HAVING COUNT(DISTINCT i.id) > 0
      ORDER BY i__1 DESC

      For more details, please read the discussion here:
      http://stackoverflow.com/questions/2647537/doctrine-sql-query-works-on-mysql-on-postgresql-wont

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            admirau
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: