Defining new Query in Laravel 4

Question

I'm currently writing a back-end for a filter using Laravel 4. The view has a table with four columns: id, area, subarea and city. There are four text inputs, respectively, which send user's input to the back-end. Say, a user types in "AMP" in area and "KUAL" in city, the backend should select corresponding models via a LIKE statement.

My idea is to check each input if it's present and chain

->where()

constraints. Something like this:

public function handleAreasFilter() {
    $data = Input::all();

    if ($data['id'] == "" and $data['subarea'] == "" and $data['area'] == "" and $data['city'] == "") {

    // If no filters are provided, we just return 20 subareas.
    // Since subarea, area and city are tied with Eloquent Relationships
    // (subarea belongs to an area, area has many subareas and belongs
    // to a city, city has many areas) and we return the basis model
    // (subarea), we add area and city names via a foreach loop.

        $subs = Subarea::take(20)->get();
        foreach ($subs as $s) {
            $s->area = $s->area()->first()->name;
            $s->city = $s->area()->first()->city()->first()->name;
        }
        return Response::json(array('status' => 'success', 'data' => $subs));
    } else {

        if ($data['id'] != "") {
            $query = Subarea::whereId($data['id']);
        }
        if ($data['subarea'] != "") {
            $query = $query->where('name', 'LIKE', '%' . $data['subarea'] . '%');
        }

        if ($data['area'] != "") {
            $areas = Area::where('name', 'LIKE', '%' . $data['area'] . '%')->get();

            // Here I try to distinguish between the first constraint
            // and all other, to get
            // $query->whereAreaId(..)->orWhereAreaId(..)->orWhere...

            for ($i = 0; $i < $areas->count(); $i++) {
                if ($i == 0) {
                    $query = $query->whereAreaId($areas[$i]->id);
                } else {
                    $query = $query->orWhereAreaId($areas[$i]->id);
                }
            }
        }
        $subs = $query->get();
        foreach ($subs as $s) {
            $s->area = $s->area()->first()->name;
            $s->city = $s->area()->first()->city()->first()->name;
        }
        return Response::json(array('status' => 'success', 'data' => $subs));
    }
}

Well, it's not complete, since I stumbled upon the following issue: I have no idea how to create an empty query. If

$data['id'] == '',

Then $query is undefined when it meets

$query = $query->where...

And I get I have to do something like

$query = (isset($query)) ? $query : Subarea::newQuery();

But I obviously tried this, and it doesn't work. Moreover,

Subarea::all();

is, naturally, a wrong solution here. Well, I hope it wasn't too much to read. Please, help :)

lukasgeiter lukasgeiter · Accepted Answer · 2015-01-21T13:29:01

You can use Subarea::query() to "start" a new query. Then just add the wheres conditionally. A simple example:

$query = Subarea::query();
if($data['id'] !== ''){
    $query->where('id', $data['id']);
}
$result = $query->get();

Defining new Query in Laravel 4

1 Answers