how to add uniq in a query

Question

in view I have this

<div>
    <% @readers.each do |reader| %>
        <% user_profile_url = user_url(reader.user.username) %>
        <div>
            <%= link_to user_profile_url do %>
                    <%= reader.user.username %>
            <% end %>
            <span>[<%= reader.created_at.to_date %>]</span>
        </div>
    <% end %>
</div>

in the books_controller i have this

  def readers_list
    @book = Book.find(params[:id])
    @readers = @book.downloads.order(created_at: :desc)
  end

I have this problem: If an user download twince a book, in this list I see the username twince. So, I know, I have to add uniq

I tried to add it at the end of the @readers, but it didn't work. How (and where) to add it?!

I also tried to make this @readers = @book.downloads.where(user_id: params[:user_id]).order(created_at: :desc), but it didn't work.

did you try this? `@book.downloads.order(created_at: :desc).distinct` — demir, Sep 22 '19 at 10:37
@demir yes. I tried @book.downloads.order(created_at: :desc).distinct and @book.downloads.order(created_at: :desc).uniq but it didn't work. I think because the "uniq-value" that i'm looking for is about the users, no the downloads — J.Luca, Sep 22 '19 at 10:41
`book_id` must be in `downloads Schema Information`. Not up to date? — demir, Sep 22 '19 at 10:58

Sebastián Palma · Accepted Answer · 2019-09-22T16:07:13.807

2

Use DISTINCT instead uniq. As you'll get everything and then do uniq, that's often considered as inefficient:

book
  .downloads
  .select('distinct on (downloads.user_id) downloads.*')
  .order('downloads.user_id, downloads.created_at DESC')

downloads was scoped to a JOIN clause, so the created_at column was present in both tables, hence the explicit reference in both select and order.

edited Sep 22 '19 at 16:07

answered Sep 22 '19 at 10:45

Sebastián Palma

32,692
6
40
59

The is no reason to us SQL in the `order` call here. I would go for `.order(:user_id, created_at: :desc)` to achieve the same result. – 3limin4t0r Sep 22 '19 at 23:34
Didn't you read _downloads was scoped to a JOIN clause, so the created_at column was present in both tables_ @3limin4t0r?, if you don't add the table name you'll have an ambiguity error. How are you gonna specify the table name with your `.order(:user_id, created_at: :desc)`? – Sebastián Palma Sep 23 '19 at 06:45
In the `book.downloads` context Rails knows to use the downloads table. `.order(:user_id, created_at: :desc)` is going to translate to `ORDER BY "downloads"."user_id", "downloads"."created_at" DESC`. I'm not sure where the JOIN comes into play. Since `book.downloads` produces `SELECT "downloads".* FROM "downloads" WHERE "downloads"."book_id" = «book id»`. – 3limin4t0r Sep 23 '19 at 10:01
I know what you mean @3limin4t0r. There's a horizontal line adding a message at the end of this solution explaining why I'm telling OP to reference both tables instead just using your proposed solution. – Sebastián Palma Sep 23 '19 at 11:04
https://chat.stackoverflow.com/rooms/199801/discussion-between-sebastian-palma-and-j-luca @3limin4t0r ;) – Sebastián Palma Sep 23 '19 at 11:09

score 0 · Answer 2 · answered Sep 22 '19 at 18:25

0

Another alternative is to use "Group By", In rails it can be done like this

@book.downloads.order(created_at: :desc).group(:user_id)

If you want to check What's faster, 'DISTINCT' or 'GROUP BY' then here is a good place to get started.

answered Sep 22 '19 at 18:25

Abdul Rehman

670
7
12

That's a MySQL benchmark. OP is using PostgreSQL (we got different errors while trying different solutions). To group without `id` will raise an error unless you've enabled `ONLY_FULL_GROUP_BY`. – Sebastián Palma Sep 23 '19 at 11:00

how to add uniq in a query

2 Answers2