Laravel select with multiple where in statement

Question

I have tried various methods to resolve this issue, but none worked for me.

1st method:

$title = Character::find($selected_char->id)->title()->where('title', '=', 'Castle'); 
$title = $title->where('title', '=', 'City');
$title = $title->get();

2nd method:

$title = Character::find($selected_char->id)->title()->where('title', '=', 'Castle')->where('title', '=', 'City')->get();

3rd method:

$title = DB::select(DB::raw("select * from titles where titles.char_id = 5 and title = 'Castle' and title = 'City'"));

None of the above methods work. If I take only one where clause it works perfectly. Example:

$title = Character::find($selected_char->id)->title()->where('title', '=', 'City')->get();

$title = Character::find($selected_char->id)->title()->where('title', '=', 'Castle')->get();

I even tried to take another column than title, but it doesn't work with a second where function. I want to retreive the rows from titles table where the title is City AND Castle I have used multiple where clauses before in a single select statement and it worked. Not now. Any suggestions? Thanks in advance.

Answer 1

You said:

I want to retreive the rows from titles table where the title is City AND Castle

You may try this:

$rowCOllection = DB::table('titles')
                   ->whereIn('title',  array('City', 'Castle'))->get();

Using multiple where:

$rowCOllection = DB::table('titles')
                   ->where('title', 'City')
                   ->where('title', 'Castle')->get();

If you want to add another where clause for titles.char_id then you may use it like:

 $rowCOllection = DB::table('titles')
                   ->where('title', 'City')
                   ->where('title', 'Castle')
                   ->where('char_id', 5)->get();

You may chain as much where as you need before you call get() method. You can add the where('char_id', 5) after the whereIn like whereIn(...)->where('char_id', 5) and then call get().

If you have a Title model then you may do the same thing using:

Title::where(...)->where(...)->get();

Same as using DB, only replace the DB::table('titles') with Title, for example:

$rowCOllection = Title::where('title', 'City')
     ->where('title', 'Castle')
     ->where('char_id', 5)->get();

What about Character here ?

Answer 2

I don't really know how work your double ->where( in php, but in sql here is the mistake :

When you say where title = 'a' and title = 'b', it's like you say : ok give me something where 0=1 it returns nothing.

You can do :

select * from titles where titles.char_id = 5 and (title = 'Castle' or title = 'City')

Retrieve all data where title equals castle or city

Or

select * from titles where titles.char_id = 5 and title IN ('Castle','City')

Retrieve all data where title equals castle or city using IN

I'm pretty sure you will find a way to do that in PHP too.

Answer 3

Assuming you are using Laravel 4

And Character is your model extended from Eloquent

don't mix FIND and WHERE.

Find is for single usage find AND sorting afterward (so order by, and etc)

So if you want to chain up your query

Character::where()->where()->where()-get() (don't forget the get or else you wont get a result)

this way you respect eloquent's features.

Note your first method with ->title() is flawed because your calling a function that you custom created inside your model - thats why it wouldn't have worked.

Note: WereWolf Alpha's method will also work IF you don't want to use Eloquent because the code that he presented will work but thats Fluent notation...so take your pick.