Chapter 5: Database

1 SQL Query

这里以这样一个database为例，来解释如何分步骤写SQL query （或subquery）：

cats have a name, color, and breed. toys have a name, color, and price. cat_toys is a joins table that references the cats table with the foreign key of cat_id and the toys table with the foreign key of toy_id.

将数据库内table之间的关系表示成星际地图

你的星际旅行任务： Jet the cat has a ton of toys! Now find the toys Jet has at least two copies of, and the number of copies. Sort the toys by name alphabetically.

1.1 FROM section （来源）

首先确定需要的columns：(a) 需要最后present的columns 加上 (b) 用作筛选条件的columns (c) 用作分类根据的columns。这里需要的columns为 toys.name ， COUNT(toys.id) -- number of copies，cats.name -- the toys `Jet` has ，这时候可以画出如下草图：

toys.name	COUNT(toys.id)	cats.name

根据这一信息，我们知道这些columns的来源是toys 和cats。要组合toys与cats ，就需要通过 toys -> cat_toys -> cats 来连接（见星际地图，这些链接 / association 的本质是foreign key和primary key之间的对应）。因此就有如下的query：

-- from section
from
    toys
join
    cat_toys on toys.id = cat_toys.toy_id -- association from toys to cat_toys    
    cats on cat_toys.cat_id = cats.id -- association from cat_toys to cat

1.2 WHERE section（筛选）

如1.1所述，我们的筛选条件是 cats.name = 'Jet' ，因此我们的query变成：

from
    toys
join
    cat_toys on toys.id = cat_toys.toy_id 
    cats on cat_toys.cat_id = cats.id 
-- where section
where
    cats.name = 'Jet' -- filtering condition

1.3 GROUP section （分类）

GROUP BY 是为聚合属性服务的，也就是aggregate functions，比如某一分类的SUM AVERAGE COUNT MAX MIN 。否则我们还为什么要把信息用GROUP BY 聚合起来呢？当我们的需求中包括这些聚合属性，那么我们必须要通过GROUP BY 来实现。

我们把toys.name 相同的都视作copies，每一份toy copy都有unique的id，我们把这些copies压缩成一类。 我们对这些类别的筛选条件是copies数量-- COUNT(toys.id) 至少为2。(也可以总是用count(*) 来表示数量，因为关系的数量等于关系那一头object的数量）因此我们的query变成：

from
    toys
join
    cat_toys on toys.id = cat_toys.toy_id    
    cats on cat_toys.cat_id = cats.id
where
    cats.name = 'Jet' 
-- group section
group by
    toys.name -- grouping basis
having
    count(toys.id) >= 2 -- group filtering condition

1.4 SELECT section （展示）

我们需要select哪些column由我们需要最后present哪些信息有关。在这里我们需要 toys.name 和 toy copy的数量，即 COUNT(toys.id)。所以query变成：

-- select section
select
    toys.name, COUNT(toys.id)
from
    toys
join
    cat_toys on toys.id = cat_toys.toy_id     
    cats on cat_toys.cat_id = cats.id
where
    cats.name = 'Jet'
group by
    toys.name
having
    count(toys.id) >= 2

1.5 ORDER section （排序）

最后我们加上我们排序的根据，以及数量的限制，即：

select
    toys.name, COUNT(toys.id)
from
    toys
join
    cat_toys on toys.id = cat_toys.toy_id     
    cats on cat_toys.cat_id = cats.id
where
    cats.name = 'Jet'
group by
    toys.name
having
    count(toys.id) >= 2
-- order section
order by
    toys.name

在SQL中表示string值必须使用单引号。

2 Data Model & Associations

Relational Database 中的每个table对应每个Model class，table里的每个row是一个数据单位，对应这个class的每个object。association的本质是不同table内的数据之间的对应关系，也就是不同类型(Model) 的object之间的联系。

e.g. James Cameron as a Person 是 Avatar as a Movie 的director。这里Person和Movie是model 对应 table；James Cameron 和 Avatar 分别是Person和Movie的object，对应两个table里的各自一行；而"J是A的director" 表示的是relationship，也就是association。

在Database中，这种关系是通过用foreign key来reference（指代）另一个table里的object来表示的，也就是foreign key和primary key的对应关系。注意这里primary key总是unique的（因为要被reference的对象必须是唯一的），一般都是primary table的id，所以两个table之间只能表示 one-to-many 的关系和one-to-one的relationship，one的那一方就是primary table，拥有primary key。

对于many-to-many的关系（A可以对应多个B，B对应多个A），可以通过一个relationship table with 2 foreign keys来拆解成两个one-to-many关系，比如上文中 cat_toys 这个中间table，每个cat或toy都有多个cat_toy关系，也就对应这个cat_toys里的多行记录。

2.1 Ruby on Rails: Model & Association

Rails的Model和Association的意义，在于用ruby中的class，object和function，来表示database中的数据，以及数据之间的关系。model对应数据类型，也就是table name；model的object对应一个数据单位；association对应的是从object通过数据间的relationship找到对应的多个或者单个object。声明association能够让rails自动生成access这些association另一头object的getter function。

沿着association周游，能够通过两个紧接的同向association来定义一个虚拟的association，比如X->Z的内部架构实际上是 X -> Y -> Z 。 这样的意义在于给X一个function能够直接得到Z , 并且在下一个section中会提到，在query中可以直接组合这三个table。

每一个Model的object都是一份ActiveRecord::Base或简称active record, 每一个association accessor得到的结果，都是ActiveRecord::Relation(多份record) 或 ActiveRecord::Base(单份record)

3 Ruby on Rails: Active Record Query

写Active Record Query的方法与SQL类似，对于同样一个问题，有：

Toy
    .select("toys.name", "COUNT(toys.id)")
    .joins(:cats)
    .where(cats: {name: 'Jet'})
    .having("COUNT(toys.id) >= 2")
    .order("toys.name")

• 主语class对应SQL中的出发的from table，通常是想要的信息主要所在的table。

• Active Record对应的函数，总是take SQL语句作为string参数。比如 joins("JOIN cat_toys ON ..)

• 表示column name时，"name" 与 :name 等价，都可以表示 name 这个column，正如我们经常用symbol来表示一个immutable string一样。当需要指定table name时，就只能用"toys.name"

• select

  • 当select take的是block，用法与Array#select 相同，意为筛选出满足条件的objects

  • 当select take的是一组string或者symbol，则意为只选取object的部分property，也就对应table的部分column

• joins

  • 这里:cats 表示的是一个从cat 到 cat_toys 到 toys 的 through association。正是因为association在定义时，已经描述了需要经过的table，目标table，以及foreign key和primary key之间的对应关系，所以完整替换了SQL JOIN... ON ...的作用。

  • 因为这里实际上是通过cat_toys而组合成的，所以这个中间table的column也可以使用。

  • 注意association只能用symbol而不能用string来表示。

• where

  • where 函数可以take一个SQL条件作为string参数，比如 where("cats.name='Jet'") ( 注意上文提到过的SQL语句内的string必须用单引号）

  • where 函数也可以take一个hash来表示筛选的条件，比如 where({"cats" => {"name" => 'Jet'})表示cats table中的name column的值等于'Jet'。

  • 表示column name和table name可以用symbol，而hash的花括号可以省略，因此也可以写作 where(:cats => {:name => 'Jet'})

  • 既然是hash，{:name => 'Jet'} 可以写作 {name: 'Jet'} ，因此才有了 where(cats: {name: 'Jet'})` 的写法。总体来说，有这些写法都是因为用symbol来表示hash的key的习惯（在这里key就是table和column name）

• 这些query function每一步得到的都是ActiveRecord_Relations 如果需要最终得到Array 可以用pluck 放在末尾替代select。也可以用 to_a 来转化。