CODEWITHSUNDEEP
mysqlsqllaravelquery-builder
LocalHorst
LocalHorst

Reputation: 1168

Laravel query: Different where clauses for each select

I'm wondering, if there is a way to declare different where clauses for each select.

Here is what I want to do: My Database looks like this: database Example

with the columns "visible..." indicating, if I want to read out the related value or not. So if "visible_mobile" says "0", I don't want to read out the column "mobile".

Of course I could do it by doing multiple queries like:

$userDetails1 = DB::table('user')
 ->select('mobile', 'id')
 ->where('user_id',  5)
 ->where('visible_mobile',  1)
 ->get();

$userDetails2 = DB::table('user')
 ->select('date_of_birth', 'id')
 ->where('user_id',  5)
 ->where('visible_date_of_birth',  1)
 ->get();

But I'm wondering if I could do this with just one query?

EDIT: the output I'm trying to get should look something linke this: enter image description here

Upvotes: 0

Views: 123

Answers (1)

woodlandtrek
woodlandtrek

Reputation: 146

Using selectRaw() along with MySQL's IF function, you could do something like this:

$userDetails = DB::table('user')
 ->selectRaw('id, user_id, IF(visible_date_of_birth = 1, date_of_birth, NULL) as dob, IF(visible_mobile = 1, mobile, NULL) as mob')
 ->where('user_id',  5)
 ->get();

Since you're using laravel with Eloquent a better way to accomplish this would be to define an accessor for both your mobile and date of birth fields on your model, like this:

function getMobileAttribute()
{
    if($this->visible_mobile == 1){
        return $this->mobile;
    } else {
        return NULL;
    }
}

Upvotes: 3

Related Questions