Rails 5.2 Polymorphic Association with polymorphic conditional

Question

MODELS:

class Car < ApplicationRecord
  belongs_to :user

  has_one  :listing, as: :listable
  has_one  :firm, as: :firmable
  has_one  :seller, as: :sellable
end

class Truck < ApplicationRecord
  belongs_to :user

  has_one  :listing, as: :listable
  has_one  :firm, as: :firmable
  has_one  :seller, as: :sellable
end

class Listing < ApplicationRecord
  belongs_to :listable, polymorphic: true
  has_many :favorites, dependent: :destroy
  has_many :users_who_favorited, through: :favorites, source: :user
end

And Assume that Car and Truck both have a user_id field....

Listing.includes(:listable) returns an eargerloaded listings AR relation.

However, I need to filter by the user_id, so I tried...

Listing.includes(:listable).where(user_id: 100)

but I get an error..

ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column listings.user_id does not exist) LINE 1: SELECT "listings".* FROM "listings" WHERE "listings"."user_...

As it seems to be looking up user_id on listings. However, I need to filter on listables table, so that would mean either the Car or Truck tables. However listable is defined.

I also tried:

Listing.includes(listable:[:user]).where('users.id = 100')

but I get...

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "users") LINE 1: SELECT "listings".* FROM "listings" WHERE (users.id = 100) ... ^ : SELECT "listings".* FROM "listings" WHERE (users.id = 100) LIMIT $1

UPDATE:

Then tried

class Listing < ApplicationRecord
  belongs_to :listable, polymorphic: true
  has_many :favorites, dependent: :destroy
  has_many :users_who_favorited, through: :favorites, source: :user

  belongs_to :car, -> { includes(:listable).where(listable: { listable_type: Car.to_s }) }, foreign_key: :listable_id
  belongs_to :truck, -> { includes(:listable).where(listable: { listable_type: Truck.to_s }) }, foreign_key: :listable_id

end

and tried Listing.includes(:car, :truck) but got..

ActiveRecord::ConfigurationError (Can't join 'Car' to association named 'listable'; perhaps you misspelled it?)

Therefore, I can not try the below until the above is working.

Listing.includes(:car, :truck).where(cars: { user_id: 1 }).or(Listing.includes(:car, :truck).where(trucks: { user_id: 1 }))

However, I can do Listing.includes(:listable) and it does work, it breaks when I add a conditional.

Interesting question. But since includes generates separate select query you will not be able to use the polymorphic table's column in the where clause. However, you cannot use joins either as you cannot eagerload polymorphic associations. My suggestion is you could write the SQL query and query using `ActiveRecord::Base.connection.execute(sql)` Would love to know if there is any other way. — Surya, Sep 05 '19 at 05:39
You need to create a listable concern for your model. In your concern has_one :listing, as: : listable, dependent: :destroy — Saif chaudhry, Sep 05 '19 at 07:44
@Surya, What would the SQL look like? I am having a hard time with it. — user2012677, Sep 05 '19 at 11:32
Have you tried Listing.includes(:users).where(users: [id: 10]) — Saif chaudhry, Sep 05 '19 at 12:15
The relation is user->car->listing. So there is no direct connection of listing to user, I updated my listing model to make that clearer — user2012677, Sep 05 '19 at 12:58

score 4 · Accepted Answer · edited Sep 30 '22 at 09:33

This is a very interesting question that I had for several months. Then I found a solution for it.

In your Listing model, in order to be able to include your polymorphic model, you'll need to tell your model that they are related.

class Car < ApplicationRecord
  belongs_to :user

  has_one  :listing, as: :listable
  has_one  :firm, as: :firmable
  has_one  :seller, as: :sellable
end

class Truck < ApplicationRecord
  belongs_to :user

  has_one  :listing, as: :listable
  has_one  :firm, as: :firmable
  has_one  :seller, as: :sellable
end

class Listing < ApplicationRecord
  belongs_to :listable, polymorphic: true
  has_many :favorites, dependent: :destroy
  has_many :users, through: :favorites

  #magic happens here
  belongs_to :car, -> { includes(:listings).where(listings: { listable_type: Car.to_s }) }, foreign_key: :listable_id
  belongs_to :truck, -> { includes(:listings).where(listings: { listable_type: Truck.to_s }) }, foreign_key: :listable_id

end

and now, you can simply do: Listing.includes(:car, :truck) and it will work perfectly :-)

For your case:

Listing.includes(:car, :truck).where(cars: { user_id: 1 }).or(Listing.includes(:car, :truck).where(trucks: { user_id: 1 }))

Why did you use Listable instead of Listing? I need to return listing objects, not the listable object. — user2012677, Sep 05 '19 at 11:50
Tried your solution, but got.. ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "listable") LINE 1: ...ON "cars"."id" = "listings"."listable_id" AND "listable"... — user2012677, Sep 05 '19 at 13:52
@user2012677 try it now, it should be `listings` instead of `listable` in the `where` condition — Roc Khalil, Sep 05 '19 at 15:21

score 0 · Answer 2 · answered Sep 05 '19 at 18:56

For those that may have as much trouble solving this as me...

My Final Solution:

    def left_join_listable(table_name, listable_type_value)
      "LEFT OUTER JOIN \"#{table_name}\" "\
      "ON \"#{table_name}\".\"id\" = \"listings\".\"listable_id\" "\
      "AND \"listings\".\"listable_type\" = #{listable_type_value}"
    end

   def left_join_users_on(*table_names)
      join = "LEFT OUTER JOIN \"users\" ON "
      conditionals = table_names.map {|table_name| "\"users\".\"id\" = \"#{table_name}\".\"user_id\"" }.join(" OR ")
      join + conditionals
    end

Listing.joins(left_join_listable('cars',"\'Car\'"))
       .joins(left_join_listable('trucks',"\'Trucks\'"))
       .joins(left_join_users_on('cars','trucks')
       .where(users.id = (?), 100)

Rails 5.2 Polymorphic Association with polymorphic conditional

MODELS:

UPDATE:

2 Answers2

My Final Solution:

Linked