62

I have two models, Courier and Order.

I have the following query below:

active_couriers = Courier.
  available_courier_status.
  where(:service_region_id => @service_region.id).
  includes(:orders)

This query works, however, it pulls in all orders. I want to limit the orders to only orders for the day. So I added the following query where("orders.created_at >= ?", Time.zone.now.beginning_of_day).

active_couriers = Courier.
  available_courier_status.
  where(:service_region_id => @service_region.id).
  includes(:current_orders).
  includes(:orders).
  where("orders.created_at >= ?", Time.zone.now.beginning_of_day)

This give me the error:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "orders"

What am I doing incorrectly here?

Ruy Diaz
  • 3,084
  • 24
  • 36
Huy
  • 10,806
  • 13
  • 55
  • 99

3 Answers3

93

Hmm it looks like you're trying to include current_orders and include order. Are these the same tables with different conditions? This might be confuse active record. Also, I'm pretty sure it's wise to include the references method when referencing a joined table. Perhaps, try something like this:

active_couriers = Courier.includes(:orders)
  .available_courier_status
  .where(:service_region_id => @service_region.id)
  .where("orders.created_at >= ?", Time.zone.now.beginning_of_day)
  .references(:orders)
Tobias
  • 4,523
  • 2
  • 20
  • 40
Dane O'Connor
  • 75,180
  • 37
  • 119
  • 173
  • 5
    Thank you. I guess you need to add `references` – Huy Aug 02 '14 at 22:42
  • This was a life-saver. I had to use different names for the `includes` and the `references` clauses due to renaming of associations, but hell, it only took me 3 minutes after I found this fantastic answer. Thanks! – dimitarvp Nov 11 '14 at 21:52
  • 7
    Docs for references method: http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-references – Eben Geer Jun 23 '15 at 21:12
  • 1
    adding references fix the issue for me. Thanks a lot – Toontje Mar 04 '16 at 07:40
  • 1
    You can use here `Time.zone.today` instead of `Time.zone.now.beginning_of_day` – Hubert Olender Nov 14 '16 at 08:46
14

You can also use eager_load to provide the same exact behavior as includes + references does. It performs the same Left Outer Join on the table passed as an argument, but in a much cleaner manner.

Docs here: http://apidock.com/rails/v4.2.7/ActiveRecord/QueryMethods/eager_load

Per this example:

active_couriers = Courier.eager_load(:orders)
  .available_courier_status
  .where(:service_region_id => @service_region.id)
  .where("orders.created_at >= ?", Time.zone.now.beginning_of_day)
mattcongel
  • 346
  • 5
  • 13
-1

Make sure to provide .includes(:service_region) before filtering with where.

Kiryl Plyashkevich
  • 2,157
  • 19
  • 18