how to add conditional where clause in sql-CodePudding

Looking for improved answer

In Laravel, I am using a raw query. My question is how to add where clause depending on variable value

i.e. if $cid is present then the query should be

    select * from user where aid=2 and cid=1;

If it is not present

    select * from user where aid=2;

Ideally, I can do it like this

if($cid) {
    $query = DB::select("select * from user where aid=2 and cid=1");
} else {
    $query = DB::select("select * from user where aid=2");
}

Is there any way to do this without the above method?

CodePudding user response：

This can be achieved with conditional clauses.

$users = DB::table('users')
    ->where('aid', 2)
    ->when($cid, function ($query) {
        $query->where('cid', 1);
    })
   ->get();

CodePudding user response：

Please contextualize your question well, we don't know what kind of condition you are talking about, nor in what sense your question is asked.

normally the comment above would be enough but it is necessary to specify

Here are some examples from the documentation

$users = DB::table('users')
            ->where('votes', '=', 100)
            ->where('age', '>', 35)
            ->get();

$users = DB::table('users')->where('votes', 100)->get();

You may also pass an array of conditions to the where function. Each element of the array should be an array containing the three arguments typically passed to the where method:

   $users = DB::table('users')->where([
['status', '=', '1'],
['subscribed', '<>', '1'],
])->get();

I encourage you to read the documentation which is very clear on this subject and to come back to me in case of misunderstanding here