PG::UndefinedTable: ERROR: missing FROM-clause entry for table when using joins and where

Question

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?

Answer 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)