1対多の関係にあるテーブル間で複数の検索条件に一致して重複してしまうレコードの抽出

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 611

annderber

score 87

お世話になります。

イメージ説明

documentテーブルがメインの文章テーブル、view_continueはブックマークテーブルのようなイメージです。
登録した文章はユーザーごとにお気に入り設定が出来るようになっていて、view_continueにはuser_idも登録されるようになっています。

〇データサンプル
documentテーブル

id title report update_date
1 test text 2018-11-12

view_continueテーブル

id document_id user_id
1 1 001
2 1 002

画面に一覧表示する際にいくつか検索条件があります。
1.documentのレコードで登録された日付が当日中のレコード
or
2.view_continueレコードにログインユーザーのidが存在する場合

この条件でjoinした2つのテーブルで検索するとレコードが重複されるのでgroup byします。

SELECT `d`.`id`, `d`.`title`, `d`.`report`, `d`.`update_date`, `vc`.`user_id` FROM `document` as `d` left join `view_continue` as `vc` on `d`.`id` = `vc`.`document_id` where `update_date`= '2018-11-12' || `user_id` = '002' GROUP by `d`.`id`;

とした場合にuser_idが002のユーザーがログインして画面を表示させると、

id title report update_date user_id
1 test text 2018-11-12 001

となり、他ユーザーのidが紛れ込んでしまいます。
これを検索したuser_idが入るようになる処理を教えていただきたいです。
documentテーブルのレコードは1万件程度の想定です。
基本的にSQLの構文についての質問ですが、テーブル設計に関してのご指摘でも大丈夫です。

よろしくお願いいたします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hihijiji

    2018/11/13 10:12

    idしかとれないって表現は適切ではなかったですね。正確にはdocumentのid以外は不定値になるです。GROUP BYで指定したカラムと集計値以外は通常使いません。

    キャンセル

  • annderber

    2018/11/13 10:21

    申し訳ありませんが、hihijijiさんが求めているsqlがどういうものであるのかが分かりかねます。

    キャンセル

  • hihijiji

    2018/11/13 10:33

    この場合SELECT 句の`d`.`id`以外は使っちゃだめってことだけです。

    キャンセル

回答 2

checkベストアンサー

+1

group by じゃなくて、単にDISTINCTすればいいような。

SELECT distinct d.id, d.title, d.report, d.update_date, vc.user_id 
FROM document as d inner join view_continue as vc on d.id=vc.document_id 
where d.update_date= '2018-11-12' 
  and vc.user_id='002'


ユーザーは限定されるので、特に結合しなくても固定で良いと思います。
こちらの方が高速かもしれません。
※[]はパラーメータ

SELECT id, title, report, update_date, [ユーザーid] as user_id
FROM document d
where update_date=[日付]
  and exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id)

追記

ログインしたユーザーでなければ、user_idを取得しないという解釈で。

SELECT id, title, report, update_date
, case when exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id)
  then [ユーザーid] end as user_id
FROM document d
where update_date=[日付]
  or exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id)


または、(見た目はすっきりするけど、前のよりは遅いと思われる)

SELECT *,case when user_exists then [ユーザーid] end as user_id
FROM (
  SELECT id, title, report, update_date
        ,exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id) as user_exists
  from document
) as d
where update_date=[日付]
  or user_exists

追記2

UNION クエリー版

SELECT d.*
     , case when exists(select 1 from view_continue where user_id='002' and id=d.id)
       then '002' end as user_id
FROM document as d
WHERE d.update_date='2018-11-12' 
UNION ALL
SELECT d.*, vc.user_id 
FROM document as d
    INNER JOIN view_continue AS vc ON d.id = vc.document_id
WHERE d.update_date<>'2018-11-12' and vc.user_id = '002'

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/13 11:49 編集

    追記2も駄目ですね。検証できていなくて手間取らせてしまいましたね。すみません。
    一応修正しておきました。

    面倒なのは指定した日付の記事に複数のユーザーがいいねをしている時に、ログインユーザが含まれている場合のみそのユーザー名を表示する仕様です。
    結局指定した日付の件数を記事の件数に合わせるには、selectでのサブクエリーしかなさそうですね。

    キャンセル

  • 2018/11/13 13:31

    テーブル設計を変えるのもありでしょうか。
    documentテーブルにカラムを一つ追加して、お気に入りしたユーザー名をカンマ区切りで入れるとか。
    アンチパターンになりますが。

    キャンセル

  • 2018/11/13 13:39

    テーブルの設計は正規化されたものだと思います。
    ユーザー名をカンマ区切りにして保持すると、インデックスは当てに出来なくなります
    カンマ区切りというのはgroup_concat()を使用すれば可能ですので、SQLの表現としては分かりやすくなるかもしれませんが、性能には期待はできなくなります。

    キャンセル

+1

要件をまとめると、一覧表示する文章は以下の2つですね?

  • 当日登録された文章
  • ユーザがお気に入りに登録した文章

これらを重複なしで取得したいと。
単純にそれぞれ別にSELECTしてUNIONで繋げではどうでしょうか。

SELECT d.*, NULL AS user_id 
FROM document as d
WHERE d.update_date= '2018-11-12' 
UNION
SELECT d.*, vc.user_id 
FROM document as d
    INNER JOIN view_continue AS vc ON d.id = vc.document_id
WHERE vc.user_id = '002'

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/13 16:11

    要件がはっきりしないので議論してもアレなんですが・・・。
    質問文には以下の2条件が抽出対象となっています。

    1.documentのレコードで登録された日付が当日中のレコード
    or
    2.view_continueレコードにログインユーザーのidが存在する場合

    つまり、当日登録分のdocumentに関してはログインユーザーがview_continueにある無しに関わらず(お気に入り登録の有無に関わらず)、すべて取得するのでは?

    キャンセル

  • 2018/11/13 16:16 編集

    kgmさんのsqlはちゃんと確認できていませんが、
    要件としては当日登録したdocumentのレコードはview_continueの有無に関わらず表示させます。
    view_continueは古い(当日以前)documentレコードを継続して表示させるために使用します。

    キャンセル

  • 2018/11/14 23:07

    でしたら、やはりsaziさんの追記2以外のSQLでは、view_continueにない当日分が取得できていないとこうことですね。
    追記2はview_continueにない当日分も取得できるのですが、ログインしたユーザのIDが入ってしまうと。
    ああ、やっと結論にたどり着けたw

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.22%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる