Home>

Laravel5.5
MySQL

I'm working on a site like Twitter that has a "Like" feature for posts.

Therefore, I want to display the number of COUNT (*) queried in the database in View.

(SQL) SELECT *, COUNT (*) FROM goodpost_favorite GROUP BY favorite_id;

Results
+ ---- + --------- + ------------- + -------------------- -+ --------------------- + ---------- +
id | user_id | favorite_id | created_at | updated_at | COUNT (*) |
+ ---- + --------- + ------------- + -------------------- -+ --------------------- + ---------- +
| 3 | 2 | 1 | 2018-08-25 15:48:58 | 2018-08-25 15:48:58 | 4 |
| 4 | 2 | 2 | 2018-08-25 15:49:13 | 2018-08-25 15:49:13 | 3 |
| 5 | 2 | 3 | 2018-08-25 15:49:24 | 2018-08-25 15:49:24 | 3 |
| 10 | 3 | 4 | 2018-08-25 15:51:18 | 2018-08-25 15:51:18 | 2 |
| 9 | 3 | 5 | 2018-08-25 15:51:17 | 2018-08-25 15:51:17 | 3 |
17 | 4 | 6 | 2018-08-25 15:53:07 | 2018-08-25 15:53:07 | 2 |
16 | 4 | 7 | 2018-08-25 15:53:06 | 2018-08-25 15:53:06 | 2 |
18 | 4 | 8 | 2018-08-25 15:54:50 | 2018-08-25 15:54:50 | 1 |
+ ---- + --------- + ------------- + -------------------- -+ --------------------- + ---------- +

I want to get only COUNT (*) and display "Like" on the post.
The goodpost id is stored in favorite_id in the goodpost_favorite table.

Currently, View is like this, it is not displayed neatly.


Mr. A
Oh oh.
[4 likes! ]


Mr. A
Oh oh.
[3 likes! ]


Mr. A
Oh oh.
[3 likes! ]


Mr. A
Oh oh.
[1 Like! ]


Mr. A
It ’s kakikuke.
[4 likes! ]


Mr. A
It ’s kakikuke.
[3 likes! ]


Mr. A
It ’s kakikuke.
[3 likes! ]


Mr. A
It ’s kakikuke.
[1 Like! ]


B-san
XYZ ...
[4 likes! ]


B-san
XYZ ...
[3 likes! ]


B-san
XYZ ...
[3 likes! ]


B-san
XYZ ...
[1 Like! ]


As you can see, the number of posts multiplied by the number of posts will be displayed, and you will not be able to display "Like!"
Maybe it's because you haven't been able to extract what was passed in the array, but I don't know how to do it.
I would like to ask you to professor how the code will be displayed on the controller side or the blade side.
Thank you in advance.

goodposts.blade.php
<ul>
@foreach ($goodposts as $goodpost)
 @foreach ($favorite_counts as $favorite_count)
    <? php $user = $goodpost->user;?>
     <li>
        <div>
            <div>
                {!! link_to_route ('users.show', $user->name, ['id' =>$user->id]) !!}<span>posted at {{$goodpost->created_at }}</span>
            </div><div>
                <p>{!! nl2br (e ($goodpost->content)) !!}</p>{{$favorite_count->count}} Good!
            </div>
            <div>
                    @include ('goodpost_favorite.favorite_button', ['goodpost' =>$goodpost])
            </div>
        </div>
    </li>
   @endforeach
@endforeach
</ul>
{!! $goodposts->render () !!}
GoodpostsController.php @index
public function index ()
    {
        $data = [];
        if (\ Auth :: check ()) {
            $user = \ Auth :: user ();
            $goodposts = $user->feed_goodposts ()->orderBy ('created_at', 'desc')->paginate (10);
            $favorite_counts = \ DB :: table ('goodpost_favorite')
                             ->select (\ DB :: raw ('favorite_id, count (*) as count'))
                             ->groupBy ('favorite_id')
                             ->get ();
            $data = [
                'user' =>$user,
                'goodposts' =>$goodposts,
            ];
        }
        return view ('welcome', $data, [
            'favorite_counts' =>$favorite_counts,
        ]);
    }
goodpost_favorite_table.php
<? php
use Illuminate \ Support \ Facades \ Schema;
use Illuminate \ Database \ Schema \ Blueprint;
use Illuminate \ Database \ Migrations \ Migration;
class CreateGoodpostFavoriteTable extends Migration
{
    / **
     * Run the migrations.
     *
     * @return void
     * /
    public function up ()
    {
        Schema :: create ('goodpost_favorite', function (Blueprint $table) {
            $table->increments ('id');
            $table->integer ('user_id')->unsigned ()->index ();
            $table->integer ('favorite_id')->unsigned ()->index ();
            $table->timestamps ();
            // foreign key setting
            $table->foreign ('user_id')->references ('id')->on ('users')->onDelete ('cascade');
            $table->foreign ('favorite_id')->references ('id')->on ('goodposts')->onDelete ('cascade');
            // Do not allow duplicate combinations of user_id and favorite_id
            $table->unique (['user_id', 'favorite_id']);
        });
    }
    / **
     * Reverse the migrations.
     *
     * @return void
     * /
    public function down ()
    {
        Schema :: dropIfExists ('goodpost_favorite');
    }
}


表示 It is a display to draw.


Mr. A
Oh oh.
[4 likes! ]


Mr. A
It ’s kakikuke.
[3 likes! ]


B-san
XYZ ...
[3 likes! ]


  • Answer # 1

    From the controller,

    $user = \ Auth :: user ();
    $goodposts = $user->feed_goodposts ()->orderBy ('created_at', 'desc')->paginate (10);

    You only need to pass this area, and then you can turn SQL on Blade.

    InApp \ Goodpost.php

    public function favorites ()
    {
        return belongsToMany ('App \ User', 'goodpost_favorite', 'favorite_id', 'user_id');
    }

    Since you should have defined a relationship like ↑,

    goodposts.blade.php

    @foreach ($goodposts as $goodpost)
        {{$goodpost->favorites->count ()}} Good!
    @endforeach
    Can you do what you want to do with

    ?

Related articles