Distinct Join Rails

Rails - Distinct ON after a join

Use a subquery and add a different ORDER BY clause in the outer SELECT:

SELECT *
FROM  (
   SELECT DISTINCT ON (pr.id)
          pu.updated_at, pr.*
   FROM   Product pr
   JOIN   Purchases pu ON pu.product_id = pr.id  -- guessing
   ORDER  BY pr.id, pu.updated_at DESC NULLS LAST
   ) sub
ORDER  BY updated_at DESC NULLS LAST;

Details for DISTINCT ON:

• Select first row in each GROUP BY group?

Or some other query technique:

• Optimize GROUP BY query to retrieve latest record per user

But if all you need from Purchases is updated_at, you can get this cheaper with a simple aggregate in a subquery before you join:

SELECT *
FROM   Product pr
JOIN  (
   SELECT product_id, max(updated_at) AS updated_at
   FROM   Purchases 
   GROUP  BY 1
   ) pu ON pu.product_id = pr.id  -- guessing
ORDER  BY pu.updated_at DESC NULLS LAST;

About NULLS LAST:

• PostgreSQL sort by datetime asc, null first?

Or even simpler, but not as fast while retrieving all rows:

SELECT pr.*, max(updated_at) AS updated_at
FROM   Product pr
JOIN   Purchases pu ON pu.product_id = pr.id
GROUP  BY pr.id  -- must be primary key
ORDER  BY 2 DESC NULLS LAST;

Product.id needs to be defined as primary key for this to work. Details:

• PostgreSQL - GROUP BY clause
• Return a grouped list with occurrences using Rails and PostgreSQL

If you fetch only a small selection (with a WHERE clause restricting to just one or a few pr.id for instance), this will be faster.

Distinct Records with joins and order

Try this:

User.select('users.*,MAX(donations.created_at) as most_recent_donation').
  joins(:donations).order('most_recent_donation desc').group('users.id')

I suppose an user has many donations, this would select the most recent created donation and would select distinct users filtering by their id.

I have not tested this though.

Get distinct rows using inner join

Event
  .joins(:picks)
  .where(picks: {result: nil, created_at: 5.days.ago..Time.now, league_id: 1})
  .group("events.id")

Distinct Join Rails

Try using :include instead of :joins and you should not see any more duplicate video results.

Also, you should be using a lambda in your named_scope, otherwise Time.now will be cached the first time you use it and you'll start getting incorrect results.

named_scope :scheduled_in_future, lambda {
  { 
    :include => :events,
    :conditions => ["event.scheduled_start > ? AND event.status = ?", Time.now.to_i, 'PENDING']
  }
}

Rails: remove duplicates after ordering a join table

In

Book.joins(:reviews).order('reviews.created_at DESC').distinct

you're trying to select distinct bookings from the join table of books and reviews, and then order this list of distinct bookings according to the reviews.created_at time. The SQL would be like this:

SELECT DISTINCT "books"."id" FROM "books" INNE JOIN "reviews" ON "reviews"."book_id" = "books"."id" ORDER BY reviews.created_at

There is a good reason why this is not allowed. Because the results are indeterminate. Imagine you have 100 reviews for one book. In the join table, you'll have 100 rows of this book with all different reviews. When you select a distinct list, you end up with one row of this book. This could be any one of the 100 in the join table. Then you order this based on the created_at of this review. As the review could be any one of the 100, the order could be different every time.

This would be perfectly fine:

Book.joins(:reviews).order('books.id DESC').distinct

Because it doesn't matter which of the 100 rows it picks for that book, the books.id is the same.

Back to your problem. Seems you're trying to get the 5 books with the most recent reviews. I don't see a simple way to do it but here's my solution:

res = Review.group("book_id").maximum("created_at") # {book_id => create_at}, each book with its most recent review time
arr = res.to_a.sort { |a,b| b[1]<=>a[1] } #array sorted by created_at in desc order
arr.map{ |n| n[0] }.take(5)  #top 5 books' ids with most recent reviews

Ruby on Rails 4 count distinct with inner join

After doing long chat, we found the below query to work :

self.member
    .engines(:reload)
    .count("DISTINCT engine_code")

Returns distinct record in a joins query - Rails 4

First of all, I believe your model is "An Order has many OrderStatusDetail". So that is the reason why you have several different name in your result.

So you can modify the query like this:

    @orders = Order.joins(order_status_details: :order_status)
                   .order('id DESC, order_status_details.created_at DESC')
                   .where('order_status_details.id IN (SELECT MAX(id) FROM order_status_details GROUP BY order_id)')
                   .select("orders.id, order_status_details.status_id, order_statuses.name, order_status_details.created_at")

Ideally, the where condition is used for selecting just the expected id of order_status_details, I use min_id for example, you can modify it as needed

---

Related Topics