CODEWITHSUNDEEP
phpmysqlsqllaravel
Patrique
Patrique

Reputation: 253

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

});

Upvotes: 1

Views: 959

Answers (2)

whoan
whoan

Reputation: 8521

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);
    });
}

Upvotes: 2

Angad Dubey
Angad Dubey

Reputation: 5452

$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);

Something like this:

SELECT m.* 
FROM
media m
JOIN 
media_likes ml
ON
ml.media_id = m.id
GROUP BY 
ml.media_id
ORDER BY
COUNT(ml.id) DESC

Upvotes: 1

Related Questions