いいね数の集計をLaravelで実装したい

Question

問題

下記のSQL文をクエリビルダに変換し、投稿（posts)のいいね（likes）の総数を取得したい。

SELECT *,count(likes.id) AS likes_count
FROM posts
JOIN likes
on posts.id = likes.post_id
GROUP BY posts.id

このSQL文を実際に実行すると、画像の通り無事にデータを取得できる。

このSQL文をクエリビルダに変換したい。

SELECT *,count(likes.id) AS likes_count
FROM posts
JOIN likes
on posts.id = likes.post_id
GROUP BY posts.id

現時点、自分で書いたクエリビルダ

$query = DB::table('posts')
            ->join('likes', 'posts.id', '=', 'likes.post_id')
            ->select('*', 'count(likes.id) AS likes_count')
            ->groupBy('posts.id')
            ->get();

上記のクエリビルダだとA column was not foundのエラーが発生する。

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'count(likes.id)' in 'field list' (SQL: select *, `count(likes`.`id)` as `likes_count` from `posts` inner join `likes` on `posts`.`id` = `likes`.`post_id` group by `posts`.`id`)

count(likes.id)がエラーの原因で、うまくlikes（いいね）の集計ができていない。
後一歩まで来ているが、残りわずかというところで実装できていません。
お忙しいと思いますがご教授いただければ、幸いです。
お願い致します

詳細情報

こちらでは、より詳しい情報を記述していきます。

環境

Laravel6.0
(laravel6.6にアップグレードしても改善なし）

モデル

postモデル

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class post extends Model
{
    protected $fillable = [
        'title', 'body', 'user_id'
    ];

    public function user()
    {
        return $this->belongsTo('App\User');
    }
    public function tags()
    {
        return $this->belongsToMany('App\Models\tag', 'post_tags');
    }

    public function likes()
    {
        return $this->hasMany('App\Models\like');
    }
}

likeモデル

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class like extends Model
{
    protected $fillable = [
        'user_id', 'post_id'
    ];

    public function post()
    {
        return $this->belongsTo('App\Models\post');
    }

    public function user()
    {
        return $this->belongsTo('App\User');
    }
}

マイグレーション

         Schema::create('posts', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users');
            $table->string('title');
            $table->text('body');
            $table->timestamps();
        });

        Schema::create('likes', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users');
            $table->unsignedBigInteger('post_id');
            $table->foreign('post_id')->references('id')->on('posts');
            $table->timestamps();
        });

背景

指定のいいね数の記事のみを取得する、絞り込み検索を実装しようとしています。

元々はwithCount()を利用して、集計を実施していました。
その集計を元に指定のいいね数（最低:$lgtm_min,　最高: max)の範囲の記事を取得。

getだとエラーなし

    $query = DB::table('posts')
            ->join('likes', 'posts.id', '=', 'likes.post_id')
            ->select('*', 'count(likes.id) AS likes_count')
            ->groupBy('posts.id');

    if ($lgtm_min !== null) {
      $query->having('likes_count', '>=', $lgtm_min);
    }
    if ($lgtm_max !== null) {
      $query->having('likes_count', '<=', $lgtm_max);
    }

    $posts = $query->orderBy('posts.created_at', 'desc')->get();

get()で取得する分にはエラーなしで実行できます。

paginateだとエラー

    $query = DB::table('posts')
            ->join('likes', 'posts.id', '=', 'likes.post_id')
            ->select('*', 'count(likes.id) AS likes_count')
            ->groupBy('posts.id');

    if ($lgtm_min !== null) {
      $query->having('likes_count', '>=', $lgtm_min);
    }
    if ($lgtm_max !== null) {
      $query->having('likes_count', '<=', $lgtm_max);
    }

    $posts = $query->orderBy('posts.created_at', 'desc')->paginate(20);

エラー

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'likes_count' in 'having clause' (SQL: select count(*) as aggregate from `posts` where (`posts`.`title` like %100% or `posts`.`body` LIKE %100%) having `likes_count` >= 1)

調べてみると、
withCount()は、having()と->paginate()と併用して利用できないことが判明しました。

【Laravelのissue】withCount(), having() and paginate() together not working

そのため、withCount()を利用せずにlikes_countを定義する必要が出てきました。

そして、下記のSQL文をクエリビルダに変換しようと考えています

SELECT *,count(likes.id) AS likes_count
FROM posts
JOIN likes
on posts.id = likes.post_id
GROUP BY posts.id

    $query = Post::withCount('likes');

    if ($lgtm_min !== null) {
      $query->having('likes_count', '>=', $lgtm_min);
    }
    if ($lgtm_max !== null) {
      $query->having('likes_count', '<=', $lgtm_max);
    }

    $posts = $query->orderBy('posts.created_at', 'desc')->paginate(20);

今はSQLで解決しようとしてますが、これが難しければ集計テーブルを作成し、それをjoinで結合するかも検討しています。

抜粋なしのコード

要望があったので、記述します。

        $query = Post::withCount('likes');
        $posts = DetailedSearch::DetailedSearch($query, $keyword, $request);
        return view('posts.index', compact('posts', 'all_posts_count', 'keyword'));

<?php

namespace App\Services;

class DetailedSearch
{
  public static function DetailedSearch($query, $keyword, $request)
  {
    // values
    $order = $request->input('order');
    $lgtm_min = $request->input('lgtm-min');
    $lgtm_max = $request->input('lgtm-max');
    $period = $request->input('period');
    $period_start = $request->input('period-start');
    $period_end = $request->input('period-end');

    //keyword
    $keyword_space_half = mb_convert_kana($keyword, 's');
    $keywords = preg_split('/[\s]+/', $keyword_space_half);
    preg_match_all('/#([a-zA-z0-9０-９ぁ-んァ-ヶ亜-熙]+)/u', $keyword, $match);
    $no_tag_keywords = array_diff($keywords, $match[0]);
    $tags = $match[1];

    //LGTM sum search
    if ($lgtm_min !== null) {
      $query->having('likes_count', '>=', $lgtm_min);
    }
    if ($lgtm_max !== null) {
      $query->having('likes_count', '<=', $lgtm_max);
    }



    // search order
    if ($order == 'new') {
      //paginate()だとエラーあり。こちらもget()にすればエラーなし
      $posts = $query->orderBy('posts.created_at', 'desc')->paginate(20);
    } else {
      //get()だとエラーなし。こちらもpaginate()にすればエラーあり
      $posts = $query->orderBy('likes_count', 'desc')->get();
    }
    return $posts;
  }
}

ご教授のほどよろしくお願い致します。

Comments

[phper.k]

Laravel5.7では確かに不具合があるようですが、6.x に不具合があるとは書かれていません。
具体的にどう言う不具合があったのですか？

wiCount と paginate は同時に使ったこともあるし、何か不具合が会ったこともありません。

[gyu]

コメントありがとうございます
他のissueのページでのやりとりでは、laravel6でも発生しているというコメントがありました。
そのissueから、リンクを貼ったissueページに辿り着きました。一番わかりやすい例として添付しております。
そのページを閉じてしまったので、今はどのタイトルかは覚えていませんが、like_countでエラーが起こるのが現状です。

[phper.k]

実際うまくいかなかったコードを提示してください。
Eloquent を使ったコードの方ですよ。

[gyu]

かなり長いですが、追加しました！
お願いします

[phper.k]

detailどうのの部分は本件の本筋ではないので、Post とLike で説明すればいい？

[phper.k]

↑の回答お願いします。

あと Like は Post と User をリレーションする belongsToMany で問題ない？

[gyu]

Post.phpとLike.phpを追加しました。
抜粋箇所が今回の大きな箇所なので、PostとLikeで大丈夫です

[gyu]

likeはhasManyです。

Answer 1

回答

時間がく雑回答ですみません。
これでどうでしょうか？

$query = DB::table('posts')
            ->join('likes', 'posts.id', '=', 'likes.post_id')
-            ->select('*', 'count(likes.id) AS likes_count')
+            ->select(
+                '*',
+                DB::raw('count(likes.id) AS likes_count')
+            )
            ->groupBy('posts.id')
            ->get();

参考： エスケープなしのSQLを使用する

解説

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'count(likes.id)' in 'field list' (SQL: select *, `count(likes`.`id)` as `likes_count` from `posts` inner join `likes` on `posts`.`id` = `likes`.`post_id` group by `posts`.`id`)

laravel関数の解釈で

SELECT *,count(likes.id) AS likes_count

としてほしいところを、

select *, `count(likes`.`id)` as `likes_count` from

ってなってるのが、カラムが無いよってエラーになっています。

回答保留

背景の withCount() まわりは別内容（？）なので一旦検索が出来ますように。

Comments

[gyu]

ありがとうございます！
解説でより一層理解できました！！
無事に検索できました。

Answer 2

＝＝＝＝　消した　＝＝＝＝

Laravel の開発にあたっては、以下のプラグインは必ずインストすべし。

https://github.com/barryvdh/laravel-debugbar

基本的に、LaravelのDB周りの処理は、 Eloquent で書くべき。

DB::select()なんて滅多に使う場面がない。よほど、Eloquent では十分なパフォーマンスが出ない場合などに限られる。DBに用意されているメソッドはEloquentでも使えるのだから、Eloquentでかけないはずがない。

Laravel のベストプラクティスがまとめられているので、参考にすると良いでしょう。

https://github.com/alexeymezenin/laravel-best-practices

Comments

[phper.k]

なんだよ。
コメントもつけないんかよ

[gyu]

問題を解決し、teratailの確認が遅れ申し訳ないです。
Laravel のベストプラクティスがまとめられているページを紹介いただきありがとうございます
記事を参考にEloquentを利用した記述に変更しました。本当にありがとうございました