0

I'm trying to get all transactions in a postgresql db, transactions have a one-to-one relationship with the pairs table. In the pairs table, there's a column called match with type json. I want to select all transactions including their pair whose pair.match count is greater than 1.

I tried pairs = Pair.where('json_array_length(match) > 1') which worked.

How do I get all transactions where pair.match is greater than 1. Tried this -> transactions = Transactions.includes(:pair).where(pairs: 'json_array_length(match) > 1') but didn't work.

Update: It's Transaction not Transactions

lawrence.adu
  • 89
  • 7

2 Answers2

2

A couple of observations:

  1. The model should be "Transaction" (singular)
  2. You are really setting yourself up for a world of hurt by naming it "Transaction". That's basically a "reserved word". You don't want to use it. You're going to have collisions with other places where there's already a method called "transaction" or "transactions"
  3. Same with having a column called "match". That's a really common method name in Ruby.

I know I'm not answering the question, but this is too much for a comment and you need to fix these issues before going on.

Michael Chaney
  • 2,911
  • 19
  • 26
1

You can try joining the two tables. Supposing that your models are Pair and Transaction

transactions = Transaction.joins(:pair).where('json_array_length(match) > 1')
geoandri
  • 2,360
  • 2
  • 15
  • 28
  • Would this be slow if I have 1 million transactions and 1 million pairs? – lawrence.adu Oct 18 '17 at 12:37
  • 1
    I can't imagine any shortcut on this. It's a simple join between two tables. I agree with the observations of Michael Chaney though. You should adopt all proposed changes to avoid any problems in the future. – geoandri Oct 18 '17 at 12:52