Arel, the SQL manager for Ruby (path 2)

Trong bài viết này (https://viblo.asia/p/arel-the-sql-manager-for-ruby-path-1-L4x5xyQwKBM) mình đã dịch phần đầu bài viết Arel, the SQL manager for Ruby, sau đây là phần còn lại (bài viết khá dài mong bạn thông cảm) Nguồn bài viết: https://jpospisil.com/2014/06/16/the-definitive-guide-to-arel-the-sql-manager-for-ruby.html

Extending our index finger

Chọn truy vấn ít khi trả lại dữ liệu từ toàn bộ bảng, thường thì bạn muốn query để lấy ra những thành phần nhỏ hơn. Hãy xem làm thế nào Arel xử lý các trường hợp này. Điểm khởi đầu là Arel :: Attribute . Cụ thể hơn, đó là một trong những mô đun của nó, Arel :: Predications . Bằng cách nhìn vào code bạn có thể thấy rất nhiều method hữu ích, nhiều trong số đó không giống như ActiveRecord 'APIs.

select_manager = users.project(Arel.star).where(users[:id].eq(23).or(users[:id].eq(42)))
select_manager = users.project(Arel.star).where(users[:id].eq_any([23, 42]))
select_manager.to_sql
# => SELECT * FROM "users"  WHERE ("users"."id" = 23 OR "users"."id" = 42)

Đối với các truy vấn phức tạp hơn, thường là tốt nhất để xây dựng các phần riêng lẻ và kết hợp chúng lại với nhau ở cuối.

admins_vips    = users[:admin].eq(true).or(users[:vip].eq(true))
with_karma     = users[:karma].gteq(5000).and(users[:hellbanned].eq(false))

select_manager = users.project(users[:id]).where(admins_vips.or(with_karma)).order(users[:id].desc)
select_manager.to_sql
# => SELECT COUNT("users"."id") FROM "users" WHERE (("users"."admin" = 't' OR "users"."vip" = 't')
#      OR "users"."karma" >= 5000 AND "users"."hellbanned" = 'f')
#    ORDER BY "users"."id" DESC

The more the merrier

Tiếp theo, chúng ta hãy xem các câu lệnh join. Phù hợp với API được hiển thị trước đó, Arel exposes join trực tiếp từ Arel::SelectManager . Như mong đợi, Arel hỗ trợ INNER JOIN thông thường, và LEFT , RIGHT , FULL OUTER JOIN .

comments       = Arel::Table.new(:comments, ActiveRecord::Base)

select_manager = users.project(Arel.star).join(comments).on(users[:id].eq(comments[:user_id]))
select_manager.to_sql
# => SELECT * FROM "users" INNER JOIN "comments" ON "users"."id" = "comments"."user_id"

Để tạo các kiểu kết nối còn lại, chúng ta cần truyền một đối số thứ hai cho phương thức join .

select_manager = users.project(Arel.star).join(comments, Arel::Nodes::OuterJoin).
  on(users[:id].eq(comments[:user_id])).
  having(comments[:id].count.lteq(16)).
  take(15)

select_manager.to_sql
# => SELECT * FROM "users" LEFT OUTER JOIN "comments" ON "users"."id" = "comments"."user_id"
#    HAVING COUNT("comments"."id") <= 16 LIMIT 15

Vì sự cần thiết của OuterJoin là rất phổ biến, có một cách gọi tắt là outer_join , trong đó gọi phương thức join với Arel::Nodes::OuterJoin đối số. Để có được các kiểu kết nối còn lại, có các Arel::Nodes::FullOuterJoin và Arel::Nodes::RightOuterJoin .

Loại CROSS JOIN it khi được sử dụng và không được hỗ trợ trực tiếp. Nó không được hỗ trợ bởi như đối với trường hợp trước, chúng ta có thể sử dụng để tạo Arel::Nodes::SqlLiteral hoặc tốt hơn bằng cách viết lại các truy vấn để sử dụng các cấu trúc được hỗ trợ.

There’s always more

Arel đi kèm với hỗ trợ ngay cả đối với các tính năng tiên tiến một chút như WITH báo cáo hoặc các chức năng WINDOW . Chúng ta hãy thử sao chép một ví dụ 7.8.1. SELECT trong WITH từ hướng dẫn sử dụng PostgreSQL. Truy vấn khá phức tạp, nó bao gồm 2 câu lệnh WITH và một vài truy vấn phụ. Hãy tập trung đầu tiên vào các mệnh đề top_regions và top_regions .

orders          = Arel::Table.new(:orders, ActiveRecord::Base)
reg_sales       = Arel::Table.new(:regional_sales, ActiveRecord::Base)
top_regions     = Arel::Table.new(:top_regions, ActiveRecord::Base)

reg_sales_query = orders.project(orders[:region], orders[:amount].sum.as("total_sales")).
                    group(orders[:region])
reg_sales_as    = Arel::Nodes::As.new(reg_sales, reg_sales_query)

Không có gì đặc biệt. Ngoại lệ duy nhất là sự thể hiện rõ ràng của Arel::Nodes::As .Có vẻ như không giống phương thức as sử dụng.

top_regions_subquery = reg_sales.project(Arel.sql("SUM(total_sales) / 10"))
top_regions_query    = reg_sales.project(reg_sales[:region]).
                        where(reg_sales[:total_sales].gt(top_regions_subquery))
top_regions_as       = Arel::Nodes::As.new(top_regions, top_regions_query)

Việc sử dụng Arel.sql không phải là lý tưởng, tuy nhiên, như với phần trước, không có cách nào để sử dụng các phép toán vào kết quả cuộc gọi sum .

attributes = [orders[:region], orders[:product], orders[:quantity].as("product_units"),
               orders[:amount].as("product_sales")]

res = orders.project(*attributes).where(orders[:region].in(top_regions.project(top_regions[:region]))).
        with([reg_sales_as, top_regions_as]).group(orders[:region], orders[:product])

res.to_sql

Với tất cả những điều đó, ta đã có được câu querry. Nếu chúng ta nhìn vào các phần riêng lẻ, chúng khá đơn giản. Nhìn chung, tuy nhiên, code này dài hơn giải pháp SQL thuần túy. Thực tế không quan trọng khi sử dụng Arel, nhưng nếu được soạn bằng tay, người ta phải luôn luôn cân nhắc liệu nó thực sự có giá trị.

SelectManager is not the only one

Tất cả những gì chúng ta đang làm là viết các truy vấn thông qua SelectManager , nhưng Arel tất nhiên cũng hỗ trợ các tính năng khá khác. Hãy xem xét tính năng DELETE. Có hai cách bạn có thể tạo một truy vấn xóa. Cách thứ nhất là Arel :: DeleteManager .

delete_manager = Arel::DeleteManager.new(ActiveRecord::Base)
delete_manager.from(users).where(users[:id].eq_any([4, 8]))
delete_manager.to_sql
# => DELETE FROM "users" WHERE ("users"."id" = 4 OR "users"."id" = 8)

Một cách khác, mặc dù có vẻ không được tốt, là tạo ra lệnh xóa từ một lệnh select bằng cách gọi compile_delete (có những method tương tự cho các tính năng khác). Bằng cách nhìn vào code chúng ta có thể thấy rằng tất cả những gì nó làm là chọn các giá trị ra khỏi đối tượng nó trộn lẫn vào ( Arel::SelectManager ) và truyền nó tới một instance mới của Arel::DeleteManager .

select_manager = users.project(users[:id], users[:name]).where(users[:banned].eq(true))
select_manager.to_sql
# => SELECT "users"."id", "users"."name" FROM "users"  WHERE "users"."banned = 't'

delete_manager = select_manager.compile_delete
delete_manager.to_sql
# => DELETE FROM "users" WHERE "users"."banned" = 't'

InsertManager và UpdateManager , hoạt động theo cách tương tự.

insert_manager = Arel::InsertManager.new(ActiveRecord::Base)
insert_manager.insert([[users[:name], "Bob"], [users[:admin], true]])
insert_manager.to_sql
# => INSERT INTO "users" ("name", "admin) VALUES ('Bob', 't')

Lưu ý rằng Arel::InsertManager có thể tìm ra tên của bảng chúng tôi đang chèn để tự động thông qua việc sử dụng Arel::Attribute . Nếu chúng ta sử dụng chuỗi literals thay vào đó, chúng ta phải chỉ định tên bảng thông qua phương thức into . Tương tự không có trong Arel::UpdateManager và chúng ta phải sử dụng bảng .

The story of .to_sql

Trong suốt bài viết, chúng tôi đã gọi tới .to_sql trong hầu hết mọi ví dụ và không bao giờ thực sự nói về nó hoạt động như thế nào. Như đã đề cập ở phần đầu, Arel đại diện cho tất cả các truy vấn dưới dạng các nút trong cây cú pháp trừu tượng. Đương nhiên, cái gì sau đó phải lấy cây kết quả và xử lý nó để ra kết quả cuối cùng. Arel sử dụng nhiều loại truy cập để thực hiện việc này. Về cơ bản, mô hình truy cập tóm tắt cách các nút của AST được xử lý từ các nút chính nó. Các nút vẫn giữ nguyên, nhưng có thể áp dụng các truy cập khác nhau và nhận được kết quả khác nhau. Đây là chính xác những gì Arel cần để tạo ra tất cả các loại định dạng đầu ra. Việc thực hiện của Arel của mô hình khách truy cập là thú vị. Nó sử dụng một biến thể gọi là "extrinsic visitor". Biến thể này có lợi thế lớn đối với Ruby và thông tin có sẵn khi chạy. Thay vì buộc các nút thực hiện phương thức accept , gọi trực tiếp accept cùng với root node và đối số. Sau đó kiểm tra nút để tìm ra kiểu của nó và xem phương pháp truy vấn thích hợp. Để làm cho phần gửi đi nhanh hơn, code sử dụng một bảng đơn giản cho mục đích lưu trữ.

{
  Arel::Visitors::SQLite => {
    Arel::Nodes::SelectStatement => "visit_Arel_Nodes_SelectStatement",
    Arel::Nodes::SqlLiteral      => "visit_Arel_Nodes_SqlLiteral",
    Arel::Nodes::Or              => "visit_Arel_Nodes_Or",
    Arel::Attributes::Attribute  => "visit_Arel_Attributes_Attribute",
    Arel::Nodes::InnerJoin       => "visit_Arel_Nodes_InnerJoin",
    Arel::Nodes::Having          => "visit_Arel_Nodes_Having",
    Arel::Nodes::Limit           => "visit_Arel_Nodes_Limit"
    Fixnum                       => "visit_Fixnum",
  }
}

Nếu chúng ta nhìn vào visitor directory , chúng ta có thể thấy một vài truy cập mà Arel đi kèm với mặc định. Một số người trong số đó trực tiếp tương ứng với một cơ sở dữ liệu cụ thể, một số chỉ được sử dụng nội bộ và một số chỉ được sử dụng từ AR. Lưu ý rằng tất cả các truy cập liên quan đến cơ sở dữ liệu kế thừa từ truy cập to_sql đang làm hầu hết công việc và truy cập cơ sở dữ liệu cụ thể chỉ xử lý các sự khác biệt với cơ sở dữ liệu cụ thể. Hãy tạo một select manager và nhận truy vấn SQL mà không có phương pháp to_sql .

select_manager = users.project(Arel.star)
select_manager.to_sql
# => SELECT * FROM "users"

sqlite_visitor = Arel::Visitors::SQLite.new(ActiveRecord::Base.connection)
collector      = Arel::Collectors::SQLString.new
collector      = sqlite_visitor.accept(select_manager.ast, collector)
collector.value
# => SELECT * FROM "users"

Collector là một đối tượng thu thập kết quả khi họ đến từ truy cập. Trong ví dụ cụ thể này, collector có thể là một Chuỗi riêng của Ruby và chúng ta sẽ nhận được kết quả tương tự (mà không phải gọi value cuối cùng). Nếu chúng ta nhìn vào code thực tế của to_sql , chúng ta có thể thấy rằng nó không giống nhau ngoại trừ nó được truy cập trực tiếp từ kết nối. Hãy cùng xem Arel :: Visitors :: Dot . Visitor truy cập tạo ra định dạng Dot của Graphviz và chúng ta có thể sử dụng nó để tạo các diagram bên ngoài AST. Để làm mọi thứ dễ dàng hơn, có một phương thức to_dot thuận tiện mà chúng ta có thể sử dụng. Chúng tôi lấy đầu ra và lưu nó vào một tập tin.

File.write("arel.dot", select_manager.to_dot)

Trên dòng lệnh, chúng ta sử dụng tiện ích dot để chuyển đổi kết quả sang một hình ảnh.

dot arel.dot -T png -o arel.png

Back to upper levels

Chúng tôi có tất cả sức mạnh này theo ý của chúng tôi ở mức Arel nhưng làm thế nào chúng ta có thể tận dụng nó với ActiveRecord? Hóa ra rằng chúng ta có thể dễ dàng có được Arel::Table cơ bản trực tiếp <Table>.arel_table . Thậm chí còn tốt hơn là chúng ta có thể nhận được AST từ các truy vấn của ActiveRecord và thao tác nó. Tuy nhiên một lời cảnh báo, làm việc với đối tượng Arel không được hỗ trợ chính thức và mọi thứ có thể thay đổi giữa các bản phát hành mà không cần thông báo. Trước tiên, chúng ta cần một vài bảng và các đối tượng ActiveRecord tương ứng để làm việc. Hãy quay lại với người dùng và nhận xét.

class User < ActiveRecord::Base
  connection.create_table table_name, force: true do |t|
    t.string :name, null: false
    t.integer :karma, null: false, default: 0
    t.boolean :vip, null: false, default: false
    t.timestamps
  end

  create! [{name: "Alice", karma: 999, vip: true}, {name: "Bob", karma: 1000}, {name: "Charlie"}]

  has_many :comments, dependent: :delete_all
end

class Comment < ActiveRecord::Base
  connection.create_table table_name, force: true do |t|
    t.text :text, null: false
    t.integer :points, null: false, default: 0
    t.references :user
    t.timestamps
  end

  belongs_to :user
end

Như đã đề cập ở đoạn trên, chúng ta có thể nhận được Arel::Table object bằng cách gọi arel_table. Một khi làm điều đó, có thể sử dụng các phương pháp tương tự đã được sử dụng.

u = User.arel_table
User.where(u[:karma].gteq(1000).or(u[:vip].eq(true))).to_a
# => [#<User id: 1, name: "Alice"...>, #<User id: 2, name: "Bob"...>]

Ở đây, chúng ta đang đi qua Arel node ( Arel::Nodes::Grouping ) trực tiếp AR. Không cần phải chuyển đổi bất cứ điều gì vì AR biết làm thế nào với các đối tượng này. Hãy sử dụng một truy vấn AR bên trong một Arel.

User.first.comments.create! text: "Sample text!", points: 1001

c             = Comment.arel_table
popular_users = User.select(:id).order(karma: :desc).limit(5)
comments      = c.project(Arel.star).where(c[:points].gt(1000).and(c[:user_id].in(popular_users.ast)))

Comment.find_by_sql(comments.to_sql)

Để thực hiện các truy vấn Arel, đầu tiên chúng ta cần phải lấy SQL ra khỏi Arel và sau đó nạp nó vào find_by_sql . Lưu ý rằng chúng tôi đã gọi ast trên popular_users trước khi chuyển nó tới Arel. Đó là vì popular_users là một instance của ActiveRecord::Relation và chúng ta cần phải có được Arel AST cơ bản. Dĩ nhiên khi bạn cần phải tạo một truy vấn mà không nhất thiết dẫn đến một active record relation. Trong trường hợp đó, chúng ta có thể sử dụng kết nối trực tiếp và gọi execute với SQL như là đối số.

ActiveRecord::Base.connection.execute(c.where(c[:id].eq(1)).compile_delete.to_sql)

Một vấn đề bạn có thể gặp khi sử dụng ActiveRecord 4.1.x là gọi to_sql có thể trả về một truy vấn SQL với các tham số ràng buộc thay vì các giá trị thực tế. Vấn đề đã được giải quyết và sẽ được update phiên bản tiếp theo. Tuy nhiên, để có thể giải quyết vấn đề này, chúng ta phải sử dụng unprepared_statement .

# ActiveRecord 4.1.x

sql = User.first.comments.to_sql
# => SELECT "comments".* FROM "comments"  WHERE "comments"."user_id" = ?

sql = User.connection.unprepared_statement {
  User.first.comments.to_sql
}
# => SELECT "comments".* FROM "comments"  WHERE "comments"."user_id" = 1

Code trong unppared_statement được đánh giá với truy cập kết hợp trong Arel :: Visitors :: BindVisitor , nó ngay lập tức giải quyết các tham số ràng buộc.

Real world

Có được tất cả những điều đó, làm thế nào để chúng ta sử dụng điều này trong một ứng dụng từ thực để code có thể duy trì và sẽ không trở thành một mớ hỗn độn? Một cách để làm việc đó là tạo ra một lớp đại diện cho truy vấn. Hãy xem một ví dụ đơn giản.

class PrivilegedUsersQuery
  attr_reader :relation

  def initialize(relation = User.all)
    @relation = relation
  end

  def find_each(&block)
    relation.where(privileged_users).find_each(&block)
  end

  private

  def privileged_users
    with_high_karma.or with_vip
  end

  def with_high_karma
    table[:karma].gt(1000)
  end

  def with_vip
    table[:vip].eq(true)
  end

  def table
    User.arel_table
  end
end

Tận dụng lợi thế là có thể xây dựng các truy vấn lặp đi lặp lại và dành một method cho mỗi phần hoặc tương tự, bất cứ điều gì cảm thấy như cách tiếp cận tốt nhất cho tình huống cụ thể.

PrivilegedUsersQuery.new.find_each do |user|
 # ...
end

The end

Arel là một công cụ tuyệt vời để xây dựng abstractions và một trợ giúp mạnh mẽ khi abstractions không cung cấp các chức năng bạn cần. Bởi bây giờ bạn biết tất cả mọi thứ, sử dụng Arel có hiệu quả và quan trọng nhất là bạn biết nơi để tìm câu trả lời khi xây dựng một truy vấn phức tạp hoặc khi mọi thứ sai. @JiriPospisil Cảm ơn và hi vọng bài viết giúp ích trong công việc của bạn.