Laravel where and orWhere function

Question

I'm trying to count how many tickets our operators have closed in a week(and per day) I have these 2 queries

    $closedWeek = Ticket::join('activity_log', 'activity_log.rel_id', '=', 'ticket.id')
                ->where([
                    ['activity_log.created_at',  '>', $fri],
                    ['activity_log.user_id', '=', $id],
                    ['activity_log.event_name', '=', 'ticket_closed'],
                    ['number', 'like', 'SD%']
                ])->count();

    $closedWeek2 = Ticket::join('activity_log', 'activity_log.rel_id', '=', 'ticket.id')
                ->where([
                    ['activity_log.created_at',  '>', $fri],
                    ['activity_log.user_id', '=', $id],
                    ['number', 'like', 'SD%'],
                    ['activity_log.event_name', '=', 'ticket_department_updated']
                ])
                ->where(function($query) {
                    $query->where('activity_log.new_value', '=', 'closed Uninvoiced')
                        ->orWhere('activity_log.new_value', '=', 'To Invoice')
                        ->orWhere('activity_log.new_value', '=', 'closed Other')
                        ->orWhere('activity_log.new_value', '=', 'closed Invoiced');
                })->count();
    $closedWeek = $closedWeek + $closedWeek2;

They are bringing back slightly bigger numbers than expected and I think some of them are duplicated. How could I put these into one query using where(function($query)) or do I have to use DB::raw instead? I can then count unique ids

This looks well suited for a `whereIn()` – FatBoyXPC Mar 08 '19 at 17:30 — FatBoyXPC, Mar 08 '19 at 17:30

score 0 · Answer 1 · answered Mar 08 '19 at 17:57

You can use GroupBy(); with id,

Example:

$closedWeek = Ticket::join('activity_log', 'activity_log.rel_id', '=', 'ticket.id')
                ->where([
                    ['activity_log.created_at',  '>', $fri],
                    ['activity_log.user_id', '=', $id],
                    ['activity_log.event_name', '=', 'ticket_closed'],
                    ['number', 'like', 'SD%']
                ])
                ->groupBy('ticket.id')
                ->count();

    $closedWeek2 = Ticket::join('activity_log', 'activity_log.rel_id', '=', 'ticket.id')
                ->where([
                    ['activity_log.created_at',  '>', $fri],
                    ['activity_log.user_id', '=', $id],
                    ['number', 'like', 'SD%'],
                    ['activity_log.event_name', '=', 'ticket_department_updated']
                ])
                ->whereIn('activity_log.new_value', 
                    [   'closed Uninvoiced', 
                        'To Invoice',
                        'closed Other',
                        'closed Invoiced',
                ])
                ->groupBy('ticket.id')
                ->count();
$closedWeek = $closedWeek + $closedWeek2;

OBS:

You dont need '=', on equal wheres, example, where('colun', '=', 'abc'); you can change to where('colun', 'abc');

Please test and see if you have the expected result.

References:

How to get distinct values for non-key column fields in Laravel?

Laravel where and orWhere function

1 Answers1