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

Question

お世話になります。

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

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

この条件でjoinした２つのテーブルで検索するとレコードが重複されるので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の構文についての質問ですが、テーブル設計に関してのご指摘でも大丈夫です。

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

Comments

[hihijiji]

単にWHERE の or を and にするのはダメなのですか？ 別件ですが、その構造だとユーザーがtitleに対して複数のブックマークを設定できませんか？

[annderber]

コメントありがとうございます。ブックマークをしていなくても当日登録したレコードは表示させたいのでor条件にしたいです。同一のtitleに対して複数ブックマークが出来てしまうということでしょうか？それであれば問題ないです

[hihijiji]

了解しました。提示のSQLではdocumentのidしか取れませんよ。他ユーザーのidが入るのは別の問題ですのでそこのSQLを提示してください。

[annderber]

申し訳ありませんdocumentのidしかとれないというのはどういうことでしょうか。確かに提示したsqlでは結果のレコード通りにならないので修正いたしました。

[hihijiji]

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

[annderber]

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

[hihijiji]

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

Answer 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'

Comments

[sazi]

UNIONマージするより、件数的に少ない方に除外条件付けて、union allにした方が高速な気もする。

[annderber]

コメントありがとうございます。

提示いただいたsqlだと同じレコードが２つ取得できてしまいます。
これは重複なしで取得したいです。

[kgm]

あーそっか。重複しちゃいますね。
saziさんのご指摘のように、件数が少ない方(お気に入り)に当日登録されたレコードを除外すべきでした。
こんなもんかな。

SELECT d.*, NULL 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 vc.user_id = '002' AND d.update_date != '2018-11-12'

[sazi]

>KGMさん
それでも駄目ですね。
日付指定された方に指定したユーザーのIDがある場合には、NUll固定ではなく表示しないと駄目なので。
※対応したものは私の回答に追記しています。

[kgm]

当日登録された文章でお気に入りに登録されているものはuser_idを引きたいということですね。
それなら確かにもうちょっと手を加えないといけませんが・・・。
saziさんが提示されたSQLだと、当日登録された文章で指定ユーザがお気に入りに登録していないものまで指定ユーザのuser_idとしちゃってますよね。
論理的に考えて、ちょっと違和感があります。
当日登録＆お気に入り非登録のものはuser_id = nullとすべきなんじゃないかと考えました。
まあ、annderberさん次第ですが。
ということで、最終的にはこんなSQLに。

SELECT d.*, NULL AS user_id
FROM document as d
INNER JOIN view_continue AS vc ON d.id = vc.document_id
WHERE vc.user_id != '002' AND 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 vc.user_id = '002'

[sazi]

>当日登録された文章で指定ユーザがお気に入りに登録していないものまで指定ユーザのuser_idとしちゃってますよね。
いえ、existsで判定しているので、「含んでいたら」です。

そのSQLだと、結合してるので記事が'002'以外のユーザー分の件数になっちゃいますよ。
それに、inner join してるから、view_continueが無ければ抽出されないし。

[kgm]

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

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

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

[annderber]

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

[kgm]

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

Answer 2

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

追記２

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'

Comments

[kgm]

それだと特定のユーザがお気に入りにした文章しか取得できないのでは？
本日登録された文章とユーザがお気に入りに登録した文章の2種類を取得したいという要件だと思うのですが。

[sazi]

確かにor条件になってますね。
だとすると、当日分で他の人がお気に入りしたもののユーザidをどうするのかというのがはっきりしないと、要件が矛盾しますね。

[annderber]

コメントありがとうございます。

参考にさせていただきます。
思ったよりも複雑になってしまうのですね。もっと簡便なsqlでかけるものかと考えていました。
普段簡単なsqlしか書かないので勉強になりました。

[sazi]

性能を意識したので、記述は冗長だったりします。
シンプルなだけの記述を追記してみましたので、確認してみて下さい。

[annderber]

色々なパターンありがとうございます。
確かにレコード数はそこそこある想定だったので性能は意識したいところでした。
まずシンプル版でパフォーマンスを見つつ、場合によって性能意識版も試したい思います。
ありがとうございました。

[annderber]

すいません。
シンプル版試したところ同一レコードが２つ取得されてしまいました。
重複なく取得したのですが、方法ありますでしょうか。

[sazi]

修正しました。

[annderber]

修正ありがとうございます。
とりあえず追記１の方で試させていただいてうまくいきました。
ありがとうございます。
exists周りのsqlの働きがよく分かっていないので調べたいと思います。

[sazi]

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

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

[annderber]

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

[sazi]

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