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