How do I get unique user engagements?

Question

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 (
    SELECT user_id AS id, 'comment'
    FROM comments WHERE commentable_id = 48136 AND commentable_type = 'Video'
    UNION ALL
    SELECT user_id AS id, 'like'
    FROM likes WHERE likeable_id = 48136 AND likeable_type = 'Video'
  ) AS a
  GROUP BY id
  LIMIT 10
) b JOIN users u USING (id);

Returns:

id        | fname      | lname
------------------------------
1         | joe        | abc
2         | sarah      | qer
3         | megan      | tryey
4         | john       | vdfa

Which is fine. Now, I want to include the engagment type. I've come up with this:

SELECT u.id, u.fname, u.lname, engagement_type FROM (
  SELECT id, engagement_type FROM (
    SELECT user_id AS id, 'comment' AS engagement_type
    FROM comments WHERE commentable_id = 48136 AND commentable_type = 'Video'
    UNION ALL
    SELECT user_id AS id, 'like' AS engagement_type FROM likes
    WHERE likeable_id = 48136 AND likeable_type = 'Video'
  ) AS a
  GROUP BY id, engagement_type
  LIMIT 10
) b JOIN users u USING (id);

Which now returns:

id        | fname      | lname    | engagement_type
---------------------------------------------------
1         | joe        | abc      | comment
2         | sarah      | qer      | like
3         | megan      | tryey    | like
4         | john       | vdfa     | like
1         | joe        | abc      | like     
3         | megan      | tryey    | comment

The only problem with above. The results are not unique anymore. As you can see, Joe and Megan have 2 entries.

Any idea how I can get this to work?

try removing `engagement_type` from `GROUP BY` clause and check if meets your needs. — जलजनक, Mar 03 '13 at 14:44
however, `joe` had two unique engagements viz, `comment` & `like`. So did `megan`. — जलजनक, Mar 03 '13 at 14:47
Depends on what you want to do. Do you want to display only one engagement_type or list like "comment, like"? — Jakub Kania, Mar 03 '13 at 14:49
Your b subquery is grouped `BY id, engagement_type`, that's why you're getting more than one row per user. You could change the LIMIT to 1 to fudge it to only one row, but which one do you really want (as Jakub says)? — AjV Jsy, Mar 03 '13 at 15:35
You failed to define what you want to show for `engagement_type` if there are multiple. — Erwin Brandstetter, Mar 03 '13 at 18:22
If you want to put all the distinct engagement types on the same single line per user, you probably want to take a look at this question: [Postgresql GROUP_CONCAT equivalent?](http://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent) — Andriy M, Mar 03 '13 at 18:31

score 1 · Accepted Answer · edited May 23 '17 at 12:06

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  (
   SELECT DISTINCT ON (1)
          id, engagement_type
   FROM (
      SELECT user_id AS id, 'comment' AS engagement_type
      FROM   comments
      WHERE  commentable_id = 48136
      AND    commentable_type = 'Video'

      UNION ALL
      SELECT user_id, 'like'
      FROM   likes
      WHERE  likeable_id = 48136 
      AND    likeable_type = 'Video'
      ) a
   ORDER  BY 1, 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_types
   FROM (
      ...
      ) a
   GROUP  BY 1
   ORDER  BY <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.

edited May 23 '17 at 12:06

Community

1
1

answered Mar 03 '13 at 18:28

Erwin Brandstetter

605,456
145
1,078
1,228

Very nice solution. Appreciate it! – Christian Fazzini Mar 03 '13 at 20:04
Just one thing how does the `ORDER BY` work here? What if I wanted to order by most recent engagements. How would I go about that? – Christian Fazzini Mar 03 '13 at 20:28
@ChristianFazzini: Follow the link. I wrote a detailed explanation at the related answer that should cover it. Start by defining "most recent". There is nothing in the question to cover that. – Erwin Brandstetter Mar 03 '13 at 20:40
"Most recent", sorted by the newest engagements. Ideally, via the `created_at` column. However, I'm a bit concerned. Since the `created_at` column is not indexed. – Christian Fazzini Mar 05 '13 at 11:04

score 0 · Answer 2 · answered Mar 03 '13 at 16:08

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.

How do I get unique user engagements?

2 Answers2

Linked