CODEWITHSUNDEEP
mysqlruby-on-railsselectjoinactiverecord
Alejandro Araujo
Alejandro Araujo

Reputation: 149

Returns distinct record in a joins query - Rails 4

I'm trying to get and display an order list including the current status.

@orders = Order.joins(order_status_details: :order_status)
               .order('id DESC, order_status_details.created_at DESC')
               .select("orders.id, order_status_details.status_id, order_statuses.name, order_status_details.created_at")

It works good but is returning all the rows with order ids duplicated like this:

+----+-----------+----------------------+---------------------+
| id | status_id | name                 | created_at          |
+----+-----------+----------------------+---------------------+
|  8 |         1 | Pending              | 2016-01-31 16:33:30 |
|  7 |         3 | Shipped              | 2016-02-01 05:01:21 |
|  7 |         2 | Pending for shipping | 2016-01-31 05:01:21 |
|  7 |         1 | Pending              | 2016-01-31 04:01:21 |
+----+-----------+----------------------+---------------------+

The correct answer must return uniques ids, for the example above should be the first and second row.

I was already trying with distinct on select, .distinct, .uniq and .group but I'm getting an error.

Thanks.

Upvotes: 0

Views: 152

Answers (1)

Hieu Pham
Hieu Pham

Reputation: 6707

First of all, I believe your model is "An Order has many OrderStatusDetail". So that is the reason why you have several different name in your result.

So you can modify the query like this:

    @orders = Order.joins(order_status_details: :order_status)
                   .order('id DESC, order_status_details.created_at DESC')
                   .where('order_status_details.id IN (SELECT MAX(id) FROM order_status_details GROUP BY order_id)')
                   .select("orders.id, order_status_details.status_id, order_statuses.name, order_status_details.created_at")

Ideally, the where condition is used for selecting just the expected id of order_status_details, I use min_id for example, you can modify it as needed

Upvotes: 1

Related Questions