SQL文で質問です。

Question

前提・実現したいこと

MySql, Rails で質問です。sql初心者です。
下記のような4つのテーブル、モデルがあったとして、

【group】

id	name
1	a

【item】

id	group_id	name	amount
1	1	donut	2
2	1	cake	5

【item_material】

id	item_id	material_id	use
1	1	1	5
2	1	2	5
3	2	1	10
4	2	2	10

【material】

id	name
1	sugar
2	flour

model

class Group < ApplicationRecord
  has_many :items
end

class Item < ApplicationRecord
  belongs to :group
  has_many :item_materials
  has_many :materials, through: :item_materials
end

class ItemMaterial < ApplicationRecord
  belongs to :items
  belongs to :materials
end

class Material < ApplicationRecord
  has_many :item_materials
  has_many :items, through: :item_materials
end

やりたいことは、group_id が1の
item の amount と item_material の use を掛けたものの
合計を取得したいです（material_idでgroup byして、material.nameまで取得）。

最終的に欲しいデータは以下のようなイメージです

id	material.name	sum(item.amount * item_material.use)
1	sugar	60
2	flour	75

発生している問題・エラーメッセージ

Every derived table must have its own alias

該当のソースコード

select item_materials.material_id, item_materials.material_name, sum(total) as sum_use
 		from (
 			select (item_materials.use * items.amount) as total from (
 				select item_materials.*, items.id as i_id, items.group_id, items.amount from item_materials
 				left outer join items on item_materials.item_id = items.id
 				 where items.group_id = 1) as im
 			left outer join (select materials.id, materials.name from materials) 
 			on im.material_id = materials.id
			 ) item_materials
		group by item_materials.material_id

試したこと

上記コードを試しましたがエラーが出てしまい、うまくいきません。

少しややこしいですが、ご教授お願い致します。

Answer 1

railsの部分は分かりませんが、出力されているSQLは文法エラーです。
以下のようにならなければなりません。

select item_materials.material_id, item_materials.material_name, sum(total) as sum_use
from (
       select im.*
            , (im.use * im.amount) as total 
       from (
              select item_materials.*, items.id as i_id, items.group_id, items.amount 
              from  item_materials
                    left outer join items 
                    on item_materials.item_id = items.id
              where items.group_id = 1
            ) as im
            left outer join (
              select materials.id, materials.name from materials
            ) as materials
            on im.material_id = materials.id
     ) item_materials
group by item_materials.material_id, item_materials.material_name

Comments

[usa_mal]

ご回答ありがとうございます。頂いたクエリ文を試してみたのですが、
Unknown column 'item_materials.use' in 'field list' というエラーが出てしまいました。

item.materials テーブルに use カラムはあるのですが、、、

[sazi]

演算している箇所は未編集でしたので、回答で訂正しておきました。

[usa_mal]

できました！助かりました。ありがとうございます。