Drew Adorador Drew Adorador - 2 months ago 7
MySQL Question

Laravel: How to get results based on relationship that needs to count the number of rows

sorry for the title but it is better to explain it. We have built an internal web app that uploads videos where you can comment and like the video just like Instagram. Back-end is using Laravel API. Everyday we need give a shout out to 10 videos that gotten the most like from yesterday and the relationship is like this

Video
- has many likes

Likes
- belongs to a video

class Stage extends Model
{
public function likes()
{
return $this->hasMany('App\likes', 'id');
}...

class Likes extends Model
{
public function likes()
{
return $this->belongsTo('App\Video', 'videoId');
}...


I know we can do the one below but retrieves both count and videoId

DB::table('likes')
->select(DB::raw("
videoId,
count(*) as LikeCount"))
->where('createDate', '>=', Carbon::now()->subDay(1)->toDateString())
->where('createDate', '<', Carbon::now()->toDateString())
->groupBy('videoId')
->get();


Is there a way to implement this in eloquent where I don't need the count just need the id's of the videos with the most like count as I will use the id's to get the collection of video information. Sorry for the noob question, just wondering if there is anymore way of doing it correctly and efficiently because we are expecting a number of users.

Here is the additional information about the table

Schema::create('video', function (Blueprint $table) {
$table->increments('id')->unsigned();
$table->integer('userId')->unsigned();
$table->string('name',255)->nullable();
$table->text('description')->nullable();
$table->string('status', 50)->nullable();
$table->text('video')->nullable();
$table->dateTime('createDate')->nullable();
$table->dateTime('upStageDate')->nullable();
$table->foreign('userId')->references('userId')->on('user')- >onDelete('cascade');
});

Schema::create('like', function (Blueprint $table) {
$table->integer('videoId')->unsigned();
$table->integer('userId')->unsigned();
$table->datetime('likeDate')->nullable();
$table->foreign('userId')->references('userId')->on('user')->onDelete('cascade');
$table->foreign('videoId')->references('id')->on('video')->onDelete('cascade');
});

Answer

One problem is there. you should specify a primary key for the like table which we can uniquely identify each likes. I'm considering it as pk_like_id. Then apply the below query. I assume the videoId in the like table is the foreign key referring to video table. Video::, I used for specifying the video model class. Date comparison you can change according to your functionality. The below query will find only the likes happened yesterday. Not only for the yesterdays video. Try this, it worked for me...

Video::select('video.id', DB::raw('COUNT(like.pk_like_id) as likecount'))
        ->leftJoin('like', 'like.videoId', '=', 'video.id')
        ->where('like.likeDate', '>=', Carbon::now()->subDay(1)->toDateString())
        ->where('like.likeDate', '<', Carbon::now()->toDateString())
        ->groupBy('video.id')
        ->get();