Patrique Patrique - 2 months ago 23
MySQL Question

laravel convert to string mysql

I have this code written in using the laravel and I wonder how to write it in pure SQL without using laravel, as would be?

Route::get('popular', function(){

$media = Media::where('active', '=', '1')->join('media_likes', 'media.id', '=', 'media_likes.media_id')->groupBy('media_likes.media_id')->orderBy(DB::raw('COUNT(media_likes.id)'), 'DESC')->select('media.*')->paginate(30);

$data = array(
'media' => $media,
'categories' => Category::all(),
'pages' => Page::all(),
'settings' => Setting::first(),
);

return View::make('Theme::home', $data);

});

Answer

Using toSql method

Laravel Query builder has a helpful method called toSql which is handy to see what the final SQL query looks like.

Now, when you call the paginate method, you'll get a Paginator and you won't be able to call the toSql method.

Deconstructing paginate

When you use paginate, Laravel will make the following query for each $page:

// Let's simplify the query for now
Media::skip(($page - 1) * $perPage)->take($perPage);

Knowing that, you can use the query builder's method toSql and you'll see the actual sql string.

Supossing $page=3, $perPage=30, and that the name of the table is media, you'll get something like this:

Media::skip(2 * 30)->take(30)->toSql();

select * from media limit 30 offset 60


Now, for your actual query, you can use the following to see the resulting SQL string for the page 3 (as an example):

Media::where('active', '=', '1')
    ->join('media_likes', 'media.id', '=', 'media_likes.media_id')
    ->groupBy('media_likes.media_id')
    ->orderBy(DB::raw('COUNT(media_likes.id)'), 'DESC')
    ->select('media.*')
    ->skip(2 * 30)
    ->take(30)
    ->toSql();

Listening For Query Events

Alternatively, you can set an event listener in your AppServiceProvider, and log each SQL query the application executes.

public function boot()
{
    \DB::listen(function ($query) {
        \Log::debug($query->sql);
        \Log::debug($query->bindings);
        \Log::debug($query->time);
    });
}
Comments