CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Dmitry
Dmitry

Reputation: 581

ActiveRecord order nested items

I have models Ticket and Service

class Ticket < ApplicationRecord
  belongs_to :medic
  has_and_belongs_to_many :services

  validates_presence_of :services
end

class Service < ApplicationRecord
  has_and_belongs_to_many :tickets
end

and Service model has field code and name. So services table has records like

id |  code  | name 
1  |  5.1   | value 1
2  |  5.2   | value 2
3  |  6.1   | value 3
and so on

Is it possible to make query to get Tickets ordered by name including Services ordered by code (for example)?

Ticket.includes(:services, :medic)
        .order(date: :desc, created_at: :desc)
        .where(where_conditions)    (works fine, but without services order)

I did smth like this using Sequelize but don't know how it should be done using ActiveRecord. I've tried

Ticket.includes(:services, :medic)
        .order(date: :desc, created_at: :desc, "services.code": :desc)
        .distinct
        .where(where_conditions)

but got 

ActionView::Template::Error (SQLite3::SQLException: no such column: tickets.services.code: SELECT DISTINCT "tickets".* FROM "tickets" WHERE "tickets"."date" BETWEEN ? AND ? AND "tickets"."enabled" = ? ORDER BY "tickets"."date" DESC, "tickets"."created_at" DESC, "tickets"."services.code" DESC):

Upvotes: 0

Views: 233

Answers (2)

Smek
Smek

Reputation: 1208

For a more 'api-using' solutions you can use Arel:

ticket = Ticket.arel_table
service = Service.arel_table

Ticket.includes(:services, :medic).references(:services)
      .order(ticket[:date].desc, ticket[:created_at].desc, service[:code].desc)
      .distinct
      .where(where_condition)

Or:

Ticket.includes(:services, :medic).references(:services)
      .order(Ticket.arel_table[:date].desc, Ticket.arel_table[:created_at].desc, Service.arel_table[:code].desc)
      .distinct
      .where(where_condition)

Upvotes: 1

Marek Lipka
Marek Lipka

Reputation: 51151

You can pass exact order clause to into the ActiveRecord order method by string:

Ticket.includes(:services, :medic).references(:services)
      .order('tickets.date desc, tickets.created_at desc, services.code desc')
      .distinct
      .where(where_condition)

Note that I also added references(:services) to notify ActiveRecord that you want to put some condition or other clause on this table, otherwise ActiveRecord by default creates separate DB query, without join.

Upvotes: 1

Related Questions