what is the best way to select different data from same table in Laravel?-CodePudding

I have two lists in my index view and both lists show different data from the SAME table depending on whether a question was answered or not.

List of answered questions:

<h1>Answerd</h1>
<ul>
  <li></li>
</ul>

List of NOT answered questions:

<h1>NOT Answerd</h1>
<ul>
  <li></li>
</ul>

Should I execute two selects in my controller and return both results?

$answered = Questions::where('status', 'answered');
$notAnswered = Questions::where('status', 'not-answered');

return view('index', ['answered' => $answered, 'notAnswerd' => $notAnswered]);

Or, Should I select all rows and handle them in my view with IF?

$all = Questions::all();

return view('index', ['all' => $all]);

I believe the first approach is cleaner. But it might take longer to execute on the server. is this right?

Is there a better way of doing it? What is the best practice in this case?

Thanks

CodePudding user response：

None of them, you can easily achieve what you want by grouping your collections.

Saying that you can use groupBy() collections method and have just 1 query and no if conditionals in your view.

// Get all questions
$questions = Questions::all();
// Group collections by a given key
$grouped_questions = $questions->groupBy('status');

// The result will be an array of grouped collections
return view('index', ['all' => $grouped_questions]);

/*
[
        'answered' => [
            ['preperty' => 'value', ............],
            ['preperty' => 'value', ............],
        ],
        'non-answered' => [
            ['preperty' => 'value', ............],
        ],
]
*/

Laravel docs: https://laravel.com/docs/8.x/collections#method-groupby

EDIT: I don't work with blade, just Vue Js at the front, but you should be able to loop over an array with something like this

@foreach($questions['answered'] as $question)
    <li>{{ $question->property }}</li>
@endforeach

@foreach($questions['non-answered'] as $question)
    <li>{{ $question->property }}</li>
@endforeach

CodePudding user response：

Can you please try this:

// Get all questions
$questions = Questions::all();

// Get all answered questions
$answered = $questions->('status', 'answered');.

// Get all not answered questions
$notAnswered = $questions->('status', 'not-answered');

// Pass the variables, note the use of compact (much cleaner)
return view('index', compact('answered', 'notAnswered'));

With this, your application will only have one trip to your database.

CodePudding user response：

Both approaches are fine, depending on what you want to do. Running two queries is always slower than running just one, so if you are looking for performance, go with one query. It also depends on the amount of data that you expect to have, will you be introducing pagination sometime later? As you said you will be having two lists, then in case of pagination, you will want to have two queries for both lists, so you can paginate it properly. Next, you want to think about how you want to organize your frontend. If you want your frontend to be simple and just to spit out the data, without needing to calculate what is what, then two queries are better approach, otherwise, one is just fine. And in the end, it is not a big of a deal to run two queries that are like those that you wrote in your question, but you really need to pay attention to the amount of data each query will produce in both cases, either for one, or two queries.

CodePudding user response：

You could do this

$answered = Questions::whereIn('status', ['answered', 'not-answered')->get();


return view('index', ['answered' => $answered]);