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.

Answer 1

I'm probably missing something here, but why not set up Car and Truck as STI subclasses under something like Vehicle?

And why not turn the has_one associations for firm and seller into belongs_to instead, so that each firm or seller can have more than one listing?

# Parent class for both Car and Truck
class Vehicle < ApplicationRecord
  # Note that this table has a +type+ column which is used for Single Table Inheritance
  belongs_to :user
  has_many :listings, dependent: :destroy

  # You _might_ want to move these over to Listing so that over time the
  # same vehicle can be listed by different sellers (at different times)
  belongs_to  :firm
  belongs_to  :seller
end

class Car < Vehicle
end

class Truck < Vehicle
end

class Listing < ApplicationRecord
  belongs_to :vehicle
  has_many :favorites, dependent: :destroy
  has_many :users, through: :favorites
end

class User < ApplicationRecord
  has_many :vehicles
  has_many :favorites, dependent: :destroy
  has_many :listings, through: :favorites
end

# Associative table between User and Listing
class Favorite < ApplicationRecord
  belongs_to :user
  belongs_to :listing
end

With this kind of setup is there anything you can't query in regards to all of this?

Answer 2

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

Answer 3

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)