How to get unique record by multi-attributes

Question

I have a model like this: Order(id: integer, user_id: integer, target_id: integer, content: string, created_at: datetime, updated_at: datetime)

It belongs to User. User can make order for each other like:

person A -> person B
person B -> person A
person A -> person B

=> The unique record should be A -> B, so how can I filter Order to unique record ?

I don't understand what you mean by "A -> B" or "B -> A", can you explain it another way? — rmosolgo
Thank you for your reply. I mean person A can create order to person B and vice versa. — Vanhensy
Can you clarify what you mean by "filter Order to unique record?" Are you looking for a query that returns, say, all orders made by user A where the target is user B? — jmschles
@jmschles I mean it'll return only one order that made between user A and user B ( even if user A send many order to user B and user B aslo make order for user A) — Vanhensy
Welcome to Stack Overflow! To make it easier for other users to help you, please include some code, preferably in the form of a Minimum, Complete, Verifiable Example. See also How to Ask. — Scott Weldon

jmschles jmschles · Accepted Answer · 2016-06-16T00:10:52

Okay, assuming you have users A and B, you should just be able to do this:

Order.where(user: A, target: B)

...assuming you've defined your associations, otherwise:

Order.where(user_id: A.id, target_id: B.id)

EDIT: just saw your comment. You could add .first to either of the above queries just to get the first order if you wanted to.

ANOTHER EDIT: As I understand it, if there's ever been one or more orders between A and B (or B and A), you want to grab one of them, and you don't care which. You could do this:

(Order.where(user: A, target: B) + Order.where(user: B, target: A)).first

That will give you a record if one exists and nil if it doesn't. Raw SQL would be more efficient:

Order.where("user_id = ? AND target_id = ? OR user_id = ? AND target_id = ?", A.id, B.id, B.id, A.id).first

... but less readable, and be sure to test it. Good luck!

How to get unique record by multi-attributes

1 Answers