where('videos.created_at', '>', '2020.07.26 14:16:29') not working for left joined table - Eloquent, Laravel

Question

everybody. I have written this code which should return most liked videos posted after the specified date. But the code returns older results than the specified date. I have posted only one video after 2020.07.26 and in spite of this Eloquent returns 8 older videos.

$query = Video::select('videos.id',DB::raw('group_concat(DISTINCT videos.code) AS code'),DB::raw('group_concat(DISTINCT videos.title) AS title'),DB::raw('group_concat(DISTINCT videos.created_at) AS createdDate'),DB::raw('count(reactions.id) AS reactCount'))
      ->leftJoin('reactions','reactions.at_video','=','videos.id')
      ->where('reactions.reaction',1)
      ->orWhereNull('reactions.reaction')
      ->where('videos.created_at', '>', '2020.07.26 14:16:29')
      ->groupBy('videos.id')
      ->orderBy('reactCount','DESC')
      ->get();

      dd($query);

Then I found this code on the stockflow where the solution was whereColumn. But it did not help me. Laravel showed me another error.

whereColumn example from another post on stackoverflow

$query = DB::table('messages')
    ->leftJoin('participants', function ($join) {
        $join->on('messages.thread_id', '=', 'participants.thread_id')
            ->on('messages.user_id', '!=', 'participants.user_id');
    })
    ->select('messages.created_at as message_date', 'participants.last_read as last_read')
    ->whereColumn('messages.created_at', '>', 'participants.last_read')->get();

The reason for this is that I group the results. The videos.created field returns the value as many times as the likes of the video. To solve this I used DISTINCT

DB::raw('group_concat(DISTINCT videos.created_at) AS createdDate')

Then I added createdDate in the WHERE() and unfortunately I got one another error again.

->where('createdDate', '>', '2020.07.26 14:16:29')

And this is the error I mean. As many time I got it... I almost loved it ;)

And... I wrote a query code which returns right results and ran in a native php page. This is what Laravel should do but it does not...

SELECT videos.id AS id, group_concat(videos.code) AS code, group_concat(videos.title) AS title, group_concat(DISTINCT videos.created_at) AS createdDate, count(reactions.id) AS reactCount
      FROM videos 
      LEFT JOIN reactions ON videos.id = reactions.at_video
      WHERE (reactions.reaction = 1 or reactions.reaction IS NULL)
      AND videos.created_at > '2020.07.27'
      GROUP BY videos.id
      ORDER BY reactCount DESC

I thought I would use custom query code in Laravel as the final solution but I have to say but one more again.

$videos = DB::select("SELECT videos.id AS id, group_concat(videos.code) AS code, group_concat(videos.title) AS title, group_concat(DISTINCT videos.created_at) AS createdDate, count(reactions.id) AS reactCount
      FROM videos 
      LEFT JOIN reactions ON videos.id = reactions.at_video
      WHERE (reactions.reaction = 1 or reactions.reaction IS NULL)
      AND videos.created_at > '2020.07.27'
      GROUP BY videos.id
      ORDER BY reactCount DESC")->paginate(16);

Please someone help me to find a solution. I'm really tired man.

Answer 1

At least I found a solution. This is not the best way maybe but it works without any issues yet.

I added them to my controller

use Illuminate\Pagination\Paginator;
use Illuminate\Database\Eloquent\Collection;

in the function

$query = DB::select(DB::raw("SELECT videos.id AS id, group_concat(DISTINCT videos.code) AS code, group_concat(DISTINCT videos.title) AS title, group_concat(DISTINCT videos.created_at) AS createdDate, count(reactions.id) AS reactCount
FROM videos 
LEFT JOIN reactions ON videos.id = reactions.at_video
WHERE (reactions.reaction = 1 or reactions.reaction IS NULL)
AND videos.created_at > '2021.07.27'
GROUP BY videos.id
ORDER BY reactCount DESC"));
    
// Added this three lines of code to make as the results array as a collection
$collection = new Collection($query);
$page = $request->get('page') ? (int)$request->get('page') : 1;
$videos = $collection->forPage($page, 16); 

dd($videos);