SUPPORT THE SITE WITH A CLICK

Subscribe Rss:

SUPPORT THE SITE WITH A CLICK

Tuesday, March 31, 2009

ActiveRecord and SQL queries

Conditional Operators and ActiveRecord



Ruby on Rails is Model-View-Controller web-application framework. Rails uses ActiveRecord database abstraction classes to work with SQL tables. However, sometimes it is more convenient to work with plain SQL queries(without any model classes) for specific database operations.

Active Records accept constructor parameters either in a hash or as a block.Conditions can either be specified as a string, array, or hash representing the WHERE-part of an SQL statement. The array form is to be used when the condition input is tainted and requires sanitization. The string form can be used for statements that don‘t involve tainted data. The hash form works much like the array form, except only equality and range is possible.

For selecting a range of data then we can use SQL BETWEEN Opertator

Hash Method


Model.find(:all,:conditions=>{:column=>5..9})
QueryExecution in console will be like
Model Load (1.5ms) SELECT * FROM `models` WHERE (`models`.`column` BETWEEN 5 AND 9)

Array Method


Model.find(:all,:conditions=>{:column=>[5,6,7,8,9]})
QueryExecution in console will be like
SELECT * FROM `models` WHERE (`models`.`column` IN (5,6,7,8,9))

Similarly we use IN & NOT IN Operators
Person.find(:all, :conditions => [ "category IN (?)", categories], :limit => 50)
This will fetch the 50 data having only the particular category

Person.find(:all, :conditions => [ "category NOT IN (?)", categories], :limit => 50)
This will be the vice-versa of the previous one