Rails uniq Public Activity

Question

t = PublicActivity::Activity.arel_table

@activities = PublicActivity::Activity.where(
    (t[:trackable_type].eq("Lesson").and(t[:trackable_id].in(@my_lessons_ids)))
    .or(t[:trackable_type].eq("Post").and(t[:trackable_id].in(@my_posts_ids)))
    .or(t[:trackable_type].eq("WallPost").and(t[:trackable_id].in(@my_wallposts_ids)))
    .or(t[:trackable_type].eq("Comment").and(t[:trackable_id].in(@my_comments_ids)))
    .or(t[:trackable_type].eq("Course").and(t[:trackable_id].in(@my_courses_ids)))
    .or(t[:trackable_type].eq("Group").and(t[:trackable_id].in(@my_groups_ids)))
).order("id DESC").uniq{|a| "#{a.trackable_type}-#{a.trackable_id}"}

In my database I have duplicate entries for "Trackable_type"+"trackable_id" because if a user click on "update" many times I get many entries. So, I want only to get one of these. I tried with .uniq{|a| "#{a.trackable_type}-#{a.trackable_id}"}

but nothing happens.

Returned SQL QUERY:

"SELECT DISTINCT \"activities\".* FROM \"activities\" WHERE (((((\"activities\".\"trackable_type\" = 'Lesson' AND \"activities\".\"trackable_id\" IN (11, 39, 40, 16, 5, 3, 12, 9, 13, 19, 18, 37, 15, 23, 24, 29, 20, 10, 25, 26, 27, 17, 28, 22, 21, 30, 33, 35, 34, 36, 32, 31) OR \"activities\".\"trackable_type\" = 'Post' AND \"activities\".\"trackable_id\" IN (50, 49, 48, 47, 46, 45, 44, 43, 42, 41, 40, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 14, 13, 12, 10, 5)) OR \"activities\".\"trackable_type\" = 'WallPost' AND \"activities\".\"trackable_id\" IN (158, 157, 155, 154, 153, 152, 151, 132, 131, 130, 129, 128, 127, 126, 125, 124, 119, 118, 117, 116, 115, 114, 113, 111, 110, 109, 108, 107, 106, 105, 104, 103, 102, 101, 100, 99, 98, 93, 92, 91, 90, 87, 85, 84, 83, 82, 81, 80, 79, 78, 77, 76, 75, 74, 73, 72, 71, 70, 69, 68, 67, 66, 65, 64, 63, 62, 61, 60, 59, 58, 57, 56, 55, 54, 53, 52, 51, 50, 49, 48, 47, 46, 45, 44, 43, 42, 41, 39, 38, 37, 36, 35, 34, 33, 31, 30, 29, 28, 27, 26, 25, 24, 22, 20, 19, 17, 16, 15, 14, 13, 10, 7, 6, 5, 4)) OR \"activities\".\"trackable_type\" = 'Comment' AND \"activities\".\"trackable_id\" IN (100, 99, 98, 95, 83, 82, 81, 79, 78, 71, 70, 69, 68, 67, 65, 63, 62, 61, 60, 59, 58, 57, 56, 55, 52, 51, 50, 49, 48, 47, 46, 45, 44, 43, 42, 41, 40, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 14)) OR \"activities\".\"trackable_type\" = 'Course' AND \"activities\".\"trackable_id\" IN (8, 1, 5, 7)) OR \"activities\".\"trackable_type\" = 'Group' AND \"activities\".\"trackable_id\" IN (14, 11, 4, 5, 6, 3, 2, 1)) ORDER BY id DESC"

UPDATE:

if I try:

@activities = PublicActivity::Activity.select("distinct trackable_type, trackable_id").where(
        (t[:trackable_type].eq("Lesson").and(t[:trackable_id].in(@my_lessons_ids)))
        .or(t[:trackable_type].eq("Post").and(t[:trackable_id].in(@my_posts_ids)))
        .or(t[:trackable_type].eq("WallPost").and(t[:trackable_id].in(@my_wallposts_ids)))
        .or(t[:trackable_type].eq("Comment").and(t[:trackable_id].in(@my_comments_ids)))
        .or(t[:trackable_type].eq("Course").and(t[:trackable_id].in(@my_courses_ids)))
        .or(t[:trackable_type].eq("Group").and(t[:trackable_id].in(@my_groups_ids)))
    ).order("id DESC")

I get this: enter image description here

UPDATE 2

If I try

@activities = PublicActivity::Activity.select("DISTINCT ON(trackable_type, trackable_id) *")

I get

PG::SyntaxError: ERROR: syntax error at or near "ON" LINE 1: SELECT COUNT(DISTINCT ON(trackable_type, trackable_id) *) FR... ^ : SELECT COUNT(DISTINCT ON(trackable_type, trackable_id) *) FROM "activities"

@sparkle do this `PublicActivity.select("distinct trackable_type, trackable_id")`... — Arup Rakshit, May 25 '15 at 13:27

Arup Rakshit · Accepted Answer · 2015-05-25T13:43:05.623

6

Your query without arel will be :

PublicActivity::Activity.select("DISTINCT ON(trackable_type, trackable_id) *")
              .where("(trackable_type = ? and trackable_id IN (?))
                      or (trackable_type = ? and trackable_id IN (?))",
                      "Lesson", @my_lessons_ids,
                      "Post", @my_post_ids)

edited May 25 '15 at 13:43

answered May 25 '15 at 13:30

Arup Rakshit

116,827
30
260
317

PG::SyntaxError: ERROR: syntax error at or near "ON" LINE 1: SELECT COUNT(DISTINCT ON(trackable_type, trackable_id) *) FR... ^ : SELECT COUNT(DISTINCT ON(trackable_type, trackable_id) *) FROM "activities" WHERE ((trackable_type = 'Lesson' and trackable_id IN (11,39,40,16,5,3,12,9,13,19,18,37,15,23,24,29,20,10,25,26,27,17,28,22,21,30,33,35,34,36,32,31)) or (trackable_type = 'Post' and trackable_id IN (NULL))) – sparkle May 25 '15 at 13:37
I do this: PublicActivity::Activity.select("DISTINCT ON(trackable_type, trackable_id) *") maybe is something with "PublicActivity::Activity" ? – sparkle May 25 '15 at 13:43
Ok, the problem was with "kaminari". Do you a way to order by id DESC? – sparkle May 25 '15 at 13:47
PG::InvalidColumnReference: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions – sparkle May 25 '15 at 13:47
@sparkle Well.. It seems some default scope from your app was getting inserted. Read [this](http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT). Yes.. *The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.* – Arup Rakshit May 25 '15 at 13:50

Rails uniq Public Activity

1 Answers1