Rails not ordering by date for this query

Question

I'm currently working on a website where a parent can reserve classes for their kids. In the parent's dashboard, I'd like to show a table of schedules and in every row there will be the child's name associated with a schedule. Unfortunately I have problem ordering the schedules by date in the parent's dashboard.

# view
<% @reservations.each do |reservation| %>
  <%= reservation.child.first_name %>
  <%= reservation.schedule.date %>
  <%= reservation.schedule.start_time %> - <%= reservation.schedule.end_time %>
  <%= link_to reservation.schedule.klass.name, schedule_path(reservation.schedule) %></td>
  <%= link_to reservation.schedule.partner.company, partner_path(reservation.schedule.partner) %></td>
  <%= reservation.schedule.city.name %></td>
<% end %>

# associations
User
  has_many :children, dependent: :destroy
  has_many :reservations
  has_many :schedules, through: :reservations
Child
  belongs_to :user
  has_many   :reservations, dependent: :destroy
  has_many   :schedules, through: :reservations
Reservation
  belongs_to :child
  belongs_to :schedule
  belongs_to :user
Schedule
  belongs_to :city
  belongs_to :partner
  belongs_to :activity
  belongs_to :klass
  has_many   :reservations
  has_many   :children, through: :reservations

I've got a default scope in my Schedule model that orders by date, start_time and end_time.

# Schedule model
default_scope { order(:date, :start_time, :end_time) }

This scope works in other tables, but not for this query:

# controller
@reservations = current_user.reservations.includes(:child, schedule: [:partner, :klass, :city])

It just refuses to order by date and time in the browser:

The log shows that the query for Schedule is indeed being ordered:

Reservation Load (0.3ms)  SELECT "reservations".* FROM "reservations" WHERE "reservations"."user_id" = $1  [["user_id", 1]]
Child Load (0.4ms)  SELECT "children".* FROM "children" WHERE "children"."id" IN (1, 2, 3)
Schedule Load (0.4ms)  SELECT "schedules".* FROM "schedules" WHERE "schedules"."id" IN (24, 12)  ORDER BY "schedules"."date" ASC, "schedules"."start_time" ASC, "schedules"."end_time" ASC
Partner Load (0.3ms)  SELECT "partners".* FROM "partners" WHERE "partners"."id" IN (2)
Klass Load (0.3ms)  SELECT "klasses".* FROM "klasses" WHERE "klasses"."id" IN (9, 17)
City Load (0.4ms)  SELECT "cities".* FROM "cities" WHERE "cities"."id" IN (28)

I could do this query in the controller instead:

@schedules = current_user.schedules

but then I'd have problem showing only a single child's name for each schedule since a class schedule can have many children associated for it.

Help?

It's not super-clear to me what all is happening; you're expecting the reservations to be ordered by the schedule? — Dave Newton, Aug 06 '15 at 16:31
@Pavan It seems that it orders using the Reservation's database id. — hsym, Aug 06 '15 at 16:33
I mean this `Schedule Load (0.4ms) SELECT "schedules".* FROM "schedules" WHERE "schedules"."id" IN (24, 12) ORDER BY "schedules"."date" ASC, "schedules"."start_time" ASC, "schedules"."end_time" ASC` — Pavan, Aug 06 '15 at 16:34
Well, you're not doing anything that affects reservation ordering; the schedules will be ordered *within* a reservation. — Dave Newton, Aug 06 '15 at 16:36
@DaveNewton So that means I need to use `@schedules = current_user.schedules` instead. But if I use that how do I show only a single child's name like in the image shown above? — hsym, Aug 06 '15 at 16:38
Well, more like it means you need to sort reservations in the way you want. I don't know how to do that anymore, but seems like it should be possible. It *certainly* is using Ruby alone, e.g., not relying on ActiveRecord. — Dave Newton, Aug 06 '15 at 16:47

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

You've defined an ordering on the default scope for Schedule, but you are pulling your relation from Reservation. As a result, the schedules are queried in the order specified, but since presumably you're looping through the @reservations in your view, you see their order and not their schedules's order. You can order @reservations by fields on the schedules table like so:

@reservations = @reservations.order("schedules.date ASC, schedules.start_time ASC, schedules.end_time ASC")

Also note that defining a default_scope in the model is generally discouraged (on SO and elsewhere) because its difficult to manage and can cause unexpected and unintuitive effects, especially down the road.

edited May 23 '17 at 12:14

Community

1
1

answered Aug 06 '15 at 17:31

eirikir

3,802
3
21
39

I tried your suggestion and it does work: `@reservations = current_user.reservations.includes(:child, schedule: [:partner, :klass, :city]).order("schedules.date ASC, schedules.start_time ASC, schedules.end_time ASC")` but in the logs it runs a really long SQL query for 1.4ms. Is this normal? – hsym Aug 07 '15 at 07:56
@i.am.noob 1.4ms seems pretty fast to me. If you want to make a faster query, you may need to add better indexes to your db. If you're not sure about this, its better to make a separate question tagged with your db – eirikir Aug 07 '15 at 16:44

Rails not ordering by date for this query

1 Answers1