Rails sql query cheat sheet

Model name :  XXX, :pluralize=>xxxS

Find query:

XXX.find(1)  # => select * from XXX where id=1
     NOTE: it raise exception when no record found


 Better use this one:
XXX.find_by_id(1) #=> Works perfectly without raising andy exception

WHERE clause
XXX.find(:all , :conditions=>['id =?', 1] )

ORDER
XXX.find(:all, :order => "date")

WHERE clause many conditions
XXX.find(:all, :conditions => ['XXX_ID not in (?)', (@xxxS.empty? ? '' : @xxxS.map(&:id))])
Topic.where( 'forum_id not in (?)', (@forums.empty? ? '' : @forums.map(&:id)) ).all


find(:all, :select => "name").map(&:name)  # find all records, then map name attributes to an array


SUM

down vote

Grouping with multiple columns cannot be supported by rails. You have to use a regular find all: budgets = Budgets.find(:all,                        :select => "emptype_id, calendar_id, sum(budgets.actual_head) AS sum_actual_head",                        :group => "emptype_id, calendar_id")

Budgets.sum(:actual_head ,:group=>["emptype_id"," calendar_id"] )
'NOT IN' operations

Topic.all - @forums.map(&:id)

Join Operations

Topic.all(:joins => "left join forums on (forums.id = topics.forum_id and some_condition)", :conditions => "forums.id is null")

Deletion of  HABTM intersection table
  Menus.items.delete( Item.find_by_id(1) );                                      # ! IMPORTANT

Nested Select Statements

@answers = Answer.select([:id, :content, :points])

.where(question_id: @questions.pluck(:id))

This is the same as:

         SELECT id, content, points 

FROM answers 

WHERE question_id in (<question_ids array>)

 
References:
http://ar.rubyonrails.org/classes/ActiveRecord/Calculations/ClassMethods.html