rails 操作数据库

数据库操作

1.生成新的记录
①使用new构造函数

>> article = Article.new=> #<Article id: nil, title: nil, body: nil, published_at: nil, created_at: nil,
updated_at: nil, excerpt: nil, location: nil>>> article.new_record?
=> true>> article.attributes=> {"body"=>nil, "created_at"=>nil, "excerpt"=>nil, "location"=>nil,"published_at"=>nil, "title"=>nil, "updated_at"=>nil}     

>> article.title = 'RailsConf'=> "RailsConf">> article.body = 'RailsConf is the official gathering for Rails developers..'=> "'RailsConf is the official gathering for Rails developers..">> article.published_at = '2013-04-13'=> "2013-04-13">> article=> #<Article id: nil, title: "RailsConf", body: "RailsConf is the official
gathering for Rails devel...", published_at: "2013-04-13 00:00:00",
created_at: nil, updated_at: nil, excerpt: nil, location: nil>>> article.save
(0.1ms) begin transactionSQL (2.2ms) INSERT INTO "articles" ("body", "created_at", "published_at","title", "updated_at") VALUES (?,?,?,?,?) [["body", "RailsConf is the official
gathering for Rails developers.."], ["created_at", Sat, 13 Apr 2013 15:50:29 UTC+00:00], ["published_at", Wed, 13 Apr 2013 00:00:00 UTC +00:00], ["title","RailsConf"], ["updated_at", Sat, 13 Apr 2013 15:50:29 UTC +00:00]]
(2.9ms) commit transaction=> true>> Article.count=> 1>> article.new_record?
=> false>> article = Article.new>> article.title
= "Introduction to SQL">> article.body
= "SQL stands for Structured Query Language, ..">> article.published_at
= Date.today>> article.save>> article = Article.new(:title => "Introduction to Active Record",:body => "Active Record is Rails's default ORM..", :published_at => Date.today)>> article.save

②使用create方法

>> Article.create(:title => "RubyConf 2013", :body => "The annual RubyConf will
take place in..", :published_at => '2013-04-13')=> #<Article id: 4, title: "RubyConf 2013", body: "The annual RubyConf will take
place in..", published_at: "2013-04-13 00:00:00", created_at: "2013-04-13
23:17:19", updated_at: "2013-04-13 23:17:19", excerpt: nil, location: nil>>> attributes = { :title => "Rails Pub Nite", :body=> "Rails Pub Nite is every
3rd Monday of each month, except in December.", :published_at => "2013-04-13"}=> {:title=>"Rails Pub Nite", :body=>"Rails Pub Nite is every
3rd Monday of each month, except in December.", :published_at=>" 2013-04-13"}>> Article.create(attributes)=> #<Article id: 5, title: "Rails Pub Nite", body: "Rails Pub Nite is every 3rd
Monday of each month, e...", published_at: "2013-04-13 00:00:00",
created_at: "2013-04-13 23:36:07", updated_at: "2013-04-13 23:36:07",
excerpt: nil, location: nil>     >> Article.count=> 5

③使用id查询单条记录

>> Article.find(3)=> #<Article id: 3, title: "Introduction to Active Record", body: "Active Record
is Rails's default ORM..", published_at: "2013-04-13 04:00:00",
created_at: "2013-04-13 23:15:37", updated_at: "2013-04-13 23:15:37",
excerpt: nil, location: nil>>>article = Article.find(3)=>#<Article id: 3 ...>>>article.id=>3>>article.title=>"Introduction to Active Record"

④使用first和last查询单条记录

>> Article.first
=> #<Article id: 1, title: "RailsConf", body: "RailsConf is the officialgathering for Rails devel...", published_at: "2013-04-13 00:00:00",
created_at: "2013-04-13 23:12:09", updated_at: "2010-04-13 23:12:09",
excerpt: nil, location: nil>
>> Article.last
=> #<Article id: 5, title: "Rails Pub Nite", body: "Rails Pub Nite is every 3rd
Monday of each month, e...", published_at: "2013-04-13 00:00:00",
created_at: "2013-04-13 23:36:07", updated_at: "2013-04-13 23:36:07",
excerpt: nil, location: nil>

⑤查询多条记录

>> articles = Article.all=> [#<Article id: 1,..> #<Article id: 2,..>, #<Article id: 3,..>,#<Article id: 4,..> , #<Article id: 5,..>]>> articles.class=> Array>> articles.size=> 5>> articles[0]=> #<Article id: 1, title: "RailsConf", body: "RailsConf is the official
gathering for Rails devel...", published_at: "2013-04-13 00:00:00",
created_at: "2013-04-13 23:12:09", updated_at: "2013-04-13 23:12:09",
excerpt: nil, location: nil>>> articles[0].title=> "RailsConf">> articles.first.title=> "RailsConf">> articles.each { |article| puts article.title }RailsConfIntroduction to SQLIntroduction to Active RecordRubyConf 2010Rails Pub Nite=> [#<Article id: 1,..> #<Article id: 2,..>, #<Article id: 3,..>,#<Article id: 4,..> , #<Article id: 5,..>]

⑥使用order对数据进行排序
默认是降序，我们可以使用DESC改变为升序

>> articles = Article.order("published_at")=> [#<Article id: 1,..> #<Article id: 2,..>, #<Article id: 3,..>,#<Article id: 4,..> , #<Article id: 5,..>]>> articles.each {|article| puts article.published_at }2013-04-13 00:00:00 UTC2013-04-13 04:00:00UTC2013-04-13 04:00:00 UTC2013-04-13 00:00:00 UTC2013-04-13 00:00:00 UTC=> [#<Article id: 1,..> #<Article id: 2,..>, #<Article id: 3,..>,#<Article id: 4,..> , #<Article id: 5,..>]>> articles = Article.order ('published_at DESC')=> [#<Article id: 4,..> #<Article id: 5,..>, #<Article id: 2,..>,#<Article id: 3,..> , #<Article id: 1,..>]>> articles.each {|article| puts article.published_at }2013-04-13 00:00:00 UTC2013-04-13 00:00:00UTC2013-04-13 00:00:00 UTC2013-04-13 00:00:00 UTC2013-04-13 00:00:00 UTC=> [#<Article id: 4,..> #<Article id: 5,..>, #<Article id: 2,..>,#<Article id: 3,..> , #<Article id: 1,..>]

⑦条件查询

>> Article.where(:title => 'RailsConf').first=> #<Article id: 1, title: "RailsConf", body: "RailsConf is the official
gathering for Rails devel...", published_at: "2013-04-13 00:00:00",
created_at: "2013-04-13 23:12:09", updated_at: "2013-04-13 23:12:09",
excerpt: nil, location: nil>>> Article.where(:title => 'RailsConf').all=> [#<Article id: 1, title: "RailsConf", body: "RailsConf is the official
gathering for Rails devel...", published_at: "2013-04-13 00:00:00",
created_at: "2013-04-13 23:12:09", updated_at: "2013-04-13 23:12:09",
excerpt: nil, location: nil>]>> Article.where(:title => 'Unknown').all=> []

⑧更新记录

>> article = Article.first>> article.title = "Rails 4 is great">> article.published_at = Time.now>> article.save=> true

也可以一次性更新多个字段

>> article = Article.first>> article.update_attributes(:title => "RailsConf2013", :published_at => 1.day.ago)=> true

或者更新一个字段

>> article = Article.first>> article.update_attribute(:title => "RailsConf2013")=> true

⑨删除记录

 >> article = Article.last    >> article.destroy    => #<Article id: 5, title: "Rails Pub Nite", body: "Rails Pub Nite is every 3rd
    Monday of each month, e...", published_at: "2013-04-13 00:00:00",
    created_at: "2013-04-13 23:36:07", updated_at: "2013-04-13 23:36:07",
    excerpt: nil, location: nil>
    #或者在一行里面操作
    >> Article.last.destroy    >> Article.destroy(1)    => [#<Article id: 1, title: "RailsConf", body: "RailsConf is the official
    gathering for Rails devel...", published_at: "2010-02-27 00:00:00",
    created_at: "2010-05-01 23:12:09", updated_at: "2010-05-01 23:12:09",
    excerpt: nil, location: nil>]#删除多条记录>> Article.destroy([2,3])=> [#<Article id: 2, ..>, #<Article id: 3, ..>]

使用destroy是没有返回值的，如果需要返回值，我们可以使用delete方法来删除记录

>> Article.delete(4)=> 1#数据库中不存在5到6的记录，所以返回0>> Article.delete([5, 6])=> 0

规定条件删除相应的记录

>> Article.delete_all("published_at < '2011-01-01'")>> 0

1.在我们的模型中添加下面方法

class Article < ActiveRecord::Base
     validates_presence_of :title
     validates_presence_of :body

     def long_title         "#{title} - #{published_at}"
     endend

我们就使用上面的方法来操作数据库了

#在命令行中操作模型>> Article.create :title => 'Advanced Active Record', :published_at => Date.today,:body => 'Models need to relate to each other. In the real world, ...'=> #<Article id: 6, title: "Advanced Active Record", ...>>> Article.last.long_title=> "Advanced Active Record - 2013-04-22 04:00:00 UTC"

2.数据库之间的关系
①一对一关系
首先：我们创建两张表
rails generate model User email:string password:string
rails generate model Profile user_id:integer name:string birthday:date bio:text color:string twitter:string
我们分别在两个模型中创建对应的方法

#User模型class User < ActiveRecord::Basehas_one :profileend#Profile模型class Profile < ActiveRecord::Basebelongs_to :userend

我们接下来可以在rails命令行中做测试了

>> user = User.create(:email => 'user@example.com', :password => 'secret')=> #<User id: 1, email: "user@example.com", password: "secret",
created_at: "2013-04-02 15:10:07", updated_at: "2013-04-02 15:10:07">>> profile = Profile.create(:name => 'John Doe',:bio => 'Ruby developer trying to learn Rails')=> #<Profile id: 1, user_id: nil, name: "John Doe", birthday: nil,
bio: "Ruby developer trying to learn Rails", color: nil, twitter: nil,
created_at: "2013-04-02 15:10:55", updated_at: "2013-04-02 15:10:55"> >> user.profile=> nil>> user.profile = profile=> #<Profile id: 1, user_id: 1, name: "John Doe", birthday: nil,
bio: "Ruby developer trying to learn Rails", color: nil, twitter: nil,
created_at: "2013-04-02 15:10:55", updated_at: "2013-04-02 15:10:55">>> user.profile=> #<Profile id: 1, user_id: 1, name: "John Doe", birthday: nil,
bio: "Ruby developer trying to learn Rails", color: nil, twitter: nil,
created_at: "2013-04-02 15:10:55", updated_at: "2013-04-02 15:10:55">>> user.create_profile :name => 'Jane Doe', :color => 'pink'=> #<Profile id: 2, user_id: 1, name: "Jane Doe", birthday: nil,
bio: nil, color: "pink", twitter: nil, created_at: "2013-04-02 15:18:57",
updated_at: "2013-04-02 15:18:57">

一对一模型中常用方法总结如下：
user.profile 返回user对应的profile对象
user.profile=(profile)对user的profile赋值
user.profile.nil? 返回user的profile是否为空，为空返回真
user.build_profile(attributes={}) 返回一条新的user的profile对象，但是不会保存到数据库，需要使用user.profile.save来保存
user.create_profile(attributes={})返回user的profile对象，直接保存到数据库中

②一对多关系
我们使用user表对应article表，每个用户都有很多的文章，但是每篇文章都只对应一个用户
rails generate migration add_user_id_to_articles user_id:integer

#文章表模型class Article < ActiveRecord::Base
    validates_presence_of :title
    validates_presence_of :body
    belongs_to :user#每篇文章指定属于用户
    def long_title        "#{title} - #{published_at}"
    endend#用户表   class User < ActiveRecord::Base
    has_one :profile
    has_many :articlesend

我们在rails命令 行中使用示例

>> user = User.first=> #<User id: 1, email: "user@example.com", password: "secret",
created_at: "2013-04-02 15:10:07", updated_at: "2013-04-02 15:10:07">>> user.articles=> []>> user.articles << Article.first=> [#<Article id: 6, ..., user_id: 1>]>> user.articles.size=> 1>> user.articles=> [#<Article id: 6, ..., user_id: 1>]>> Article.first.user_id=> 1>> Article.first.user=> #<User id: 1, email: "user@example.com", password: "secret",
created_at: "2013-04-02 15:10:07", updated_at: "2013-04-02 15:10:07">

一对多常用方法
user.articles 返回用户模型中所有文章对象
user.articles=(articles) 替换用户模型的所有文章对象，用articles来代替
user.articles << article 添加article对象到user的文章对象中
user.articles.delete(articles) 删除文章模型中的一篇或者多篇文章
user.articles.empty? 判断用户实例的文章是否是空的
user.articles.size 返回用户示例的文章数量
user.article_ids 返回用户示例的文章id，以数组形式返回
user.articles.clear 清空用户的文章
user.articles.find 传入文章的id，返回该文章
user.articles.build(attributes={}) 创建一个新的用户文章对象，不会保存，需要使用user.articles.last.save来保存
PS:这种方法一般在实际的应用是这样子使用的

product = Product.find(params[:product_id])@line_item = @cart.line_items.build(product: prodcut)#将产品实例直接传进来respond_to do |format|  if @line_item.save#使用它来保存

user.articles.create(attributes={}) 直接创建文章对象，并且保存到数据库中。

③关系型数据库扩展
i.定义默认排序

#指定单个的排序class User < ActiveRecord::Base
    has_one :profile
    has_many :articles, -> { order('published_at DESC') }end#指定多个字段排序class User < ActiveRecord::Base
    has_one :profile
    has_many :articles, -> { order('published_at DESC, title ASC')}end

ii.特殊依赖
当我们删除一个用户时，该用户的所有文章都会被删除

class User < ActiveRecord::Base
    has_one :profile
    has_many :articles, -> { order('published_at DESC, title ASC')}, :dependent => :destroyend

当然我们也可以不删除文章，可以设置被删除用户的所有文章的user_id为空

class User < ActiveRecord::Base
    has_one :profile
    has_many :articles, -> { order('published_at DESC, title ASC')}, :dependent => :nullifyend

④多对多关系
我们假定一篇文章有很多种分类，一个分类也有很多文章，我们使用rails generate model Category name:string和rails generate migration create_articles_categories分别创建分类表和中间表，中间表的定义如下

#The db/migrate/20130407002156_create_articles_categories.rb: Fileclass CreateArticlesCategories < ActiveRecord::Migration
    def change
        create_table :articles_categories, :id => false do |t|
        t.references :article
        t.references :category
        end
    end
    def self.down
        drop_table :articles_categories
    endend

references方法和integer是一样的效果，也就是说我们可以使用下面的方式来创建add_column :articles_categories, :article_id, :integer,add_column :articles_categories, :category_id, :integer
下面我们分别为Article模型和Category模型添加has_and_belongs_to_many方法

class Article < ActiveRecord::Base
    has_and_belongs_to_many :categoriesendclass Category < ActiveRecord::Base
    has_and_belongs_to_many :articlesend

下面我们在rails命令行中测试，

>> article = Article.last=> #<Article id: 8, title: "Associations", ...>>> category = Category.find_by_name('Programming')=> #<Category id: 1, name: "Programming", ..>>> article.categories << category=> [#<Category id: 1, name: "Programming", ..>]>> article.categories.any?
=> true>> article.categories.size=> 1>> category.articles.empty?
=> false>> category.articles.size=> 1>> category.articles.first.title>> "Associations"    

多对多关系需要注意，我们可以为每个字段添加索引，增加数据库的查询速度，并且可以通过数据库来保证每个字段的唯一性

add_index :articles_categories, :article_idadd_index :articles_categories, :category_idadd_index :articles_categories, [:article_id, :category_id], unique: true

自定义方法来模拟多对多关系

还是通过文章和分类模型来定义多对多关系
1.创建ac数据库

class CreateAcs < ActiveRecord::Migration
  def change
    create_table :acs do |t|
      t.integer :article_id
      t.integer :category_id

      t.timestamps    end
  endend

2.分别在文章和分类模型中定义下面的方法
文章模型

class Article < ActiveRecord::Base
    validates_presence_of :title
    validates_presence_of :body

    belongs_to :user
    has_many :acs, dependent: :destroy #这里我们不需要制定外键，默认是article_id
    def long_title        "#{title} -- #{published_at}"       
    end

    def allc
        categories = []        self.acs.each {|i| categories << Category.find(i.category_id)}        return categories    end end

分类模型

class Category < ActiveRecord::Base
    has_many :acs, dependent: :destroy#这里我们不需要制定外键，默认是category_id

    def alla
        articles = []        self.acs.each {|i| articles << Article.find(i.article_id)}        return articles    endend

最后在ac类中定义下面的方法

class Ac < ActiveRecord::Base
    belongs_to :article, class_name: "Article"
  belongs_to :category, class_name: "Category"end

这样我们就可以模拟多对多关系了，应该比默认的方法查询速度要快很多吧！

使用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}"
    endend

我们在命令行中测试

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