外部結合したテーブルを重複処理してから関連テーブルのカラムでソートしたい

Question

外部結合したテーブルをGROUPしてから関連テーブルのカラムでソートしたい

以下の関係の４つのテーブルがあります。

報告には１人のユーザー
ユーザーは複数の部署に所属しています。

#報告
class DailyReport
  belongs_to :user

#ユーザー
class User
  has_many :daily_reports

#中間テーブル
class DepartmentsUser
  belongs_to :department
  belongs_to :user

#部署
class Department
  has_many :users

やりたいこと

現在、報告を「報告に紐づくユーザーが所属する部署の名前」で並びかえしようと思っています。
ただし、ユーザーが所属する部署が複数の場合は（※部署の優先度が一番高いもの）でソートしたい。

※部署の優先度については、優先度順に部署のIDが取得できています。

試したこと

# 部署のIDを優先度に配列で取得
sorted_department_ids = Department.departments_sort.ids

#ソート条件を作成
sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ["field(departments.id ,?)",sorted_department_ids])

daily_reports = DailyReport.all
#各テーブルを外部結合したものを部署の優先度順に並び替え、uniqで重複を取り除いている。
daily_reports.uniq("daily_reports.id").from(daily_reports.joins("LEFT OUTER JOIN `users` ON `users`.`id` = `daily_reports`.`user_id` LEFT OUTER JOIN `departments_users` ON `departments_users`.`user_id` = `users`.`id` LEFT OUTER JOIN `departments` ON `departments`.`id` = `departments_users`.`department_id`").order(sanitized_query).select("*")).select("*")

#部署名でソート
daily_reports = daily_reports.order("departments.name DESC")

エラーメッセージ

4行目において以下のエラーメッセージが出力されていました。

Mysql2::Error: Duplicate column name 'id': SELECT DISTINCT * FROM (SELECT * FROM `daily_reports` LEFT OUTER JOIN `users` ON `users`.`id` = `daily_reports`.`user_id` LEFT OUTER JOIN `departments_users` ON `departments_users`.`user_id` = `users`.`id` LEFT OUTER JOIN `departments` ON `departments`.`id` = `departments_users`.`department_id`ORDER BY field(departments.id ,26,27,29,25,32,33,28,34,35,36,37,38)) subquery

ご教示お願い致します。

Comments

[Orlofsky]

質問に各テーブルのCREATE TABLE を載せては？

[Orlofsky]

SQL 中に GROUP BY はありません。提示するSELECTを間違えていませんか？それともタイトルの間違い？

[Orlofsky]

教授 → 教示

[Orlofsky]

本文の1行目はタイトルと同じだから不要。直す気もないようだし。

Answer 1

必要な項目を全て記述するのは動的でなくなるためできれば避けたいと思っております。

というのはモラルが低いです。
どこでどのテーブルやどのカラムをを使っているかを明示的に記述してあるかは
今後起きるであろう変更に如何に容易に対応できるかがとっても大切なことです。
保守性を考えてコードを書くのが真のプログラマーです。

SELECT DISTINCT
    `users`.`id`
  , `daily_reports`.`user_id`
  , `departments`.`id` AS `departments_id`
  , `departments_users`.`user_id` AS `departments_users_id`
以下略

Answer 2

必要な項目を全て記述するのは動的でなくなるためできれば避けたい

まず考え方がおかしい
select句に*を使うのはデバッグレベルの話なので
必要なカラムをすべて列記するのが正解

そしてお書きになったSQLではどうやっても無理です

create table tbl_a(id int,aid int);
insert into tbl_a value(1,100),(2,200),(3,300);
create table tbl_b(id int,bid int);
insert into tbl_b value(1,200),(2,300),(3,300);

• OK

select * from tbl_a left join tbl_b on aid=bid

• NG

select * from (
select * from tbl_a left join tbl_b on aid=bid
) as sub

同じカラム名があるテーブルは存在しないのでサブクエリとしてダメです

MySQL関してはjoinする条件カラム名が一緒の場合に限って
USINGをつかって回避することは可能です。
(上記のSQL文とやっていることは違うので注意)

select * from (
select * from tbl_a left join tbl_b using(id)
) as sub

しかし命題を見るかぎりこの方法に適用する条件があいません

Answer 3

Mysql2::Error: Duplicate column name 'id':

というメッセージが示す通りです。

daily_reports、departments_users、departments
それらのテーブルそれぞれにIDという項目があるのでしょうね。

それを結合してたものをselect * で取得しそれをさらにdistinctしているからその際にIDという項目が重複しているという事です。

subquery内で、select で*ではなく項目名を指定するようにしてみてください。

Comments

[r_u]

ご回答ありがとうございます。
教えていただいた通り、項目名を指定で期待通りの処理はできました。
ただ、必要な項目を全て記述するのは動的でなくなるためできれば避けたいと思っております。

他に良い方法はありませんでしょうか？

部署を外部結合する際に優先度の高いもの１つだけを取得できればdistinctしなくて済みそうなのですが。。。

[sazi]

DISTINCTしなくていいように、重複にならないような結合にすれば良いかと思います。
テーブルの定義が明確ならアドバイスは出来ますけど。