CODEWITHSUNDEEP
laravel
Chaiwat Jo
Chaiwat Jo

Reputation: 143

How to query multiple whereMonth in orWhere query for laravel

I'm newbie in Laravel and I need to do laravel query by getting result like below sql.

SQL

select * from `tb_project_participate` 
where 
    `project_id` = 20 and 
    `department_id` = 1 and 
    (
        month(`created_at`) = 10 or 
        (month(`created_at`) = 11) or 
        (month(`created_at`) = 12)
    )

Below is Laravel code that I use.

$participate = ProjectParticipate::where('project_id' , $project->project_id)
            ->where('department_id', $auth->department_id)
            ->whereMonth('created_at' , 10)
            ->orWhere(function($query){$query->whereMonth('created_at',11); })
            ->orWhere(function($query1){$query1->whereMonth('created_at',12); })
            ->get();

And I have result as below, that is not a query that I want. SQL

select * from `tb_project_participate` 
where 
    `project_id` = 20 and 
    `department_id` = 1 and 
    month(`created_at`) = 10 or 
    (month(`created_at`) = 11) or 
    (month(`created_at`) = 12)

Appreciated for advice how to write Laravel query to get result as whsh, And so sorry for my English. Thank you very much.

Upvotes: 2

Views: 1907

Answers (3)

Arfan
Arfan

Reputation: 169

the accepted answer that recommended should be whereIn instead of ->where(DB::RAW('month(created_at)'), $months) Hope this helps others

Upvotes: 0

Indra
Indra

Reputation: 702

Please google it first and foremost. I think this question has been answered tens of times on stackoverflow and hundreds or times on the web.

Try this:

$participate = ProjectParticipate::where('project_id' , $project->project_id)
                                ->where('department_id', $auth->department_id)
                                ->where(function($query){
                                $query->whereMonth('created_at' , 10);
                                $query->orWhere(function($query2){
                                   $query2->whereMonth('created_at',11); });
                                $query->orWhere(function($query1){
                                   $query1->whereMonth('created_at',12); });
                                })->get();

Or this (recomended):

We try to make code as general as possible so I want the months as a paramater. No real reason for $auth either, since it's an extra operation to do: $auth = \Auth::user(). Just use \Auth::user()

$participate = ProjectParticipate::where('project_id' , $project->project_id)
                                   ->where('department_id', \Auth::user()->department_id)
                                   ->where(DB::RAW('month(created_at)'), $months)
                                   ->get();

Where $months is this array: [10,11,12]

Basically we have a query where we add all the or statements in. Check the manual for reference. You didn't mention the version, but not much change has been done in the last 3 years so here is the latest version: https://laravel.com/docs/5.7/queries#parameter-grouping

Upvotes: 2

Jayashree
Jayashree

Reputation: 163

try something like this 

 $participate = ProjectParticipate::where('project_id' , $project->project_id)
                ->where('department_id', $auth->department_id)
                ->whereRaw('month(created_at) in (10,11,12))
                ->get()

or you can also do as

$participate = ProjectParticipate::where('project_id' , $project->project_id)
                ->where('department_id', $auth->department_id)
                ->whereIn(DB::raw('month(created_at)'),[10,11,12])
                ->get()

Upvotes: 3

Related Questions