CODEWITHSUNDEEP
sqllaravellaravel-5eloquent
Benjoe
Benjoe

Reputation: 466

2 condition in 1 where clause in Laravel Builder

I want to convert my query into laravel builder format:

This is my working query:

SELECT * FROM tickets
WHERE tic_status_id = 65
AND (tic_escalation_date1 IS NULL OR tic_escalation_date1 < DATE('2019-06-22 13:00:00'))
AND (tic_escalation_date2 IS NULL OR tic_escalation_date2 < DATE('2019-06-22 13:00:00'))
AND (tic_escalation_date3 IS NULL OR tic_escalation_date3 < DATE('2019-06-22 13:00:00'))

I'm trying to use whereNull and orWhere but It's not working.

Upvotes: 4

Views: 4768

Answers (3)

porloscerros Ψ
porloscerros Ψ

Reputation: 5078

You can use Parameter Grouping to constrain the or where.

Passing a Closure into the where method instructs the query builder to begin a constraint group. The Closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group.

Then, inside the constrain you can use whereNull() to add a "where null" clause to the query, and orWhereDate() to add a "or where date" statement to the query. Note the whereDate method will take the date part out from the datetime expression.

DB::table('tickets')
            ->where('tic_status_id', '=', 65)
            ->where(function ($query) {
                $query->whereNull('tic_escalation_date1')
                      ->orWhereDate('tic_escalation_date1', '<', '2019-06-22 13:00:00');
            })
            ->where(function ($query) {
                $query->whereNull('tic_escalation_date2')
                      ->orWhereDate('tic_escalation_date2', '<', '2019-06-22 13:00:00');
            })
            ->where(function ($query) {
                $query->whereNull('tic_escalation_date3')
                      ->orWhereDate('tic_escalation_date3', '<', '2019-06-22 13:00:00');
            })
            ->get();

Upvotes: 5

Dhananjay Kyada
Dhananjay Kyada

Reputation: 1036

You can try the following code:

    $tickets = DB::table('tickets');
    $tickets->where('tic_status_id', 65);
    $tickets->where(function($query){
        $query->whereNull('tic_escalation_date1');
        $query->orWhereDate('tic_escalation_date1', '<', DATE('2019-06-22 13:00:00'));
    });
    $tickets->where(function($query){
        $query->whereNull('tic_escalation_date2');
        $query->orWhereDate('tic_escalation_date2', '<', DATE('2019-06-22 13:00:00'));
    });
    $tickets->where(function($query){
        $query->whereNull('tic_escalation_date3');
        $query->orWhereDate('tic_escalation_date3', '<', DATE('2019-06-22 13:00:00'));
    });
    $ticket_data = $tickets->get();

Please refer laravel for more information. You can use "orWhere" in place of "orWhereDate" if you have a column other then "DateTime" type.

Upvotes: 2

ManojKiran
ManojKiran

Reputation: 6341

can you try this

Method One

$query = DB::table( 'tickets')
            ->where( 'tic_status_id' ,'=',65)
            ->whereNull( 'tic_escalation_date1')
            ->orWhere( 'tic_escalation_date1', '<', "DATE('2019-06-22 13:00:00')")
            ->whereNull( 'tic_escalation_date2')
            ->orWhere( 'tic_escalation_date2', '<', "DATE('2019-06-22 13:00:00')")
            ->whereNull( 'tic_escalation_date3')
            ->orWhere( 'tic_escalation_date3', '<', "DATE('2019-06-22 13:00:00')");

Method Two I am Not Sure About the this query will Work Fine

$query = DB::table( 'tickets')
            ->where( 'tic_status_id' ,'=',65)
            ->whereNull( 'tic_escalation_date1')
            ->orWhereDate( 'tic_escalation_date1', '<', "('2019-06-22 13:00:00')")
            ->whereNull( 'tic_escalation_date2')
            ->orWhereDate( 'tic_escalation_date2', '<', "DATE('2019-06-22 13:00:00')")
            ->whereNull( 'tic_escalation_date3')
            ->orWhereDate( 'tic_escalation_date3', '<', "DATE('2019-06-22 13:00:00')");

Upvotes: 0

Related Questions