Rails数据库高级查询
Rails数据库高级查询
第三方支付接口 发表于4年前
Rails数据库高级查询
  • 发表于 4年前
  • 阅读 58
  • 收藏 0
  • 点赞 0
  • 评论 0

【腾讯云】买域名送云解析+SSL证书+建站!>>>   

除了我们常用的find,first,last,all方法以为rails还为我们提供了更加高级的一些用法,比如下面的where,和直接使用原生sql。

下面有两个非常重要的知识点

1。使用代理模式来操作数据库

2。自定义作用域

使用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", ...>]

观澜男科医院 http://wwww.no1nk.com
  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 13
博文 49
码字总数 28661
×
第三方支付接口
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: