Routing in Laravel 4 and MySQL query

Question

I am trying to write a mySQL statement that will fetch a row matching the user_id aswell as all of the other rows in the DB. Currently I am doing it like this, but I'm wondering if there is a better way to perform the query so that it occurs in one single query?

Route::get('user/candles-mix/{user_id?}', function($user_id){
        $candles = DB::select('SELECT * FROM candles WHERE user_id = ? LIMIT 1', array($user_id));
        $candles2 = DB::select('SELECT * FROM candles');
        return Response::json(array($candles, $candles2));
    });

Answer 1

You could just run the select * from candles query, and filter its result by user_id using PHP. This would reduce it to one query. I don't think it's possible to get 2 result sets from a single query, which is what you're looking for.

Route::get('user/candles-mix/{user_id?}', function($user_id){
    $allCandles = DB::select('SELECT * FROM candles');
    $userCandles = array_filter(function($candle) {
        return ($user_id == $candle['user_id']);
    });

    return Response::json(array($userCandles, $allCandles));
});