当 ActiveRecord 不够用时还有 Arel

1. 取反运算符

1
SELECT * FROM posts WHERE title != 'Arel is cool'

ActiveRecord

1
Post.where.not(title: 'Arel is cool')

Arel

1
Post.where(Post[:title].not_eq('Arel is cool')

2. NULL 运算

1
SELECT * FROM posts WHERE title IS NOT NULL

ActiveRecord

1
Post.where.not(title: nil)

Arel

1
Post.where(Post[:title].not_eq(nil))

3. 比较运算

1
SELECT * FROM posts WHERE id > 100

ActiveRecord

1
Post.where('id > 100')

Arel

1
Post.where(Post[:id].gt(100))

4. Like 运算符

标题里含有 arel 的所有博文

MySQL 中默认设定时 LIKE 是不区分大小写的,要想使用区分大小写的比较则可以写成

ActiveRecord 的标准写法

1
Post.where("title LIKE BINARY ?", '%arel%')

但是这是 MySQL 特定的写法,如果换成 PostgreSQL 就需要更改查询语句了。

Arel 的写法

1
2
3
4
5
6
Post.where(Post.arel_table[:title].matches('%arel%'))
``
=>

MySQL 时

SELECT posts. FROM posts WHERE posts.title LIKE x’256172656c25’)

1
2
如果是 PostgreSQL 时


SELECT posts.
FROM posts WHERE posts.title LIKE ‘%arel%’)
1
2
3
4
5

## 5. 获得查表SQL文

使用 `count`


Post.select(Post[:id].count, :text).to_sql
1
2
=>


SELECT COUNT(posts.id), text FROM posts
1
2
3

使用别名 `as`


Post.select(Post[:id].count.as(count_id)).to_sql
1
2
3

=>


SELECT COUNT(posts.id) AS count_id, text FROM posts
1
2
3

## 6. 使用自定义命令


Post.select(
Arel::Nodes::NamedFunction.new(
“LENGTH”, [Post[:text]]
).as(“length”)
)
1
2
3

=>


SELECT LENGTH(posts.text) AS length FROM posts
1
2
3

## 7. 全选


Post.select(Arel.star)
1
2
3

=>


SELECT FROM posts
1
2
3
4
5

## 8. 子查询

1). 在From 语句里面使用


Post.select(:id).from(Post.select([:title, :text]).ast)
1
2
3

=>


SELECT id FROM SELECT title, text FROM posts
1
2
3

2). 在Where 语句里面使用


Post.where(Post[:title].eq(‘Arel is cool’).and(Post[:id].eq(22)))
1
2
3

=>


SELECT id FROM posts WHERE(
posts.title = ‘Arel is cool’ AND (Post.id = 22)
)
1
2
3
4
5

3). 在条件语句里面

查询与第五个博文标题一致的所有博文


SELECT posts.
FROM posts WHERE posts.title IN (
SELECT posts.title WHERE id = 5
)
1
2
3

### ActiveRecord 的标准写法


WHERE posts.title IN (
SELECT title FROM posts WHERE id = 5
)
1
2
3

### Arel 的写法


Post.where(
Post[:title].in(
Post.select(:title).where(id: 5).ast
)
)
1
2
3
4
5
6


## 9. JOIN 语句

假设 3 个类的关系是


class Post < ActiveRecord::Base
has_many :comments

end

class Comment < ActiveRecord::Base
belongs_to :post
has_one :author

end

class Author < ActiveRecord::Base
belongs_to :comment

end

1
2
3

1). `INNER JOIN` 查询用户"Barack Obama"评论过的所有有效博文


SELECT posts.*
FROM posts
INNER JOINS comments ON comments.post_id = posts.id
INNER JOINS authors ON authors.comment_id = comments.id
WHERE authors.name = ‘Barack Obama’
AND posts.active = true
1
2
3

### ActiveRecord 的标准写法


Post
.joins(:comments => :author)
.where([
“authors.name = ? AND posts.active = ?”,
“Barack Obama”, true
])

Post
.joins(:comments => :author)
.where(author: {name: “Barack Obama”}, posts: {active: true})

1
2
3

### Arel 的写法


Post
.joins(:comments)
.joins(Comment.joins(:author).join_sources)
.where(
Author[:name].eq(“Barack Obama”)
.and(Post[:active].eq(true))
)
`

备注

Post[:id] 写法默认不支持,是使用了 arel-helpersPost.arel_table[:id] 的缩写方式。

延伸阅读