CODEWITHSUNDEEP
phplaraveleloquent
tmartin314
tmartin314

Reputation: 4171

Laravel Eloquent search two optional fields

I'm trying to search two optional tables using eloquent:

$users  = User::where('ProfileType', '=', 2)
                ->where(function($query) {
                    $query->where('BandName', 'LIKE', "%$artist%");
                    $query->or_where('Genre', 'LIKE', "%$genre%");
                })->get();

This works fine for return all results when a user does an empty search, but I am not sure how to adjust this for to search for bandname when that is present and vise versa.

Upvotes: 1

Views: 8743

Answers (4)

Ali Raza
Ali Raza

Reputation: 720

$query = FormEntry::with('form')->where('domain_id', $id);

$query->where(function($query) use ($search, $start, $limit, $order, $dir) {                
     $query->where('first_name', 'LIKE', "%{$search}%")
           ->orWhere('last_name', 'LIKE', "%{$search}%")
           ->orWhere('email', 'LIKE', "%{$search}%")
           ->offset($start)
           ->limit($limit)
           ->orderBy($order, $dir);
      });

$entries = $query->get();
$totalFiltered = $query->count();

Upvotes: 0

vFragosop
vFragosop

Reputation: 5773

Just to explain what happens on answer below:

Eloquent does a tricky thing here: When you call User::where(...) it returns a Database\ Query object. This is basically the same thing as DB::table('users')->where(...), a chainable object for constructing SQL queries.

So having:

// Instantiates a Query object
$query = User::where('ProfileType', '=', '2');
$query->where(function($query) {
    // Adds a clause to the query
    if ($artist = Input::get('artist')) {
        $query->where_nested('BandName', 'LIKE', "%$artist%", 'OR');
    }
    // And another
    if ($genre = Input::get('genre')) {
        $query->where_nested('Genre', 'LIKE', "%$genre%", 'OR');
    }
});

// Executes the query and fetches it's results
$users = $query->get();

Upvotes: 10

tmartin314
tmartin314

Reputation: 4171

Building on Vinicius' answer here's what worked:

// Instantiates a Query object
$query = User::where('ProfileType', '=', '2');

// Adds a clause to the query
if ($artist = Input::get('artist')) {
    $query->where('BandName', 'LIKE', "%$artist%");
    // Temp Usernamesearch
    $query->or_where('NickName', 'LIKE', "%$artist%");
}

// Genre - switch function if artist is not empty
if ($genre = Input::get('genre')) {
    $func = ($artist) ? 'or_where' : 'where';
    $query->$func('Genre', 'LIKE', "%$genre%");
}

// Executes the query and fetches it's results
$users = $query->get();

Turns out that the second optional field must use or_where only if $artist is not set.

Thanks for your help

Upvotes: 2

dynamo
dynamo

Reputation: 382

I think this is what youre after. Your view would have a form to search artist/genre one or the other can be set, or both, or none.

$users = User::where('ProfileType', '=', 2);

if (Input::has('artist')) {
    $users = $users->where('BandName', 'LIKE', '%'.Input::get('artist').'%');
}

if (Input::has('genre')) {
    $users = $users->where('Genre', 'LIKE', '%'.Input::get('genre').'%');
}

$users = $users->get();

Upvotes: 1

Related Questions