2

Example data:

I have table orders:

[order_id: 007, state: completed]
[order_id: 123, state: completed]
[order_id: 007, state: failed]
[order_id: 321, state: failed]
[order_id: 111, state: failed]
[order_id: 111, state: failed]

Expected output:

[order_id: 007, state: completed]
[order_id: 123, state: completed]
[order_id: 321, state: failed]
[order_id: 111, state: failed]

I have many values in table 'Orders' with the same order_id but different state. What I want to receive is list of orders without those 'failed' states IF there is another order in table 'orders' with same order_id and with different state than 'failed'.

I need something like:

Order.all.group(:state).take_only_first_value_if("COUNT(*) > 1")
Karol Kamiński
  • 75
  • 6

2 Answers2

1

Using postgreSQL (snippet):

Order.order("order_id, state = 'failed'").select("DISTINCT ON(order_id) *")
tokland
  • 66,169
  • 13
  • 144
  • 170
  • Ok. It returns unique values of my Orders BUT ... it's still random. If there would be more than one order with same order_id I would receive random orders with this id. What I want is - if there is many orders with same order_id and one of them has 'state' different than 'failed' return me that order. DISTINCT gonna return me uniq value but random. I believe that @Kristján found my answer here: [LINK](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) But I still need help with translate this SQL on Ruby Active Record :( – Karol Kamiński Dec 30 '15 at 09:31
  • nice! .. it actually did the thing! I've made some syntax mistake. "DISTINCT ON(xxx)" is no the same as "DISTINCT ON xxx" .. thanks a lot! :) – Karol Kamiński Dec 30 '15 at 14:18
0

You could try something like:

failed = Order.where(state: 'failed').pluck(:order_id)
completed = Order.where(state: 'completed').pluck(:order_id)

what_you_want = Order.where("order_id IN (#{failed}) AND order_id IN (#{completed})").distinct
fabriciofreitag
  • 2,843
  • 22
  • 26
  • That makes more sense, though I don't think it's quite what the OP wants. You could also do this as `where(order_id: failed & completed)` (`Array#&` is intersection). – Kristján Dec 29 '15 at 16:34
  • Unfortunatelly in Your solution (getting my example above) order with order_id = 007 gonna be twice in your 'what_you_want' array ... its not what I am looking for. – Karol Kamiński Dec 29 '15 at 16:37
  • Karol, what about adding a "distinct", like I did in the latest edition? – fabriciofreitag Dec 29 '15 at 16:47