CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
PuckXY
PuckXY

Reputation: 59

How to efficiently retrieve groups of objects from activerecord with a single SQL query?

I have a table products which has a product_type_code column on it. What I'd like to do is retrieve different numbers of objects based on this column (eg.: 3 products with product_type_code = 'fridge', 6 products with product_type_code = 'car', 9 products with product_type_code = 'house', etc.).

I know I can do like this:

fridges = Product.where(product_type_code: 'fridge').limit(3)
houses = Product.where(product_type_code: 'house').limit(9)
[...]

And even create a scope like this:

# app/models/product.rb

scope :by_product_type_code, -> (material) { where(product_type_code: product_type_code) }

However, this is not efficient since I go to the database 3 times, if I'm not wrong. What I'd like to do is something like:

scope :by_product_type_code, -> (hash) { some_method(hash) }

where hash is: { fridge: 3, car: 6, house: 9 }

and get an ActiveRecord_Relation containing 3 fridges, 6 cars and 9 houses.

How can I do that efficiently?

Upvotes: 3

Views: 99

Answers (2)

engineersmnky
engineersmnky

Reputation: 29478

@SebastianPalma's answer is the best solution; however if you were looking for a more "railsy" fashion of generating this query you can use arel as follows:

scope :by_product_type_code, ->(h) { 
   products_table = self.arel_table
   query = h.map do |product_type,limit| 
     products_table.project(:id)
       .where(products_table[:product_type_code].eq(product_type))
       .take(limit)
   end.reduce do |scope1, scope2|
     Arel::Nodes::UnionAll.new(scope1,scope2)
   end 
   self.where(id: query)
end

This will result in the sub query being part of the where clause.

Or 

scope :by_product_type_code, ->(h) { 
   products_table = self.arel_table
   query = h.map do |product_type,limit| 
     products_table.project(Arel.star)
       .where(products_table[:product_type_code].eq(product_type))
       .take(limit)
   end.reduce do |scope1, scope2|
     Arel::Nodes::UnionAll.new(scope1,scope2)
   end 
   sub_query = Arel::Nodes::As.new(query,products_table)
   self.from(sub_query)
end

This will result in the subquery being the source of the data.

Upvotes: 2

Sebastián Palma
Sebastián Palma

Reputation: 33450

You can create a query using UNION ALL, which selects records having a specifc product_type_code and limit to use it with find_by_sql:

{ fridge: 3, car: 6, house: 9 }.map do |product_type_code, limit|
  "(SELECT *
   FROM products
   WHERE product_type_code = '#{product_type_code}'
   LIMIT #{limit})"
end.join(' UNION ALL ')

And you're gonna have a query like:

(SELECT * FROM products WHERE product_type_code = 'fridge'LIMIT 3)
UNION ALL
(SELECT * FROM products WHERE product_type_code = 'car'LIMIT 6)
UNION ALL
(SELECT * FROM products WHERE product_type_code = 'house'LIMIT 9)

Upvotes: 6

Related Questions