タグ毎の最新の記事を5件ずつ取得したいです

Question

前提

初学者です。よろしくお願いします。
以下の環境でシステムを実装しています。
cakephp: 3.8.2
mysql: 8.0.18

実現したいこと

多対多でリレーションを紐づけている以下のテーブルがあります。
指定する各タグ毎に新しい順の記事を5件ずつ取得したいです。

## 記事テーブル
CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `text` text NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

## タグテーブル
CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

## 中間テーブル
CREATE TABLE `articles_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article_id` int(11) NOT NULL,
  `tags_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

試した事

指定するタグ毎に、日時とidを降順にソートした記事を取得する所まではできたのですが、タグ毎に取得した記事に対してlimitで取得件数の制限を付けるやり方が分からない状況です。

$this->tags->find()
        ->contain([
            'Articles' => function ($q) {
                return $q->orderDesc('Articles.created', 'Articles.id');
            }
        ])
　　　　 //タグを指定
        ->where(['tags.id IN' => [3, 6, 10]]);

以上です。ご助力やアドバイス等頂けると幸いです。
宜しくお願いします。

Answer 1

SELECT
	*
FROM
	(
		SELECT
			articles.*
			, ROW_NUMBER() OVER (
				PARTITION BY
					articles_tags.tags_id
				ORDER BY
					articles.created DESC
			) AS rank
		FROM
			articles
			INNER JOIN articles_tags
				ON articles.id = articles_tags.article_id
		WHERE
			articles_tags.tags_id IN (3, 6, 10)
	) temp
WHERE
	rank <= 5
ORDER BY
	id DESC

SQLを書くとこういうことでしょうか？

タグの種類が3つ程度であれば、タグ別に5件*3回取得してもそれほど問題にならないとも思います。

Answer 2

Stack Overflow に同様の質問があります。 cakephp - How to limit contained associations per record/group? - Stack Overflow

MySQL専用ですが、ここでの回答にあるカスタムアソシエーション GroupLimitedBelongsToMany を利用するのがよいでしょう。

GroupLimitedBelongsToMany, GroupLimitedSelectLoader クラスを作成して、window-based-group-limited-belongs-to-many-associations.php に記述されているように使用すればよいです。

---

以下は、Tagsを1件のみ取得する場合のみ有効です。

単純にアソシエーションのクエリ部分にlimitを追加すれば良いです。

        ->contain([
            'Articles' => function ($q) {
                return $q
                    ->orderDesc('Articles.created', 'Articles.id')
                    ->limit(5);
            }
        ])

参考までにhasManyでの書き方ですが、belongsToManyでも利用できるはずです。

CakePHP3でhasManyの取得件数を制限する | php-tips.com

Comments

[krom]

返信が遅くなり申し訳ございません。
ご回答ありがとうございます。
頂いた回答を元にcontain内でlimitをかけてみましたが上手くlimitがかからず、5件しか記事の抽出ができませんでした。
サブクエリを組んでみようと奮闘していますがそちらも中々上手く行かない状況で、完全に行き詰ってしまいました・・
大変恐縮なお願いで申し訳ないのですが別解等がありましたらご教示頂けると幸いです。

[nojimage]

すみません、私の回答だとTagsが1件だけのときのみ有効ですね。