“Missing FROM-clause entry” when using nested merges with ActiveRecord

Question

I'm trying to use ActiveRecord queries to select devices belonging to specific users in my DB (devices are just models containing push notifications keys), regarding how many orders users made, or other things.

(In all models I removed non relevant or private stuff)

Here is my Device model:

class Device < ActiveRecord::Base
    belongs_to :user
end

User model:

class User < ActiveRecord::Base
  has_many :order
  has_one :device
end

Order model:

class Order < ActiveRecord::Base
  belongs_to :user
  belongs_to :order_status
  scope :valid, -> { joins(:order_status).merge(OrderStatus.valid) }
end

OrderStatus model:

class OrderStatus < ActiveRecord::Base
  has_many :orders
  scope :valid, -> { where.not(name: "processing") }
end

So, with all these, i'm trying to get Devices belongings to users who have made more than x valid orders. Here is where i am:

Device.all.joins(:user).merge(User.joins(:order).merge(Order.valid).group("users.id").having("count(orders.id) > ?", [NB_ORDERS]))

This generates this request:

: SELECT "devices".* FROM "devices" INNER JOIN "users" ON "users"."id"
= "devices"."user_id" LEFT OUTER JOIN "order_statuses" ON "order_statuses"."id" = "orders"."order_status_id" LEFT OUTER JOIN "orders" ON "orders"."user_id" = "users"."id" WHERE ("order_statuses"."name" != $1) GROUP BY users.id HAVING count(orders.id) > 10

And gives me this error:

Hirb Error: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "orders" LINE 1: ... JOIN "order_statuses" ON "order_statuses"."id" = "orders"."...

What i really don't understand is that when i just do

User.joins(:order).merge(Order.valid).group("users.id").having("count(orders.id) > ?", [NB_ORDERS])

It gives me the right result. It seems like nesting merges messes up with Postgres.

is there a way to solve this problem ? (I have a great amount of records so using SQL/ActiveRecord is essential)

Why you use Device.all? It returns Array not Relation. Try Device.joins(...) — Aleksey
I'm pretty sure it does return a relation. Anyways, when I remove .all, I get the exact same error — Shrolox
Can you try with Proc merge, i.e. Device.all.joins(:user).merge(-> { User.joins(:order)... })? — Aleksey
Yeah it works ! Thanks a lot :) Can you post this as an answer ? (Some explanations would be greatly appreciated) — Shrolox

Aleksey Aleksey · Accepted Answer · 2016-07-13T14:17:53

Try to use merge as Proc, like

Device.joins(:user).merge(-> { User.joins(:order)... })

Sorry, right not I cannot give any explanations because I don't know much about merge method.
I just read the docs and saw Proc option.

Maybe soon I will dive into the problem and be able to help you with something more than this.

“Missing FROM-clause entry” when using nested merges with ActiveRecord

1 Answers