Advanced SQL in Rails - Part 2

Trong phần 1 của bài đăng này, tôi đã tổng quan nhanh về các chức năng windows và views của SQL. Bây giờ, chúng ta sẽ thấy cách chúng ta có thể sử dụng những tính năng này ngay bên trong Rails.

Putting it all together

Để chứng minh làm thế nào chúng ta có thể gia tăng ứng dụng Rails với windows và views, tôi sẽ xây dựng một ứng dụng Rails nhỏ được gọi là GifVotr. Hãy suy nghĩ về nó như Reddit dành cho GIFs: bạn có thể đăng GIF và mọi người có thể vote cho nó. GIF được nhiều vote nhất sẽ tăng lên hàng đầu.

Chúng ta có thể bắt đầu với một số mô hình đơn giản và các bảng tương ứng cho GIF và vote:

class Gif < ApplicationRecord
  has_many :votes
end

class Vote < ApplicationRecord
  belongs_to :gif

  scope :upvotes,   -> { where(value:  1) }
  scope :downvotes, -> { where(value: -1) }
end

Với những mô hình đơn giản này , chúng ta có các câu hỏi như:

gif = Gif.first
# => <Gif ...>

gif.votes.upvotes.size
# => 12

gif.votes.downvotes.size
# => 5

Nếu chúng tôi muốn tính toán thứ hạng của mỗi GIF, chúng tôi có thể tổng hợp các phiếu bầu cho mỗi GIF và sau đó sắp xếp theo:

class Gif < ApplicationRecord
  has_many :votes

  def score
    votes.sum(:value)
  end
end

gif.score
# SELECT SUM("votes"."value") FROM "votes" WHERE "votes"."gif_id" = $1  [["gif_id", 7]]
# => 7

Chúng ta sẽ tính score bằng cách tiến hành một truy vấn tổng hợp mỗi khi nó được gọi, điều này sẽ chuyển thành nhiều truy vấn bổ sung khi tập dữ liệu của bạn phình to. Thêm vào đó, chúng ta không chỉ cần tính score - chúng ta cũng muốn hiển thị thứ hạng của mỗi GIF - loại GIF phổ biến nhất # 1, # 2 phổ biến nhất ...

Put a view on it

Thay vào đó, chúng ta hãy xem! Chúng ta sẽ bắt đầu bằng cách tải lên database console bằng cách sử dụng rails db và tìm ra cách chúng ta có thể xây dựng một truy vấn SQL cung cấp cho chúng ta dữ liệu chúng ta cần. Hãy nhớ rằng, chúng tôi đang tìm kiếm số điểm của mỗi GIF, cũng như thứ hạng của nó so với tất cả các GIF trong hệ thống.

SELECT row_number() OVER () AS id, total_votes.gif_id, total_votes.score, rank() OVER (ORDER BY total_votes.score DESC) AS rank
FROM ( SELECT gif_id, sum(value) AS score FROM votes GROUP BY gif_id) AS total_votes;

 id | gif_id | score | rank
----+--------+-------+------
  3 |      7 |    26 |    1
  1 |      8 |    22 |    2
  2 |      9 |    14 |    3
(3 rows)

Đây là một truy vấn khá phức tạp, vì vậy, hãy chia nó xuống.

Trước tiên, chúng ta cần phải tính điểm cho mỗi GIF. Chúng ta có thể nhận được điều này từ bảng bình chọn với một chức năng tổng hợp cơ bản:

SELECT gif_id, sum(value) AS score FROM votes GROUP BY gif_id;

 gif_id | score
--------+-------
      8 |    22
      9 |    14
      7 |    26
(3 rows)

Tiếp theo, chúng ta muốn lấy kết quả này và tính toán một xếp hạng cho mỗi hàng, dựa trên score. Score cao nhất sẽ nhận được thứ hạng cao nhất. Để làm điều này, chúng ta có thể truy vấn ở trên và sử dụng nó trong mệnh đề FROM của truy vấn lớn hơn của chúng ta, để tính toán của chúng ta có thể hoạt động trên những kết quả đó. Chúng ta phải đặt tên cho bộ kết quả này để chúng ta có thể tham khảo nó trong phần còn lại của truy vấn, vì vậy chúng ta sẽ gọi nó là 'total_votes'.

SELECT ... FROM (SELECT gif_id, sum(value) AS score FROM votes GROUP BY gif_id ) AS total_votes;

Chúng ta có thể sử dụng hàm rank () để tính toán xếp hạng cho mỗi hàng. Thú vị là: bạn không thể sử dụng thứ hạng () mà không có một chức năng cửa sổ, mà làm cho nó một ví dụ khá tuyệt vời!

SELECT ...,rank() OVER (ORDER BY total_votes.score DESC) AS rank FROM ( SELECT gif_id, sum(value) AS scoreFROM votes GROUP BY gif_id) AS total_votes;

Chúng ta cần phải xếp thứ hạng () để sắp xếp thứ tự các kết quả , vì vậy sử dụng một window function và hướng dẫn nó sắp xếp các hàng theo score giảm dần. Không có gì ngạc nhiên khi chúng tôi đặt tên cho cột này là 'rank'.

Cùng với cột 'xếp hạng', chúng tôi cũng muốn trả lại các cột khác từ 'total_votes'. Trong khi chúng tôi đang ở đó, chúng ta nên thêm một 'id' :

SELECT row_number() OVER () AS id, total_votes.gif_id, total_votes.score, rank() OVER (ORDER BY total_votes.score DESC) AS rank
FROM (SELECT gif_id, sum(value) AS score FROM votes GROUP BY gif_id) AS total_votes;

Waiter, there’s a View in my Migration****

Bây giờ, chúng ta có thể áp dụng nó lên Rails. Chạy migration để tạo View trong db. Chúng ta gọi truy vấn trong một câu lệnh CREATE VIEW và gọi nó là 'thứ hạng'. Chúng ta có thể sử dụng chức năng thực thi của Active Record để chuyển quyền SQL của chúng ta vào cơ sở dữ liệu.

class CreateRankingsView < ActiveRecord::Migration[5.0]
  def up
    execute <<-SQL
      CREATE VIEW rankings AS (
        SELECT row_number() OVER () AS id,
        total_votes.*,
        rank() OVER (ORDER BY total_votes.score DESC) AS rank
        FROM (
          SELECT gif_id, sum(value) AS score
          FROM votes GROUP BY gif_id
        ) AS total_votes
      )
    SQL
  end

  def down
    execute("DROP VIEW rankings")
  end
end

Đây là nơi chúng tôi tách ra một chút từ cách Rails Way of things, vì vậy rút ra vài điều sau:

• Vì chúng ta đang chuyển SQL nên trong quá trình migration này nên chúng ta không nên sử dụng phương pháp thay đổi, vì Rails sẽ không biết làm thế nào để đảo ngược quá trình migration. Thay vào đó, chúng ta nên sử dụng up / down để chúng ta có thể thả chính xác chế độ xem nếu chúng ta cần cuộn nó lại.
• Khi run một new migrations, Rails cập nhật db / schema.rb để phản ánh trạng thái hiện tại của cơ sở dữ liệu. Vì chúng ta đang làm một cái gì đó tùy chỉnh bây giờ, nên kết quả của việc migrations này sẽ không hiển thị trong schema. Kết quả là chạy Rails db: schema: load sẽ không còn đặt cơ sở dữ liệu của chúng ta vào đúng trạng thái. Điều này có nghĩa là bất cứ nơi nào bạn dựa vào khởi tạo cơ sở dữ liệu từ schema - continuous integration, ví dụ bạn chỉ cần sử dụng Rails db:migrate thay thế. (Điều đáng nói đến ở đây là thoughtbot đã tạo ra một loại gem có tên là Scenic để giải quyết vấn đề thứ hai, ví dụ như nếu bạn thấy mình làm việc với tầm nhìn thường xuyên, hãy xem Scenic.)

Dù sao, bây giờ chúng tôi đã có một View trong cơ sở dữ liệu. Sau quy ước Rails của việc đặt tên table/model , chúng ta có thể nối Ranking model vào "table" này của chúng ta:

class Ranking < ApplicationRecord
  self.primary_key = :id

  belongs_to :gif
end

Điều này giống như bất kỳ mô hình bảng hỗ trợ khác, ngoại trừ một sự khác biệt nhỏ: chúng ta cần phải cho schema biết khóa chính của nó là gì, vì Rails không thể nhận biết dc từ schema.

Bây giờ chúng tôi đã nối View với Model, thực tế rằng đây là một truy vấn SQL phức tạp và chúng tôi có thể tiến hành như thể 'ranking' thực sự là một bảng.

Hãy nhanh chóng chuyển tiếp một chút và xem tất cả ba mô hình này có thể làm việc cùng nhau để tạo ra một bộ kết quả phức tạp trong khi nhấn vào cơ sở dữ liệu chỉ một lần:

class Gif < ApplicationRecord
  has_one :ranking
  has_many :upvotes,   -> { upvotes   }, class_name: "Vote"
  has_many :downvotes, -> { downvotes }, class_name: "Vote"

  scope :with_votes,    -> { includes(:upvotes, :downvotes) }
  scope :with_rankings, -> { includes(:ranking) }
  scope :order_by_rank, -> { joins(:ranking).order('rankings.rank', 'rankings.id') }

  delegate :rank, to: :ranking

  def downvotes_count
    downvotes.size
  end

  def upvotes_count
    upvotes.size
  end
end

class Ranking < ApplicationRecord
  self.primary_key = :id

  belongs_to :gif
end

class Vote < ApplicationRecord
  belongs_to :gif

  scope :upvotes,   -> { where(value:  1) }
  scope :downvotes, -> { where(value: -1) }
end

(Một điều cần lưu ý về phạm vi order_by_rank: chúng tôi được sắp xếp theo thứ hạng và id để đảm bảo thứ tự sắp xếp nhất quán vì chức năng rank () của chúng tôi sẽ gán cùng một thứ hạng cho GIF với điểm số bằng nhau Sắp xếp theo id đảm bảo rằng Các tính năng như pagination sẽ tiếp tục làm việc có thể dự đoán được. Nếu bạn muốn nerd ra nhiều hơn về chức năng xếp hạng, xem bài viết này.)

Trở lại ActiveRecord, chúng ta có thể khai thác sức mạnh của scopes và associationsđể xây dựng một truy vấn cơ sở dữ liệu hiệu quả, với tất cả các dữ liệu chúng ta cần, đưa lên phía trước:

@gifs = Gif.with_votes.with_rankings.order_by_rank

# SELECT "gifs"."id" AS t0_r0, "gifs"."image" AS t0_r1, "gifs"."created_at" AS t0_r2, "gifs"."updated_at" AS t0_r3, "votes"."id" AS t1_r0, "votes"."gif_id" AS t1_r1, "votes"."value" AS t1_r2, "votes"."created_at" AS t1_r3, "votes"."updated_at" AS t1_r4, "downvotes_gifs"."id" AS t2_r0, "downvotes_gifs"."gif_id" AS t2_r1, "downvotes_gifs"."value" AS t2_r2, "downvotes_gifs"."created_at" AS t2_r3, "downvotes_gifs"."updated_at" AS t2_r4, "rankings"."id" AS t3_r0, "rankings"."gif_id" AS t3_r1, "rankings"."score" AS t3_r2, "rankings"."rank" AS t3_r3 FROM "gifs" INNER JOIN "rankings" ON "rankings"."gif_id" = "gifs"."id" LEFT OUTER JOIN "votes" ON "votes"."gif_id" = "gifs"."id" AND "votes"."value" = $1 LEFT OUTER JOIN "votes" "downvotes_gifs" ON "downvotes_gifs"."gif_id" = "gifs"."id" AND "downvotes_gifs"."value" = $2 ORDER BY rankings.rank, "gifs"."id" ASC  [["value", 1], ["value", -1]] for those who scrolled to the end, I salute you!
=> #<ActiveRecord::Relation …>

@gifs.first.rank
=> 1

@gifs.first.upvotes_count
 => 30

@gifs.first.downvotes_count
 => 4

Conclusion

Khi kích thước và độ phức tạp của dữ liệu của bạn bắt đầu tăng nhanh ActiveRecord, bạn không cần phải vứt bỏ tất cả lợi thế của Rails chỉ để có được kết quả bạn cần. Với quy trình làm việc phù hợp, các tính năng SQL cấp cao có thể tích hợp độc đáo với bất kỳ ứng dụng Rails nào.

Link nguồn: http://brewhouse.io/2016/08/12/sql-in-rails-part2.html