Rails_ActiveRecord_find方法_拾疑_整理_搜集

简而言之：拼sql语句的方法和细节。

public class method find(*args) in ActiveRecord::Base::self in active_record/base.rb

另外可以看看这个帖子http://www.iteye.com/topic/135499的回复中提到的插件：criteria_query，看着好用：

http://agilewebdevelopment.com/plugins/criteria_query 

 

 

 

 

#joins

:joins -

#1 Either an SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id" (rarely needed),

这样的话，joins后面的string字符串会完全跟在select * from model_xxx #{joins 字符串} where ... ... 

这样的话，如果写别名的话，根据拼sql规则，需要下面这样写，在joins处先写别名：

Deal.all(:limit => 1, :joins=>"as d join areas as s on d.city_id = s.id ", :select => "d.*, s.* ", :conditions => ["s.id < 10 and d.id < 100"])

 

#2 named associations in the same form used for the :include option, which will perform an INNER JOIN on the associated table(s),

等价于include的用法，也就是说，这样的关联用法，必须是在model中声明了的关联，如belongs_to xxx || include yyy

 

#3 or an array containing a mixture of both strings and named associations.

等价于#2，只不过有多个关联时，用数组就可以了

 

#4 注意=>If the value is a string, then the records will be returned read-only since they will have attributes that do not correspond to the table's columns. Pass :readonly => false to override.

 

#5 和include的区别

看看同样的表结构下的例子，更详细的还是要去看源码或实验：

Deal.all(:limit => 1, :joins=>:concern_deal, :conditions => ["deal_id < 10 and concern_deals.user_id < 1000"])

SELECT `deals`.* FROM `deals` INNER JOIN `concern_deals` ON concern_deals.deal_id = deals.id WHERE (deal_id < 10 and concern_deals.user_id < 1000) LIMIT 1

 

Deal.all(:limit => 1, :include=>:concern_deal, :conditions => ["deal_id < 10 and concern_deals.user_id < 1000"])

SELECT DISTINCT `deals`.id FROM `deals` LEFT OUTER JOIN `concern_deals` ON concern_deals.deal_id = deals.id WHERE (deal_id < 10 and concern_deals.user_id < 1000) LIMIT 1

 

#from

:from - By default, this is the table name of the class, but can be changed to an alternate table name (or even the name of a database view)

也就是说，可以像hibernate那样，model不一定对应表。

这个用find_by_sql也可以实现，只不过这种用法更好看一些～

例子：

 

>> ShopClean.first(:select => "id, shop_name, city_id")

=> #<ShopClean id: 1, shop_name: "上海正善食品有限公司", city_id: 0>

 

>> ShopClean.first(:select => "id, shop_name, city_id", :from => "shops")

=> #<ShopClean id: 1, shop_name: "品尚豆捞 ", city_id: 1>

>> ShopClean.find_by_sql("SELECT id, shop_name, city_id FROM shops LIMIT 1")

=> [#<ShopClean id: 1, shop_name: "品尚豆捞 ", city_id: 1>]

 

#select

:select - By default, this is "*" as in "SELECT * FROM", but can be changed if you, for example, want to do a join but not include the joined columns. Takes a string with the SELECT SQL fragment (e.g. "id, name").

这中用法的拼sql方法就是替换掉默认的SELECT * FROM，所以从拼sql的角度来看，可以在里面写count等语句

例如：

 

?> sp = ShopClean.first(:select => "city_id, count(*) cnt", :conditions => ["city_id = 1"], :group => "city_id")

=> #<ShopClean city_id: 1>

>> sp.city_id

=> 1

>> sp.cnt

=> "2584"

#conditions

:conditions - An SQL fragment like "administrator = 1", [ "user_name = ?", username ], or ["user_name = :user_name", { :user_name => user_name }]. See conditions in the intro.

说白了就是拼where语句

注意可以使用hibernate类似的占位符，可以防止sql注入吧～

["user_name = :user_name", { :user_name => user_name }

 

 

Y

Y

Y

=

Y

Y

Y