Ruby Rails Active Record Query on two columns

Question

Lets say we have following table:
Employee
ID Name Sick_Leaves Casual_Leaves
1 John 4 8
2 Nancy 5 2
3 Matthew 2 9

Now if I want to get a list of all the Employess who have taken more sick leaves than casual, its straight forward in SQL:
Select * from Employee Where Sick_Leaves > Casual_Leave

Now considering that I have a Rails Active Record Model class defined for Employee, how can I execute the same query using model class itself ? I am getting stuck in how to define the WHERE clause. If it had been Sick_Leaves > 5 (or some fixed number), then its simple, but what now when we want to compare two columns itself ?

Any help will be appreciated.

Joe Kennedy · Accepted Answer · 2014-05-25 18:50:02Z

5

You can use the following ActiveRecord query:

Employee.where('sick_leaves > casual_leaves')

answered May 25, 2014 at 18:50

Joe Kennedy

9,4537 gold badges44 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

snow_leopard Over a year ago

Thanks. But I would prefer a solution in which I dont use a hand written SQL.

pdobb · Accepted Answer · 2014-05-25 19:13:34Z

4

Rails' interface to Active Relation doesn't have a simple syntax for expressing "greater than" (amongst other things). So you can either use hand-written SQL (as @JKen12579 suggested) or you can step down into the bowels of using ARel syntax. (It's weird at first but gives you the full power of SQL still.)

Employee.where(arel_table[:Sick_Leaves].gt(arel_table[:Casual_Leaves]))

I'd recommend this approach because it keeps you writing Ruby code, it alleviates issues with having to write the table name or not (e.g. "Do I need to use employees.<column>?"), and it is database agnostic (which may not be important here but maybe later... so get used to the syntax and never worry).

edited May 25, 2014 at 19:13

answered May 25, 2014 at 18:55

pdobb

18.1k5 gold badges63 silver badges77 bronze badges

6 Comments

snow_leopard Over a year ago

Ok, lets say if I relax the requirement of greater than, i.e. we need sick_leaves = casual_leaves, then can we do that through code rather than hard coding column names ?

pdobb Over a year ago

Would have to do the same as above but with .eq() instead of .gt().

pdobb Over a year ago

Am I missing something? This is doing it through code. Are you looking to do this in a Ruby array? Such as my_arary.select { |e| e.sick_leaves > e.casual_leaves }? Or are you wanting a SQL/database query still?

snow_leopard Over a year ago

Nope, this sounds good. I did not know about ARel syntax. May be this would help me here. Actually I want to do this on an INNER JOIN result where first column is from first table and second column to compare is from other table which is being joined.

pdobb Over a year ago

You may appreciate this talk from Rails Conf: youtube.com/watch?v=ShPAxNcLm3o

|

Collectives™ on Stack Overflow

Ruby Rails Active Record Query on two columns

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related