Home > Back-end >  Rails postgresql left join using has_may order by created_at
Rails postgresql left join using has_may order by created_at

Time:09-21

I have two models named as 'customer' and 'membership'. customer can have many memberships. I want to display customers in order of their latest memberships cancelled date in ascending or descending order. My query looks like this

@customers = Customer
  .joins('LEFT JOIN memberships on memberships.customer_id = customers.id')
  .includes(:partner, :affiliate, active_membership: :plan)
  .group('customers.id')
  .order('memberships.created_at DESC, memberships.cancelled_at DESC')
  .page(params[:page])
  .per(100)

CodePudding user response:

class Customer
  has_many :memberships

  def self.order_by_latest_membership
     left_joins(:memberships) # no need to use a SQL string
       .group(:id) # you only need to group by customers.id
       .order(Membership.arel_table[:created_at].maximum.desc) # MAX(memberships.created_at) DESC
  end
end
Customer.order_by_latest_membership
        .includes(:partner, :affiliate, active_membership: :plan)
        .page(params[:page])
  • Related