Patrique Patrique - 1 month ago 12
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_likes.media_id')->groupBy('media_likes.media_id')->orderBy(DB::raw('COUNT('), 'DESC')->select('media.*')->paginate(30);

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

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



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_likes.media_id')
    ->orderBy(DB::raw('COUNT('), 'DESC')
    ->skip(2 * 30)

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) {