Ruby Rails Active Record Query on two columns
ruby, ruby-on-rails, sql
Solution
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).
Problem
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.