识别与SQL查询没有付款关联的资源 [英] Identify Resource that Does Not Have a Payment Association with SQL query

问题描述

我有一个索引页面，我想同时显示特色板和标准板。特色板具有has_one付款，标准板则没有。

I have an index page where I want to both show featured and standard boards. A featured board has has_one payment, a standard board does not.

class Payment < ApplicationRecord
  belongs_to :board
end

class Board < ApplicationRecord
  has_one :payment
end

所以我可以通过

Board.joins(:payment).where(category: category, confirmed: true)

现在我想通过以下方式获取标准清单：

Now I want to get the standard listings by doing:

Board.where(category: category, confirmed: true)

但这都会返回特色板和标准板。

But this both returns the featured and the standard boards.

我正在寻找一种获取标准列表的方式（没有

I'm looking for a way to get standard listings (boards that do not have a payment) and I just can't figure out how to do that.

推荐答案

Board
  .joins("LEFT JOIN payments ON boards.id = payments.board_id")
  .where(payments: {id: nil})

我强烈建议您阅读这篇精彩的博客文章，将所有不同的连接可视化。这也说明了您的用例：您首先离开了董事会并进行付款，然后过滤出所有有付款的董事会。

I would highly suggest you read this great blog post visualizing all different joins. It also explains your use case: You first left join the boards with the payments and then filter out all board who do have a payment.

这篇关于识别与SQL查询没有付款关联的资源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！