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-29 16:04:06Z

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, 2015 at 16:04

answered Jul 29, 2015 at 15:55

Joseph Silber

221k59 gold badges369 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3718908x100 Over a year ago

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: stackoverflow.com/questions/13760070/…. Thanks again :D

Wader Over a year ago

I'll definitely be adopting this for future use. +1

Joseph Silber · Accepted Answer · 2015-07-29 15:50:44Z

2

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

edited Jul 29, 2015 at 15:50

Joseph Silber

221k59 gold badges369 silver badges292 bronze badges

answered Jul 29, 2015 at 15:46

Wader

9,9081 gold badge39 silver badges38 bronze badges

2 Comments

user3718908x100 Over a year ago

Thank you but Joseph Sibler's solution seems much simpler and works well too. Unless your solution is better performance wise?

Joseph Silber Over a year ago

Calling Input::get() and Input::has() repeatedly is extremely inefficient.

Collectives™ on Stack Overflow

Retrieving records from database using eloquent with optional query parameters

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related