Group result set by combination of multiple columns

Question

I have table called negotiations like this

id    seller_id  buyer_id property_id
1        4           190      33123
2        4           190      33123
3        5           191      34000
4        5           191      34000
5        6           200      35000

I can fetch all by doing:

Negotiation.all

I want fetch everything, but grouped by seller_id-buyer_id-property_id combination. This in the above example I want to return three groups.

Is this possible in my rails application?

What do you mean by "grouped by seller_id-buyer_id-property_id"? I can see the three groups but what would the result look like? — mu is too short, Mar 07 '16 at 02:09

potashin · Accepted Answer · 2021-07-06T09:44:41.193

9

You can use Enumerable#group_by :

Negotiation.all.group_by do |item| 
  [item.seller_id, item.buyer_id, item.property_id]
end

edited Jul 06 '21 at 09:44

answered Mar 07 '16 at 02:23

potashin

44,205
11
83
107

wow can't believe this wasn't asked on SO before – american-ninja-warrior Mar 07 '16 at 02:34

score 0 · Answer 2 · edited May 23 '17 at 12:31

I guess you could achieve this creating a scope that takes some args that you define in your Negotiation model. I am not 100% on the correct syntax, but believe this approach is the most appropriate.

scope :seller_buyer_property_combo, ->(seller, buyer, property) { 
   where("negotiations.seller_id = ? AND 
          negotiations.buyer_id = ? AND
          negotiations.property_id = ?", seller.id, buyer.id, property.id) }

This syntax follows the advice given here:

In Rails for example, the question mark is replaced by an argument given by a variable of the library's programming language (Ruby), e.g.:

Table.where("column = ?", "value")

and it automatically quotes arguments to avoid bugs and SQL injection, generating a statement like:

SELECT * FROM Table WHERE column = 'value';

The quoting would save us in case of something like:

Table.where("column = ?", "; INJECTION")

If you want to reuse the combinations and there are not so many perhaps you can just hard-code the combinations into a few different scopes (alleviating the needs for the args which may cause confusion in the ordering etc.)

Group result set by combination of multiple columns

2 Answers2