PG::UndefinedTable: ERROR: missing FROM-clause entry for table when using joins and where
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?
Solution 1:
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)
Solution 2:
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)