Rails数据库高级查询

使用where方式

>> Aricle.where(:title => 'Advanced Active Record')
=> [#<Article id: 6, title: "Advanced Active Record", ...>]

使用原生SQL语句

>> Article.where("created_at > '23-04-2013' OR body NOT LIKE '%model%'")
=> [#<Article id: 7, title: "One-to-many associations", ...>,
#<Article id: 8, title: "Associations", ...>]


>> Article.where("created_at > '23-04-2013' AND body NOT LIKE '%model%'")
=> []

使用数组条件语义

>> Article.where("published_at < ?", Time.now)
=> [#<Article id: 6, title: "Advanced Active Record", ...>]

>> Article.where("published_at < ?", Time.now).to_sql
=> "SELECT
\"articles\".* FROM
\"articles\"
WHERE
(published_at < '2013-04-02 16:27:51.059277')"

>> Article.where("created_at = ?", Article.last.created_at)
=> [#<Article id: 8, title: "Associations", ...>]

>> Article.where("created_at = ? OR body LIKE ?", Article.last.created_at, 'model')
=> [#<Article id: 8, title: "Associations", ...>]

>> Article.where("title LIKE :search OR body LIKE :search",
{:search => '%association%'})
=> [#<Article id: 7, title: "One-to-many associations", ...>,
#<Article id: 8, title: "Associations", ...>]

使用代理模式

>> User.first.articles.all
=> [#<Article id: 8, title: "Associations", ...>] 

current_user.articles.create(:title => 'Private', :body => ‘Body here..’)

其他一些常用方法

>> Article.all
=> [#<Article id: 6, title: "Advanced Active Record", ...>,
#<Article id: 7, title: "One-to-many associations", ...>,
#<Article id: 8, title: "Associations", ...>]
>> Article.order("title ASC")
=> [#<Article id: 6, title: "Advanced Active Record", ...>,
#<Article id: 8, title: "Associations", ...>,
#<Article id: 7, title: "One-to-many associations", ...>]
>> Article.limit(1)
=> [#<Article id: 6, title: "Advanced Active Record", ...>]
>> Article.order("title DESC").limit(2)
=> [#<Article id: 7, title: "One-to-many associations", ...>,
#<Article id: 8, title: "Associations", ...>]    
>> Article.all
=> [#<Article id: 6, title: "Advanced Active Record", ...>,
#<Article id: 7, title: "One-to-many associations", ...>,
#<Article id: 8, title: "Associations", ...>]
>> Article.order("title ASC")
=> [#<Article id: 6, title: "Advanced Active Record", ...>,
#<Article id: 8, title: "Associations", ...>,
#<Article id: 7, title: "One-to-many associations", ...>]
>> Article.limit(1)
=> [#<Article id: 6, title: "Advanced Active Record", ...>]
>> Article.order("title DESC").limit(2)
=> [#<Article id: 7, title: "One-to-many associations", ...>,
#<Article id: 8, title: "Associations", ...>]

默认作用域

首先我们看看数据库的默认排序方法
>> Category.all
=> [#<Category id: 1, name: "Programming", ...>, #<Category id: 2, name: "Event", ...>,
#<Category id: 3, name: "Travel", ...>, #<Category id: 4, name: "Music", ..>,
#<Category id: 5, name: "TV", ...>]

在category模型中我们添加default_scope方法

class Category < ActiveRecord::Base
    has_and_belongs_to_many :articles 
    default_scope lambda { order('categories.name') }
end

我们在rails命令行中进行测试

>> reload!
Reloading...
>> Category.all
=> [#<Category id: 2, name: "Event", ...>, #<Category id: 4, name: "Music", ...>,
#<Category id: 1, name: "Programming", ...>, #<Category id: 5, name: "TV", ...>,
#<Category id: 3, name: "Travel", ...>]

自定义作用域

首先我们需要在模型中定义方法，如下

class Article < ActiveRecord::Base
    scope :published, lambda { where("articles.published_at IS NOT NULL") }
    scope :draft, lambda { where("articles.published_at IS NULL") }
    scope :recent, lambda { published.where("articles.published_at > ?",
    1.week.ago.to_date)}
    scope :where_title, lambda { |term| where("articles.title LIKE ?", "%#{term}%") }
     
    def long_title
        "#{title} - #{published_at}"
    end
end

我们在命令行中测试

>> Article.published
=> [#<Article id: 6, title: "Advanced Active Record", ...>]
>> Article.draft
=> [#<Article id: 7, title: "One-to-many associations", ...>,
#<Article id: 8, title: "Associations", ...>]
>> Article.recent
=> [#<Article id: 6, title: "Advanced Active Record", ...>]
>> Article.draft.where_title("one")
=> [#<Article id: 7, title: "One-to-many associations", ...>]
>> Article.where_title("Active")
=> [#<Article id: 6, title: "Advanced Active Record", ...>]