SQL

concat_with_MySQL.sql

UPDATE users SET email = CONCAT("test.",email)

counting.rb

ratings = self.ratings.select("count(*) as all_ratings, AVG(val) as average_rating").where("created_at > ?", Time.now - 12.months ).first

latest_rates.rb

  def self.get_list
    self.find_by_sql("SELECT e.currency, e.rate
      FROM exchange_rates as e 
      JOIN (
        SELECT currency, MAX(valid_from) AS max 
        FROM exchange_rates 
        GROUP BY currency
      ) m 
      ON (m.currency = e.currency AND m.max = valid_from)")
  end

Nested Query Example

sql = "SELECT DISTINCT p.id, p.name as original_name, p.thumbnail_file_name, p.sku,
 	pv.vendor_id, pv.price, pv.stock, pv.display_stock, pv.free_shipping,
  	pl.name, pl.short
  	FROM products as p
  	INNER JOIN product_vendors AS pv ON pv.product_id = p.id AND pv.vendor_id = ?
  	LEFT JOIN product_languages AS pl ON pl.product_id = p.id AND pl.locale = ?
  	WHERE p.id in (SELECT product_id FROM newsletter_products where newsletter_id = ?)
  	ORDER BY "

Nested Query Example 2

  def self.pvls(product_ids, vendor_id, locale="en")
    ids = product_ids.join(",")
    sql = "SELECT DISTINCT p.id, p.name as original_name, p.thumbnail_file_name, p.sku, 
      pv.vendor_id, pv.shipper_id, pv.is_consignment, pv.price,
      pv.stock, pv.display_stock, pv.free_shipping, pv.min_prep_time, pv.max_prep_time,
      pl.name, pl.short
      FROM products as p 
      INNER JOIN product_vendors AS pv ON pv.product_id = p.id AND pv.vendor_id = ? 
      LEFT JOIN product_languages AS pl ON pl.product_id = p.id AND pl.locale = ? 
      WHERE p.id in (#{ids})"
    Product.find_by_sql([sql, vendor_id, locale])
  end

order.rb

# Rails 3 Chained Scope Example 
  def self.matching(column, value)
    where(["#{column} like ?", "%#{value}%"])
  end  

  def self.order(col, dir="asc")
    logger.debug("=======hello! col = #{col} and dir = #{dir}")
    sql = case col
            when "id"; "id #{dir}"
            when "created_at"; "created_at #{dir}"
            when "name"; "name #{dir}"
            else "id desc"
          end
    order(sql)
  end  

  def self.search(params)
    params.delete_if {|key, value| value.blank? } 

    finder = scoped

    if params[:from_id] && params[:to_id] 
      finder = finder.where({:id => (params[:from_id]..params[:to_id])})
    elsif params[:from_id]
      finder = finder.where(:id => params[:from_id])
    end 
    finder = finder.where(:created_at => " >= #{Time.parse(params[:created_from]).to_s(:db)}") if params[:created_from]   
    finder = finder.where(:created_at => " <= #{Time.parse(params[:created_to]).to_s(:db)}") if params[:created_to]    
    finder = finder.matching('email', params[:email]) if params[:email].blank
    if params[:keyword].blank
      finder = finder.matching('company', params[:keyword])
      order = nil
    elsif !params[:order_by].blank? && !params[:order_dir].blank?
      order = "#{params[:order_by]} #{params[:order_dir]}"
    end
    return finder.paginate(:per_page => 1, :page => params[:page], :order => order )
  end  

update_using_subquery

update products set main_image_id = (
select id as image_id from images where
  owner_type = 'Product' and owner_id = products.id and main = true
  limit 1
)


update products set main_category_id = (
SELECT min(category_id) FROM category_products where product_id=products.id
group by product_id
)

update product_vendors set main_category_id = (
SELECT min(category_id) FROM category_product_vendors where product_vendor_id=product_vendors.id
group by product_vendor_id
)

DELETE cl FROM category_languages cl 
INNER JOIN (
  select max(id) AS id, count(*) AS count from category_languages group by category_id, locale
) duplicates 
ON (cl.id = duplicates.id AND duplicates.count > 1);

user.rb

  def self.full_search(params)
    params.delete_if {|key, value| value.blank? } 

    params.each {|k,v|
      logger.debug("#{k} -------------- #{v}")
      }

    finder = scoped

    if params[:from_id] && params[:to_id] 
      finder = finder.where({:id => (params[:from_id]..params[:to_id])})
    elsif params[:from_id]
      finder = finder.where(:id => params[:from_id])
    end 
    
    finder = finder.where("created_at >= '#{Time.parse(params[:created_from]).to_s(:db)}'") if params[:created_from]   
    finder = finder.where("created_at <= '#{Time.parse(params[:created_to]).to_s(:db)}'") if params[:created_to]    


    finder = finder.where("start_date >= '#{Time.parse(params[:start_date_from]).to_s(:db)}'") if params[:start_date_from]   
    finder = finder.where("start_date <= '#{Time.parse(params[:start_date_to]).to_s(:db)}'") if params[:start_date_to]    

    finder = finder.matching('email', params[:email]) if params[:email]
    if params[:keyword]
     finder = finder.where("(
      email like ? 
      OR first_name like ? 
      OR last_name like ?
      )", params[:keyword], params[:keyword], params[:keyword])      
      order = nil
    elsif params[:order_by] && params[:order_dir]
      order = "#{params[:order_by]} #{params[:order_dir]}"
    else
      order = "id DESC"
    end
    return finder.paginate(:per_page => 30, :page => params[:page], :order => order )
  end