0

I have two models which have tables in my rails app (board and payment). I want to know which boards have a payment associated with them. Right now I have a has_one and belongs_to association setup between a payment and a board where the foreign key is a board_id in my payments table. So the payment belongs_to a board and a board has_one payment.

I want to get all of the board objects that have a payment ID associated with them. I know I can accomplish this by making a call to the payments table to get all the payments that have a board ID and then query for those boards, but I want to do this in one DB query. Is that possible? If so what does that query look like in rails?

adamscott
  • 823
  • 1
  • 10
  • 31
  • If there is an association, how are there board objects that do not have a payment ID associated with them? – Sinstein Feb 24 '17 at 14:31
  • In rails it depends on how the association is setup. Right now I have a board_id in the payments table, but not a payment_id in the board tabl @Sinstein – adamscott Feb 24 '17 at 14:39
  • btw, the first paragraph could easily be explained with code, with text only it's hard to visualize. See http://stackoverflow.com/questions/20543490/rails-4-left-join-getting-values-of-joined-model – davegson Feb 24 '17 at 14:54

2 Answers2

2

You could either solve this with a subquery:

Board.where(id: Payment.select(:board_id).where.not(board_id: nil))

Or you can inner join the two tables:

Board.joins(:payments)

Both resulting in one DB call

Community
  • 1
  • 1
davegson
  • 8,205
  • 4
  • 51
  • 71
0

It's very easy with inner join:

Board.joins(:payment)

Returns only boards with payment associated.

Of course you need has_one :payment in your Board class.

mikdiet
  • 9,859
  • 8
  • 59
  • 68
  • He said he had that set up: "Right now I have a has_one and belongs_to association" – davegson Feb 24 '17 at 15:02
  • Is there an easy way to invert this? I.e if you want to find Boards with no payments associated (either the payment_id is nil or the payment_id is stale... there is no Payment with that id) – Dan Sharp May 28 '19 at 20:37
  • @DanSharp yes, you need to use left join, i.e. `Board.left_joins(:payment).where(payments: { id: nil })` – mikdiet May 29 '19 at 08:02
  • 1
    Perfect! I was doing: `Board.where.not(id: Board.joins(:payment).map(&:id))` which works but has two queries and gets big if there are lots of boards with payments – Dan Sharp May 29 '19 at 17:45