lriyfg
Last Updated: February 25, 2016
·
1.825K
· aalvarado
Me

Select all fields from a model without strings

Arel is a library built into rails, it simplifies queries and adapts to different RDBMS.

I've been recently been working with queries that required some optimization without losing ActiveRecord's features.

Selecting from a sum for example it is normaly done with:

MyModel.sum(:field)

But what if you want to add a related model that you want to join in and get a sum from one of it's fields?

An option is to write the following:

MyModel.select( 'my_model.*, sum( my_other_model.field2 )').group( 'my_model.*')

Using Arel we can simplify and make this portable too!

MyModel.select( [ arel_table[ Arel.star ], 'sum(my_other_model.field2 ]) as field2_total').group( 'my_model.id' )

depending on your RDBMS you will get something along the way of:

SELECT `my_model`.*, sum( my_other_model.field2 ) as field2_total FROM `my_model` INNER JOIN `my_other_model` ON `my_model`.`id` = `my_other_model`.`id` GROUP BY `my_model`.`id` 

We can also add arel_table[primary_key] to the group clause:

MyModel.select([ arel_table[ Arel.star ], 'sum(my_other_model.field2' ]) as field2_total').group( arel_table[primary_key] )

And we should get the same output.

SELECT `my_model`.*, sum( my_other_model.field2 ) as field2_total FROM `my_model` INNER JOIN `my_other_model` ON `my_model`.`id` = `my_other_model`.`id` GROUP BY `my_model`.`id` 
Say Thanks
Respond