Sorting rows in postgres based on joined table - Rails

Question

I have the following situation

class Book
  has_many :book_publications
  has_many :publications, through: :book_publications
end

class BookPublication
  # book_id
  # publication_id
end

class Publication
  # date
end

Book1 | Publication1(2000)
Book1 | Publication2(2003)
Book2 | Publication3(2004)
Book2 | Publication4(1999)

I would like to sort the books based on the first publication date, not caring about the other dates. So for ascending I would have Book2(1999), Book1(2000), and descending Book1(2000), Book2(1999). The later years should not be counted in this query. Basically i would need to find the first year of a publication for each book, attach it somehow to the query and order by that. I also need to still be able to show the books that don't have publications. So i can't do a join to only get the rows that match.

max · Accepted Answer · 2020-09-23 19:31:30Z

1

class Book
  has_many :book_publications
  has_many :publications, through: :book_publications
  def self.order_by_publication_date
    left_joins(:publications)
      .group(:id)
      .order(Publication.arel_table[:date].minimum, :asc)
  end
end

If you want the date in the result set you can select it:

class Book < ApplicationRecord
  has_many :book_publications
  has_many :publications, through: :book_publications

  def self.order_by_publication_date
    left_joins(:publications)
      .select(
        arel_table[Arel.star],
        Publication.arel_table[:date].minimum.as('publication_date')
      )
      .group(:id)
      .order(:publication_date, :asc)
  end
end

answered Sep 23, 2020 at 19:31

max

103k15 gold badges113 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting rows in postgres based on joined table - Rails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related