0

I'm a novice when it comes to Postgres, and I realize similar questions have been asked before, however, I'm struggling to make this query work:

SELECT    p_search.id, count(kia.image_id) AS count
FROM      (SELECT id,
                  keyword,
                  to_tsvector('english',keyword) AS tskw
          FROM keyword) AS p_search
LEFT JOIN keyword_image_assoc AS kia 
ON        kia.keyword_id = p_search.id
WHERE     p_search.tskw @@ plainto_tsquery('english','resident life')
OR        p_search.tskw @@ plainto_tsquery('english','wofford')
GROUP BY  kia.image_id
ORDER BY  count desc

For context, I have three tables:

  • image which has an id as the primary key
  • keyword which has id as the primary key and keyword which is a string representing a keyword
  • keyword_image_assoc which has an id, image_id and keyword_id which is used to associate images and keywords canonically.

If I add p_search.id to the GROUP BY clause, the results aren't correct - count is always 1. Is there a way to group the results so the image_ids have the count of the number of matching keywords?

I'm looking for two columns count - the number of matching keywords and image_id the id of the corresponding image. Thanks!

  • Try GROUP BY p_search.id instead. (remove p_search.id) – Edgar Allan Bayron Jan 2 '18 at 5:57
  • @EdgarAllanBayron That does kind of the opposite that i need - it returns a count of images per keyword; i need keywords per image... – andrewniesen Jan 2 '18 at 6:50
  • Change p_search.id by kia.image_id – McNets Jan 2 '18 at 8:14
  • SELECT kia.image_id, count(*) AS count ... GROUP BY kia.image_id – ypercubeᵀᴹ Jan 2 '18 at 8:38
4

Have a look at Postgres docs about GROUP BY:

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

According to this you cannot reference a field on the SELECT statement if it doesn't appear on the GROUP BY clause or without using an aggregated function.

In your example, you're referencing p_search.id but it doesn't appear on the GROUP BY clause. You could try to change it by kia.image_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.