Retrieving records from database using eloquent with optional query parameters

Question

i have the following block of code in my Resource Controller:

$travel_company_id = Input::get('travel_company_id');
$transport_type = Input::get('transport_type');
$route_type = Input::get('route_type');

$travelRoutes = TravelRoute::where('travel_company_id', $travel_company_id)
                        ->where('transport_type', $transport_type)
                        ->where('route_type', $route_type)
                        ->get();

Now what this does is it gets travelRoutes based on the parameters supplied. What i want is for it to do is perform a search based on the available parameters, that way if $route_type is empty the search will be performed only on travel_company_id and transport type.

Also if all the parameters are empty then it will simply do a get and return all available records.

I know i can do this with lots of if statements but then if i add a new parameter on the frontend i will have to add it to the backend as well, I was wondering if there was a much simpler and shorter way to do this in laravel.

Joseph Silber · Accepted Answer · 2015-07-29T16:04:06.863

3

The where method accepts an array of constraints:

$constraints = array_only(Input::all(), [
    'travel_company_id',
    'transport_type',
    'route_type',
]);

$routes = TravelRoute::where($constraints)->get();

Warning: do not use Input::only() instead of array_only(). They're not the same.

Input::only() fills in any missing items with null, which is not what you want here.

edited Jul 29 '15 at 16:04

answered Jul 29 '15 at 15:55

Joseph Silber

214,931
59
362
292

Wow this worked great! had an issue where my javascript api was passing 'undefined' to the undefined parameters because i hardcoded them into the url causing it not to work but i found the solution for that here: http://stackoverflow.com/questions/13760070/angularjs-passing-data-to-http-get-request. Thanks again :D – user3718908x100 Jul 29 '15 at 16:30
I'll definitely be adopting this for future use. +1 – Wader Jul 29 '15 at 16:38

score 2 · Answer 2 · edited Jul 29 '15 at 15:50

This is pretty hacky and if you spend some time developing a solution I'm sure it could be much nicer. This assumes all the fields in the getSearchFields() function match the input names from the form and database.

/**
 * Search fields to retrieve and search the database with. Assumed they match the 
 * column names in the database
 */
private function getSearchFields()
{
    return ['travel_company_id', 'transport_type', 'route_type'];
}

public function search()
{
    // Get a new query instance from the model
    $query = TravelRoute::query();

    // Loop through the fields checking if they've been input, if they have add 
    //  them to the query.
    foreach($this->getSearchFields() as $field)
    {
        if (Input::has($field))
        {
            $query->where($field, Input::get($field));
        }
    }

    // Finally execute the query
    $travelRoutes = $query->get();
}

Thank you but Joseph Sibler's solution seems much simpler and works well too. Unless your solution is better performance wise? — user3718908x100, Jul 29 '15 at 16:32
Calling `Input::get()` and `Input::has()` repeatedly is *extremely* inefficient. — Joseph Silber, Jul 29 '15 at 16:36

Retrieving records from database using eloquent with optional query parameters

2 Answers2

Linked