CODEWITHSUNDEEP
ruby-on-railsactiverecordparent-child
Alejandro Araujo
Alejandro Araujo

Reputation: 149

Get newest child on parent.all in one call - Rails 4

I have 2 models, orders and order_status_details. The last is just a history of status changes for each order.

class Order < ActiveRecord::Base
    has_many :order_status_details, :dependent => :destroy
end

class OrderStatusDetail < ActiveRecord::Base
    belongs_to :order, foreign_key: :order_id
end

Im getting all the orders with this:

@orders = Order.all.order('id DESC')

Now, I want to include the newest status. For example:

+----+-----------+---------------------+---------------------+----------+
| id | status_id | created_at          | updated_at          | order_id |
+----+-----------+---------------------+---------------------+----------+
|  1 |         1 | 2016-01-31 04:00:00 | 2016-01-31 04:00:00 |        7 |
+----+-----------+---------------------+---------------------+----------+
|  2 |         3 | 2016-01-31 05:00:00 | 2016-01-31 05:00:00 |        7 |
+----+-----------+---------------------+---------------------+----------+
|  3 |         5 | 2016-01-31 06:00:00 | 2016-01-31 06:00:00 |        7 |
+----+-----------+---------------------+---------------------+----------+

The correct must be row #3, status #5.

Check my schema.rb:

  create_table "order_status_details", force: :cascade do |t|
    t.integer  "status_id",  limit: 4
    t.datetime "created_at",           null: false
    t.datetime "updated_at",           null: false
    t.integer  "order_id",   limit: 4
  end

  create_table "order_statuses", force: :cascade do |t|
    t.string   "name",       limit: 255
    t.datetime "created_at",             null: false
    t.datetime "updated_at",             null: false
    t.boolean  "paid"
  end

  create_table "orders", force: :cascade do |t|
    t.integer  "customer_id", limit: 4
    t.decimal  "subtotal",                precision: 10
    t.decimal  "tax",                     precision: 10
    t.decimal  "total",                   precision: 10
    t.integer  "invoice",     limit: 4
    t.integer  "shipping_id", limit: 4
    t.datetime "created_at",                             null: false
    t.datetime "updated_at",                             null: false
    t.decimal  "comission",               precision: 10
    t.string   "comment",     limit: 255
  end

How can in one call?

Thanks.

Upvotes: 1

Views: 57

Answers (1)

Arup Rakshit
Arup Rakshit

Reputation: 118271

You need to write the query as:

Order
 .joins(order_status_details: :order_status)
 .order('id DESC')
 .where(
    "order_status_details.status_id = \
     (SELECT max(status_id) FROM order_status_details)"
  )
 .select("orders.*, order_statuses.name")

Upvotes: 1

Related Questions