CODEWITHSUNDEEP
phpmysqlweblaravellaravel-4
Dennis Braga
Dennis Braga

Reputation: 1478

How to assemble a query with various optional parameters in Laravel?

I have a research to do in a database. Not always I'll be using all of parameters. The user may want to research for a name, but not address or the other way around.

I've tried to use advanced wheres and even unions, but none seems to work. All of them give me a SQL error "General error: 1221 Incorrect usage of UNION and ORDER BY".

Here's a piece of code I've tried 

$city_name = ($city_name != null) ? DB::table('cities')->where('name', 'LIKE', "%$city_name%") : DB::table('cities');
$state  = ($state_id != '--') ? DB::table('cities')->where('state_id', '=', $state_id) : DB::table('cities');

$cities = DB::table('cities')->union($city_name)->union($state)->orderBy('name')->get();

But it gives me the above described error.

What I really want to do is to select, dinamically, what parameters I put in the query and even assemble it "on the fly". Does anyone knows how to do that?

If I couldn't make myself clear, please let me know in the comments...

Upvotes: 1

Views: 2402

Answers (1)

Antonio Carlos Ribeiro
Antonio Carlos Ribeiro

Reputation: 87789

I think you need somethink like this:

$query = DB::table('cities');

if ($city_name != null)
{
    $query->where('name', 'LIKE', "%$city_name%");
}

if ($state_id != '--')
{
    $query->where('state_id', '=', $state_id);
}

$cities = $query->orderBy('name')->get();

Upvotes: 4

Related Questions