13 Mar Rails 3 and 4 using joins, scopes and advanced queries in general (Using ActiveRecord ORM)
Hello Guys,
In this post I’m going to start putting all about queries in rails, so if you find any interesting query let me know and I can put it here, so let’s get right to it:
Something that I found really useful to understand how the joins works is using this image:
If you want to retrieve all the orders that contain more than 2 shipments (Using Spree models)
# this is using inner joins buy default from rails
Spree::Order.joins(:shipments).group("spree_shipments.order_id").having("count(spree_shipments.id) > 2")
the sql query generated is:
"SELECT `spree_orders`.* FROM `spree_orders` INNER JOIN `spree_shipments` ON `spree_shipments`.`order_id` = `spree_orders`.`id` GROUP BY spree_shipments.order_id HAVING count(spree_shipments.id) > 2
and using a left join:
Spree::Order.joins("left join spree_shipments ON spree_orders.id = spree_shipments.order_id").group("spree_shipments.order_id").having("count(spree_shipments.id) > 2")
the sql query generated is:
"SELECT `spree_orders`.* FROM `spree_orders` left join spree_shipments ON spree_orders.id = spree_shipments.order_id GROUP BY spree_shipments.order_id HAVING count(spree_shipments.id) > 2"
Group clause
All the following sql queries using group are based on the following citizen model:
Citizen seccion:integer
Basic usage:
Citizen.select('seccion').group('seccion')
Group count:
Citizen.select('seccion').group('seccion').count
SELECT COUNT("citizens"."seccion") AS count_seccion, seccion AS seccion FROM "citizens" GROUP BY "citizens"."seccion"
{43=>1117, 8=>1140, 11=>618, 80=>733, 16=>672, 39=>544, 54=>1989, 3=>1666, 47=>1279, 61=>1989, 87=>953, 67=>1059, 14=>948, 46=>930, 48=>1146, 17=>723, 83=>560, 28=>535, 36=>1473, 15=>527, 66=>1202, 77=>1004, 89=>913, 30=>493, 4=>727, 50=>894, 33=>1420, 73=>3066, 40=>610, 56=>459, 53=>1464, 62=>7902, 19=>996, 57=>803, 51=>850, 23=>2085, 31=>1039, 65=>1964, 35=>1362, 52=>1730, 76=>1490, 69=>3287, 20=>1029, 44=>544, 37=>2246, 85=>1114, 34=>1185, 82=>2289, 81=>2173, 25=>1127, 32=>1286, 12=>825, 58=>1105, 1=>4928, 10=>1229, 79=>1606, 26=>957, 42=>610, 90=>553, 18=>937, 59=>1250, 78=>135, 86=>577, 13=>1483, 49=>354, 22=>874, 63=>1229, 9=>936, 24=>4155, 91=>629, 70=>1184, 64=>1339, 45=>1711, 55=>1175, 27=>806, 68=>1478, 84=>1425, 88=>1025, 38=>945, 60=>7075, 74=>1884, 6=>1812, 71=>1070, 29=>729, 21=>1135, 2=>819, 72=>886, 41=>467, 75=>1332, 5=>569, 7=>830}
Iterate over the group hash(creating a value basic on group result)
Citizen.select('seccion').group('seccion').to_a.map {|citizen| Section.create!(number: citizen.seccion)}
Order ascendent or descendent using the group clause
If you give the count method a specific field, it will generate an output column with the name count_{column_grouped}
Citizen.group('seccion').order('count_id asc').count('id')
SELECT COUNT("citizens"."id") AS count_id, seccion AS seccion FROM "citizens" GROUP BY "citizens"."seccion" ORDER BY count_id asc
{78=>135, 49=>354, 56=>459, 41=>467, 30=>493, 15=>527, 28=>535, 39=>544, 44=>544, 90=>553, 83=>560, 5=>569, 86=>577, 42=>610, 40=>610, 11=>618, 91=>629, 16=>672, 17=>723, 4=>727, 29=>729, 80=>733, 57=>803, 27=>806, 2=>819, 12=>825, 7=>830, 51=>850, 22=>874, 72=>886, 50=>894, 89=>913, 46=>930, 9=>936, 18=>937, 38=>945, 14=>948, 87=>953, 26=>957, 19=>996, 77=>1004, 88=>1025, 20=>1029, 31=>1039, 67=>1059, 71=>1070, 58=>1105, 85=>1114, 43=>1117, 25=>1127, 21=>1135, 8=>1140, 48=>1146, 55=>1175, 70=>1184, 34=>1185, 66=>1202, 63=>1229, 10=>1229, 59=>1250, 47=>1279, 32=>1286, 75=>1332, 64=>1339, 35=>1362, 33=>1420, 84=>1425, 53=>1464, 36=>1473, 68=>1478, 13=>1483, 76=>1490, 79=>1606, 3=>1666, 45=>1711, 52=>1730, 6=>1812, 74=>1884, 65=>1964, 61=>1989, 54=>1989, 23=>2085, 81=>2173, 37=>2246, 82=>2289, 73=>3066, 69=>3287, 24=>4155, 1=>4928, 60=>7075, 62=>7902}
Group with having clause with Joins
user_ids = User.select('users.id').joins('left join addresses on addresses.initial_user_update = users.id').joins('inner join citizens on addresses.citizen_id = citizens.id').group('users.id').having('count(citizens.id) > 50').pluck(:id)
How to iterate over a joins with a where and group condition in Rails sql:
Section.joins('left join citizens on citizens.seccion = sections.name').where("citizens.simpatizante = true").group('sections.name').count('id').map{|element| "group by: #{element[0]} => results: #{element[1]}" }
The sql query would be
SELECT COUNT("sections"."id") AS count_id, sections.name AS sections_name FROM "sections" left join citizens on citizens.seccion = sections.name WHERE (citizens.simpatizante = true) GROUP BY sections.name
and the output result
["group by: 43 => results: 98", "group by: 8 => results: 124", "group by: 11 => results: 10", "group by: 80 => results: 32", "group by: 39 => results: 54", "group by: 16 => results: 37", "group by: 54 => results: 63", "group by: 3 => results: 190", "group by: 47 => results: 65", "group by: 61 => results: 137", "group by: 87 => results: 125", "group by: 67 => results: 41", "group by: 14 => results: 55", "group by: 46 => results: 32"]
Get work with payments and orders queries within Spree models
Get all the cheapest cost payment order
Spree::Order.select('spree_orders.number, sum(spree_payments.amount) as payment_total_order').joins(:payments).limit(50).group('spree_orders.id').order("spree_payments.amount DESC").first.payment_total_order.to_s
# sql generated
Spree::Order Load (118.2ms) SELECT spree_orders.number, sum(spree_payments.amount) as payment_total_order FROM `spree_orders` INNER JOIN `spree_payments` ON `spree_payments`.`order_id` = `spree_orders`.`id` GROUP BY spree_orders.id ORDER BY spree_payments.amount DESC LIMIT 1
=> "50412.5"
Get the most expensive cost payment orders:
Spree::Order.select('spree_orders.number, sum(spree_payments.amount) as payment_total_order').joins(:payments).limit(50).group('spree_orders.id').order("spree_payments.amount DESC").last.payment_total_order.to_s
## sql generated
SELECT spree_orders.number, sum(spree_payments.amount) as payment_total_order FROM `spree_orders` INNER JOIN `spree_payments` ON `spree_payments`.`order_id` = `spree_orders`.`id` GROUP BY spree_orders.id ORDER BY spree_payments.amount DESC LIMIT 50
=> "16598.0"
Get the last 50 orders that has a payments higher than $10,000
Spree::Order.select('spree_orders.payment_total, spree_orders.number, sum(spree_payments.amount) as payment_total_order').joins(:payments).limit(50).group('spree_orders.id').having('spree_orders.payment_total > 10000')
# sql generated
Spree::Order Load (123.2ms) SELECT spree_orders.payment_total, spree_orders.number, sum(spree_payments.amount) as payment_total_order FROM `spree_orders` INNER JOIN `spree_payments` ON `spree_payments`.`order_id` = `spree_orders`.`id` GROUP BY spree_orders.id HAVING spree_orders.payment_total > 10000 LIMIT 50
=> #<ActiveRecord::Relation [#<Spree::Order id: nil, number: "R756351982", payment_total: #<BigDecimal:7fb72e0c1b28,'0.104125E5',18(18)>>]>
Retrieves the orders that have a wrong payment_total(wrong updated or not updated):
Spree::Order.select('spree_orders.updated_at, spree_orders.payment_total, spree_orders.number, sum(spree_payments.amount) as payment_total_order').joins(:payments).where("spree_orders.state = 'complete'").where("spree_payments.state = 'completed'").group('spree_orders.id').having('payment_total_order != spree_orders.payment_total').order('spree_orders.updated_at ASC')
# sql generated:
SELECT spree_orders.updated_at, spree_orders.payment_total, spree_orders.number, sum(spree_payments.amount) as payment_total_order FROM `spree_orders` INNER JOIN `spree_payments` ON `spree_payments`.`order_id` = `spree_orders`.`id` WHERE (spree_orders.state = 'complete') AND (spree_payments.state = 'completed') GROUP BY spree_orders.id HAVING payment_total_order != spree_orders.payment_total ORDER BY spree_orders.updated_at ASC
Search the orders completed that have a balance due:
Spree::Order.select("(total - payment_total) as sum_total").where('completed_at is not NULL').where('state = "complete"').order('completed_at DESC').having("sum_total > 0")
Search how many orders have been completed and have a balance due:
Spree::Order.select("(total - payment_total) as sum_total").where('completed_at is not NULL').where('state = "complete"').order('completed_at DESC').having("sum_total > 0").to_a.count
group by and select count with custom attributes
object.inventory_units.select('variant_id, line_item_id, COUNT(variant_id) as tot_grouped').group('variant_id').map{ |inventory| {line_item_id: inventory.line_item_id,variant_id: inventory.variant_id,count: inventory.tot_grouped }}
SELECT variant_id, line_item_id, COUNT(variant_id) as tot_grouped FROM `spree_inventory_units` WHERE `spree_inventory_units`.`shipment_id` = 1054164745 GROUP BY variant_id [["shipment_id", 1054164745]] }}
No Comments