How Do I Get Unique User Engagements?

I am trying to list unique user engagements with the engagement_type I used to have the following query. SELECT u.id, u.fname, u.lname FROM ( SELECT id, engagement_type FROM (

Solution 1:

Simpler with DISTINCT ON in PostgreSQL. For lack of definition I pick the first engagement_type according to its sort order:

SELECT u.id, u.fname, u.lname, b.engagement_type
FROM  (
   SELECTDISTINCTON (1)
          id, engagement_type
   FROM (
      SELECT user_id AS id, 'comment' AS engagement_typeFROM   comments
      WHERE  commentable_id = 48136AND    commentable_type = 'Video'

      UNION ALL
      SELECT user_id, 'like'FROM   likes
      WHERE  likeable_id = 48136AND    likeable_type = 'Video'
      ) a
   ORDERBY1, 2
   LIMIT  10
   ) b
JOIN users u USING (id);

Details, links and explanation:

• Select first row in each GROUP BY group?

If you want a unique list of all engagement_types:

SELECT id, string_agg(DISTINCT engagement_type, ', ') AS engagement_typesFROM (
      ...
      ) a
   GROUPBY1ORDERBY <whatever>
   LIMIT  10;

string_agg() need Postgres 9.0 or later. This form allows to order by whatever you want, while you'd need another subquery if you want ORDER BY to disagree with DISTINCT ON.

Solution 2:

If you want only one row you need to remove the engagement_type from the GROUP BY clause. however this then won't show you all the different engagement_type's.

If you want to list the engagement_types in one row without duplicating the user details then use the ARRAY_TO_STRING function. This contaminates the results to one line. So you could list the engagement types from the comments table as a comma separated list.