joinsをどうしてもしたかった
userとfoodとorderという三つのDBテーブルのうち、foodとorderをjoinsさせて、フロントに持っていきたかったのですが、どうしても二つのテーブルのデータが同時に取得できなかったため、無理矢理joinsさせた愚行をここに記していきます。
※追記
@koki_73さんからコメントをいただき、いい感じにジョインする方法が見つかったので、そちらを「実装方法」に記しておきます。
念のため、無理やりjoinsさせたときの実装方法もそのまま載せておきます。
モデル
モデルの関係性は以下です。
foodとorderは1対nの関係です。
user.rb
has_many :foods
has_many :orders
food.rb
belongs_to :user
has_many :orders,foreign_key:"id",primary_key: "id"
order.rb
belongs_to :make_user,class_name: "User", optional: true
belongs_to :order_user,class_name: "User", optional: true
belongs_to :food, foreign_key:"food_id",optional: true
schema.rb
ActiveRecord::Schema.define(version: 2021_06_17_024851) do
create_table "foods", force: :cascade do |t|
t.integer "user_id", null: false
t.string "name", null: false
t.integer "price", null: false
t.text "description", null: false
t.string "image"
t.integer "count", null: false
t.string "station", null: false
t.boolean "deleted", default: false
t.datetime "created_at", precision: 6, null: false
t.datetime "updated_at", precision: 6, null: false
t.index ["user_id"], name: "index_foods_on_user_id"
end
create_table "orders", force: :cascade do |t|
t.integer "order_user_id", null: false
t.integer "make_user_id", null: false
t.integer "food_id", null: false
t.integer "count", default: 1, null: false
t.string "order_status", default: "0", null: false
t.datetime "created_at", precision: 6, null: false
t.datetime "updated_at", precision: 6, null: false
t.index ["food_id"], name: "index_orders_on_food_id"
t.index ["make_user_id"], name: "index_orders_on_make_user_id"
t.index ["order_user_id"], name: "index_orders_on_order_user_id"
end
create_table "users", force: :cascade do |t|
t.string "provider", default: "email", null: false
t.string "uid", default: "", null: false
t.string "encrypted_password", default: "", null: false
t.string "reset_password_token"
t.datetime "reset_password_sent_at"
t.boolean "allow_password_change", default: false
t.datetime "remember_created_at"
t.string "confirmation_token"
t.datetime "confirmed_at"
t.datetime "confirmation_sent_at"
t.string "unconfirmed_email"
t.string "name"
t.string "address"
t.string "image"
t.string "email"
t.integer "point"
t.string "deleted", default: "f"
t.text "tokens"
t.datetime "created_at", precision: 6, null: false
t.datetime "updated_at", precision: 6, null: false
t.index ["confirmation_token"], name: "index_users_on_confirmation_token", unique: true
t.index ["email"], name: "index_users_on_email", unique: true
t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
t.index ["uid", "provider"], name: "index_users_on_uid_and_provider", unique: true
end
add_foreign_key "foods", "users"
add_foreign_key "orders", "foods"
add_foreign_key "orders", "users", column: "make_user_id"
add_foreign_key "orders", "users", column: "order_user_id"
end
実装方法
@koki_73さんの助言をいただき、苦肉の策を乗り越えたスマートな実装ができたました！
その方法を下記です。
order_controller.rb
tasks = Food.joins(:orders).merge(
Order.where(make_user_id: user.id)
).select("foods.*,orders.*").order(updated_at: "DESC")
render json: {
tasks: tasks,
}, status: :ok
長く書いてきましたが、ここからが愚行の本題です。アンチパターンとしてお納めください。
order_controller.rb
class OrdersController < ApplicationController
def taskIndex
user = User.find_by(id: params[:user_id])
/本来であれば、ここでjoinsして両テーブルから値を取得できれば・・・/
/orderテーブルの中の条件に合ったものをアクティブリレーションとしてをtaskに代入しています/
tasks = Order.joins(:food).where(make_user_id: 5..7).select("foods.*,orders.*").order(updated_at: "DESC")
taskList = []
/以下の塊では、上で該当のデータと合致するfoodデータを見つけて、taskListに統合していきます/
tasks.each do |task|
food = task.attributes
taskList.push food
end
logger.debug(taskList)
render json: {
taskList: taskList,
}, status: :ok
emd
end
返ってきた結果がこちらになります。
結果
Started GET "/api/v1/orders/5/taskIndex?user_id=5" for ::1 at 2021-08-16 14:31:37 +0900
Processing by Api::V1::OrdersController#taskIndex as HTML
Parameters: {"user_id"=>"5", "order_id"=>"5"}
User Load (1.6ms) SELECT "users".* FROM "users" WHERE "users"."id" =
? LIMIT ? [["id", 5], ["LIMIT", 1]]
↳ app/controllers/api/v1/orders_controller.rb:6:in `taskIndex'
testユーザー_4
Order Load (2.8ms) SELECT foods.*,orders.* FROM "orders" INNER JOIN "foods" ON "foods"."id" = "orders"."food_id" WHERE "orders"."make_user_id" BETWEEN ? AND ? ORDER BY "orders"."updated_at" DESC [["make_user_id", 5], ["make_user_id", 7]]
↳ app/controllers/api/v1/orders_controller.rb:24:in `taskIndex'
[{"id"=>24, "order_user_id"=>6, "make_user_id"=>7, "food_id"=>6, "count"=>5, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "user_id"=>1,
"name"=>"フード名_5", "price"=>500, "description"=>"フード_5の説明文です
。", "image"=>nil, "station"=>"5駅", "deleted"=>0}, {"id"=>23, "order_user_id"=>6, "make_user_id"=>7, "food_id"=>6, "count"=>4, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "user_id"=>1, "name"=>"フード名_5", "price"=>500, "description"=>"フード_5の説明文です。", "image"=>nil, "station"=>"5駅", "deleted"=>0}, {"id"=>22, "order_user_id"=>6, "make_user_id"=>7, "food_id"=>6, "count"=>3, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "user_id"=>1, "name"=>"フード名_5", "price"=>500, "description"=>"フード_5の説明文です。", "image"=>nil, "station"=>"5駅", "deleted"=>0}, {"id"=>21, "order_user_id"=>6, "make_user_id"=>7, "food_id"=>6, "count"=>2, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "user_id"=>1, "name"=>"フード名_5", "price"=>500, "description"=>"フード_5の
説明文です。", "image"=>nil, "station"=>"5駅", "deleted"=>0}, {"id"=>20, "order_user_id"=>6, "make_user_id"=>7, "food_id"=>6, "count"=>1, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "user_id"=>1, "name"=>"フ
ード名_5", "price"=>500, "description"=>"フード_5の説明文です。", "image"=>nil, "station"=>"5駅", "deleted"=>0}, {"id"=>19, "order_user_id"=>6,
"make_user_id"=>7, "food_id"=>6, "count"=>0, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "updated_at"=>Wed, 11 Aug
2021 13:38:05 UTC +00:00, "user_id"=>1, "name"=>"フード名_5", "price"=>500, "description"=>"フード_5の説明文です。", "image"=>nil, "station"=>"5駅", "deleted"=>0}, {"id"=>18, "order_user_id"=>5, "make_user_id"=>6, "food_id"=>5, "count"=>5, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_4", "price"=>500, "description"=>"
フード_4の説明文です。", "image"=>nil, "station"=>"4駅", "deleted"=>0},
{"id"=>17, "order_user_id"=>5, "make_user_id"=>6, "food_id"=>5, "count"=>4, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_4", "price"=>500, "description"=>"フード_4の説明文です
。", "image"=>nil, "station"=>"4駅", "deleted"=>0}, {"id"=>16, "order_user_id"=>5, "make_user_id"=>6, "food_id"=>5, "count"=>3, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_4", "price"=>500, "description"=>"フード_4の説明文です。", "image"=>nil, "station"=>"4駅", "deleted"=>0}, {"id"=>15, "order_user_id"=>5, "make_user_id"=>6, "food_id"=>5, "count"=>2, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_4", "price"=>500, "description"=>"フード_4の説明文です。", "image"=>nil, "station"=>"4駅", "deleted"=>0}, {"id"=>14, "order_user_id"=>5, "make_user_id"=>6, "food_id"=>5, "count"=>1, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_4", "price"=>500, "description"=>"フード_4の
説明文です。", "image"=>nil, "station"=>"4駅", "deleted"=>0}, {"id"=>13, "order_user_id"=>5, "make_user_id"=>6, "food_id"=>5, "count"=>0, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フ
ード名_4", "price"=>500, "description"=>"フード_4の説明文です。", "image"=>nil, "station"=>"4駅", "deleted"=>0}, {"id"=>12, "order_user_id"=>4,
"make_user_id"=>5, "food_id"=>4, "count"=>5, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug
2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_3", "price"=>500, "description"=>"フード_3の説明文です。", "image"=>nil, "station"=>"3駅", "deleted"=>0}, {"id"=>11, "order_user_id"=>4, "make_user_id"=>5, "food_id"=>4, "count"=>4, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_3", "price"=>500, "description"=>"
フード_3の説明文です。", "image"=>nil, "station"=>"3駅", "deleted"=>0},
{"id"=>10, "order_user_id"=>4, "make_user_id"=>5, "food_id"=>4, "count"=>3, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_3", "price"=>500, "description"=>"フード_3の説明文です。", "image"=>nil, "station"=>"3駅", "deleted"=>0}, {"id"=>8, "order_user_id"=>4, "make_user_id"=>5, "food_id"=>4, "count"=>1, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_3", "price"=>500, "description"=>"フード_3の説明文です。", "image"=>nil, "station"=>"3駅", "deleted"=>0}, {"id"=>7, "order_user_id"=>4, "make_user_id"=>5, "food_id"=>4, "count"=>0, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:04 UTC +00:00, "user_id"=>1, "name"=>"フード名_3", "price"=>500, "description"=>"フード_3の説
明文です。", "image"=>nil, "station"=>"3駅", "deleted"=>0}]
Completed 200 OK in 66ms (Views: 16.9ms | ActiveRecord: 4.4ms | Allocations: 7133)
分かりにくいですが、foodにしかなかったフードのnameやpriceとorderにしかなかったmake_user_idが同じハッシュ内に入っています。この状態が延々と繰り返された結果が上記です。
結果
[{"id"=>24, "order_user_id"=>6, "make_user_id"=>7, "food_id"=>6, "count"=>5, "order_status"=>"0", "created_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "updated_at"=>Wed, 11 Aug 2021 13:38:05 UTC +00:00, "user_id"=>1,
"name"=>"フード名_5", "price"=>500, "description"=>"フード_5の説明文です
。", "image"=>nil, "station"=>"5駅", "deleted"=>0},
もっとベストプラクティスがあったはずだとは思うのですが、今の自分では見つけられず暫定策として上記を行いました。
他にベストプラクティスがある方は教えていただけると幸いです。