MySQL query convert in Laravel DB query

Question

This is a product catalog page, and I need to get 1 image for a preview.

My query now:

DB::select('SELECT  
                h.*
              , (SELECT link 
                 FROM media_libs m 
                 where h.id=m.have_videos_id limit 1) as link 
            FROM have_videos h')

But I need pagination.

DB::select('SELECT 
                h.*
                , (SELECT link 
                    FROM media_libs m 
                    where h.id=m.have_videos_id limit 1) as link 
            FROM have_videos h')->paginate(15)

So naturally nothing works

How to convert a query in the Facade DB?

Answer 1

I solved my question like that

$products = DB::table('have_videos')
                ->leftJoin('media_libs', function($join) {
                    $join->on('have_videos.id', '=', 'media_libs.have_videos_id')
                        ->select('have_videos_id','link')
                        ->where('kind',1);
                })
                ->distinct()
                ->whereIn('media_libs.ID', 
           [DB::raw('select min(ID) from media_libs group by media_libs.have_videos_id')])
                ->simplePaginate(15);