Join Multiple Tables with Active Records

Rails - Joining multiple tables

Company.joins(:price_movements,:goods_movements).where("goods_movement.date = price_movement.date")

Go through this link it has detailed explanation of how to use ActiveRecord

Rails Activerecord query for joining multiple tables

It should be like

Comment.joins(post: :user)

Rails ActiveRecord joins with multiple tables

if you don't to want change your structure:

Category.left_outer_joins(:offer_categories, :plum_cake_categories).where(offer_categories: { offer_id: eligible_offer_ids }).or(Category.left_outer_joins(:offer_categories, :plum_cake_categories).where(plum_cake_categories: { plum_cake_id: eligible_plum_cake_ids })).uniq

Rails - Active Record Join across 3 Tables

Assuming all of your models have the correct has_many and belongs_to associations defined, you can join in multiple tables by passing in a hash to the join method instead of just a symbol.

Vote.joins(lunch: :provider).select('lunches.date, providers.name, votes.*').where(lunch_id: 1)

More information about these can be found in the 'Using Array/Hash of Named Associations' portion of the rails query inferface documentation.

http://guides.rubyonrails.org/active_record_querying.html#using-array-hash-of-named-associations

Join multiple table with active storage in rails

When you execute

bill = Bill.joins(:product).select('bills.*, products.image a
s image, products.name as name')

It's actually trying to load data from image column which is in the product table. Active Record don't work that way.
When we write has_one_attached :image we are not binding it with product tables image column, it's declaring one association with active_storage_attachments table.

---

Here is explanation.

has_one_attached :image, which is a relation between product table and active_storage_attachments table.

When we run rails active_storage:install those migration get added into application.

so the actual relation is

class Bill
  belongs_to :product
end

and

class Product
  has_one :image
end

Note: (`image` for us to access, the real table name is `active_storage_attachments`)

Now the query you want is something like this to return image by joining the product.

=> bill = Bill.joins(:product).joins("INNER JOIN active_storage_attachments on active_storage_attachments.record_id = products.id and active_storage_attachments.record_type = 'Product'").select('bills.*, active_storage_attachments.* as image, products.name as name'))

=> bill.image
#<ActiveStorage::Attached::One:0x00007f911d8a2540
 @name="image",
 @record=
  #<Product:0x00007f911dadf448
   id: 1,
   ...

bill.image in above example will return the image record and it will look like this. But it won't execute a extra query to load the image so don't worry.

to access the image in frontend

<%= image_tag url_for(bill.image) %>

Here is the full guide of ActiveStorage https://edgeguides.rubyonrails.org/active_storage_overview.html#what-is-active-storage-questionmark

Join multiple tables with active records

You can do the following:

Inscription.includes(item1: { item2: :banner })

The relations names item1, item2 and banner need to match the names given to each relation.

If you want to set a where statement on this query, you can do:

scope = Inscription.includes(item1: { item2: :banner }) 
scope = scope.where(banner: { name: "MOTD: Hello World!" })
scope = scope.where(item2: { is_favorite: true })

---

Similar questions:

• Rails 4 scope to find parents with no children
• How to query a model based on attribute of another model which belongs to the first model?
• Rails active record querying association with 'exists'
• Rails 3, has_one / has_many with lambda condition

Activerecord, how to chain multiple condition on a join table

An AND clause won't really give you the result you want. What you want is an to use an OR clause and GROUP and HAVING:

f_id = ProductFeature.arel_table[:feature_id]
raw = ProductFeature.arel_table[:raw_value]
Product.joins(:product_features)
       .where(
          f_id.eq(124).and(raw.eq("Yes")).or(
            f_id.eq(12345).and(raw.eq("No"))
          )
        )
       .group("products.id")
       .having(Arel.star.count.eq(2))

This results in the following query:

SELECT "products".*
FROM   "products"
       INNER JOIN "product_features"
               ON "product_features"."product_id" = "products"."id"
WHERE  ( "product_features"."feature_id" = 123
         AND "product_features"."raw_value" = 'Yes'
          OR "product_features"."feature_id" = 12345
             AND "product_features"."raw_value" = 'No' )
GROUP  BY "products"."id"
HAVING ( count(*) = 2 )
LIMIT  ?

Which returns all products that have at least two matches in the join table.

You might want to use a JSON/JSONB column instead of a string column for the value storage. This will help you mitigate one of the biggest problems with the EAV pattern which is the headaches of typecasting everything into a string column.

On Postgres (and probably MySQL) you can use WHERE (columns) IN (values) to compose a simpler and more effective query:

class Product < ApplicationRecord
  has_many :product_features
  has_many :features, through: :product_features

  def self.search_by_features(*pairs)
    t =  ProductFeature.arel_table
    conditions = Arel::Nodes::In.new(
        Arel::Nodes::Grouping.new( [t[:feature_id], t[:raw_value]] ),
        pairs.map { |pair| Arel::Nodes::Grouping.new(
            pair.map { |value| Arel::Nodes.build_quoted(value) }
        )}
    )
    Product.joins(:product_features)
      .where(
        conditions
      ).group(:id)
      .having(Arel.star.count.eq(pairs.length))
  end
end

Usage:

Product.search_by_features([1, "Yes"], [2, "No"], [3, "Maybe"])

SQL query:

SELECT     "products".*
FROM       "products"
INNER JOIN "product_features"
ON         "product_features"."product_id" = "products"."id"
WHERE      
  ("product_features"."feature_id", "product_features"."raw_value")
  IN 
  ((1, 'Yes'),(2, 'No'),(3, 'Maybe'))
GROUP BY   "products"."id"
HAVING     ( COUNT(*) = 3) ) 
LIMIT $1 

---

Related Topics