时间戳

首页 > 解决方案 > Rails 与扩展生成不正确 SQL 的多对多关系

sql - Rails 与扩展生成不正确 SQL 的多对多关系

问题描述

我遇到了一个问题，即与“扩展”的多对多关系生成不正确的 SQL。

class OrderItem < ApplicationRecord
  belongs_to :buyer, class_name: :User
  belongs_to :order
  belongs_to :item, polymorphic: true
end

class User < ApplicationRecord
  has_many :order_items_bought,
    -> { joins(:order).where.not(orders: { state: :expired }).order(created_at: :desc) },
    foreign_key: :buyer_id,
    class_name: :OrderItem

  has_many :videos_bought,
    -> { joins(:orders).select('DISTINCT ON (videos.id) videos.*').reorder('videos.id DESC') },
    through: :order_items_bought,
    source: :item,
    source_type: :Video do
      def confirmed
        where(orders: { state: :confirmed })
      end
    end
end

user.videos_bought.confirmed生成此 SQL：

Video Load (47.0ms)  SELECT  DISTINCT ON (videos.id) videos.* FROM 
"videos" INNER JOIN "order_items" "order_items_videos_join" ON 
"order_items_videos_join"."item_id" = "videos"."id" AND 
"order_items_videos_join"."item_type" = $1 INNER JOIN 
"orders" ON "orders"."id" = "order_items_videos_join"."order_id" INNER JOIN 
"order_items" ON "videos"."id" = "order_items"."item_id" WHERE 
"order_items"."buyer_id" = $2 AND ("orders"."state" != $3) AND "order_items"."item_type" = $4 AND 
"orders"."state" = $5 ORDER BY videos.id DESC, "order_items"."created_at" DESC LIMIT $6

它返回一些Video与未确认状态的订单相连的记录。我希望所有订单都得到国家确认。

如果我使用原始 SQL 一切正常：

has_many :videos_bought,
  -> {
    joins('INNER JOIN orders ON orders.id = order_items.order_id')
      .select('DISTINCT ON (videos.id) videos.*')
      .reorder('videos.id DESC')
  },
  through: :order_items_bought,
  source: :item,
  source_type: :Video do
    def confirmed
      where(orders: { state: :confirmed })
    end
end

现在user.videos_bought.confirmed生成这个 SQL：

Video Load (5.4ms)  SELECT  DISTINCT ON (videos.id) videos.* FROM 
"videos" INNER JOIN "order_items" ON 
"videos"."id" = "order_items"."item_id" INNER JOIN orders ON 
orders.id = order_items.order_id WHERE 
"order_items"."buyer_id" = $1 AND ("orders"."state" != $2) AND
"order_items"."item_type" = $3 AND "orders"."state" = $4 ORDER BY
videos.id DESC, "order_items"."created_at" DESC LIMIT $5

这似乎更简洁，因为它避免了自动生成的order_items_videos_join名称。它也只返回已确认状态的订单。

知道发生了什么吗？ActiveRecord 有时会生成错误的 SQL 吗？

使用导轨5.1.5。升级到最新版没有任何区别。

我希望能解释为什么 Railsorder_items_videos_join在第一种情况下生成字符串，而在第二种情况下不生成。另外，为什么第二个 SQL 查询会产生不正确的结果。如果需要，我可以使用更多代码和数据示例来编辑问题。

标签： sqlruby-on-railsactiverecordmany-to-manyrelational-database

解决方案

ActiveRecord 有时不仅会生成错误的 SQL，而且还存在一些细微差别，因此在定义关系时最好从简单开始。例如，让我们重新处理您的查询以摆脱它DISTINCT ON。我从未见过需要使用该 SQL 子句。

在链接高度定制的关联逻辑之前，让我们先看看是否有更简单的查询方法，然后检查是否有充分的理由将您的查询转化为关联。

看起来你有这样的架构：

用户

  • ID

命令

  • ID
  • 状态

订单项

  • ID
  • order_id
  • 买家id（有什么理由在 OrderItem 上而不是在 Order 上？）
  • item_type（视频）
  • item_id (videos.id)

视频

  • ID

一些花絮

  • 无需为查询条件创建关联扩展，这将使模型上的范围非常好。见下文。

一个完美的查询可能看起来像

Video.joins(order_item: :order).
    where(order_items: {
       buyer_id: 123, 
       order: {
          state: 'confirmed'
       }).
    # The following was part of the initial logic but
    #   doesn't alter the query results.
    # where.not(order_items: {
    #    order: {state: 'expired'}
    # }).
    order('id desc')

这是另一种方式：

class User < ActiveRecord::Base
  has_many :order_items, foreign_key: 'buyer_id'
  def videos_purchased
    Video.where(id: order_items.videos.confirmed.pluck(:id))
  end
end

class OrderItem < ActiveRecord::Base
  belongs_to :order, class_name: 'User', foreign_key: 'buyer_id'
  belongs_to :item, polymorphic: true
  scope :bought, -> {where.not(orders: {state: 'cancelled'})}
  scope :videos, -> {where(item_type: 'Video')}
end

class Video < ActiveRecord::Base
  has_many :order_items, ...

  scope :confirmed, -> {where(orders: {state: 'confirmed'})}
end

...
user = User.first()
user.videos_purchased

当涉及到表名和属性名时，我的语法可能有点古怪，但这应该是一个开始。

请注意，我将其从一个查询更改为两个查询。我建议您一直使用它，直到您真正注意到您有性能问题，即便如此，您可能会更轻松地缓存查询，而不是尝试优化复杂的连接逻辑。

推荐阅读