CODEWITHSUNDEEP
phplaraveleloquent
harunB10
harunB10

Reputation: 5207

How to include or exclude where statement in Laravel Eloquent

I need the same query for two different user roles. Difference is only in one whereNotIn condition.

So for the Basic user it would be:

$chart2 = DB::connection('mysql2')->table('tv')
                    ->select('*')
                    ->join('epgdata_channel', 'cid', '=', 'channelid')
                    ->where('ReferenceDescription', $campaign->spotid)
                    ->whereNotIn('ChannelName', $sky)
                    ->get();

And for Premium:

$chart2 = DB::connection('mysql2')->table('tv')
                    ->select('*')
                    ->join('epgdata_channel', 'cid', '=', 'channelid')
                    ->where('ReferenceDescription', $campaign->spotid)
                    ->get();

I know I can do it with simple if statement:

    if($user->userRole == "Basic"){
    //first $chart2
}
    else{
    //second $chart2}

but I have a lots of queries where I need just to add or remove this whereNotin condition and rewriting the queries (using if statement) is not a nice solution.

Upvotes: 0

Views: 1524

Answers (3)

Mayur Sojitra
Mayur Sojitra

Reputation: 712

$userRole = $user->userRole;
$chart2 = DB::connection('mysql2')->table('tv')
            ->select('*')
            ->join('epgdata_channel', 'cid', '=', 'channelid')
            ->where('ReferenceDescription', $campaign->spotid)
            ->where(function ($query) use ($userRole){
                if($userRole == "Basic"){
                    $query->whereNotIn('ChannelName', $sky)
                }
            })
            ->get();

This code worked for me.

Upvotes: 0

ch271828n
ch271828n

Reputation: 17607

Try scope.

In your TVModel.php:

public function scopeConditionalWhereNotIn($query, $doesUse, $col, $val) {
    if($doesUse)
        $query->whereNotIn($col, $val);
}

Usage:

$condi = true;//or false.
$chart2 = TVModel::select('*')
                ->join('epgdata_channel', 'cid', '=', 'channelid')
                ->where('ReferenceDescription', $campaign->spotid)
                ->conditionalWhereNotIn($condi, 'ChannelName', $sky)
                ->get();

Upvotes: 1

Sletheren
Sletheren

Reputation: 2486

Inside your model add this:

public function scopeBasicUser($query,$channel){
return $query->whereNotIn('ChannelName', $channel);
}

and in your controller:

$query = DB::connection('mysql2')->table('tv')
         ->select('*')
         ->join('epgdata_channel', 'cid', '=', 'channelid')
          ->where('ReferenceDescription', $campaign->spotid);
if($user->userRole == "Basic")
    $query = $query->basicUser($channel);
return $query->get();

Upvotes: 0

Related Questions