Query Help

[clcarver]

Hello, I have been searching on how to accomplish this for the last few days so this is my last ditch effort to see if this is even possible. I am trying to build the following query using the query builder:

db.work_orders.aggregate([ { '$match': { '$and': [ {"main_work_center": {'$in': ['WC_1', 'WC_2', 'WC_3', 'WC_4']}}, {'statuses.system': {'$in':["REL", "CRTD"]}}, {'statuses.system': {'$nin':["TECO", "CNF", "CLSD"]}}, {'scheduled_start_date': {'$lte': 20210712}}, {'scheduled_finish_date': {'$gte': 20210712}}, {'$or' : [ {'last_call.cycle_days': {'$gte': 30}}, {'maintenance_plan': null} ]} ], }, }, { '$addFields': { 'plannedWork': {'$sum': "$operations.planned_duration"} } }, { '$match': { '$and': [ { '$or' : [ {'plannedWork': {'$gte': 4}}, {'order_type': {'$in':["T1", "T3", "T6"]}} ]} ] } }, {'$sort': {'main_work_center': 1}} ])

This works great and I get the results from my database application.

I run into the issue translating to laravel:

WorkOrder::whereIn('statuses.system', ['REL', 'CRTD']) ->whereNotIn('statuses.system', ['CNF']) ->whereNotIn('statuses.system', ['TECO', 'CNF', 'CLSD']) ->whereNotIn('statuses.user', ['CNCL']) ->where('main_work_center', ['WC_1', 'WC_2', 'WC_3', 'WC_4']) ->where('scheduled_start_date', '<=', 20210712) ->where('scheduled_finish_date', '>=', 20210712) ->where(function ($q) { $q->where('last_call.cycle_days', '>=', 30) ->orWhereNull('maintenance_plan'); }) ->whereRaw(function ($collection) { return $collection->aggregate([ '$addFields' => [ 'plannedWork' => ['$sum' => '$operations.planned_duration'] ] ]); }) ->orderBy('main_work_center') ->paginate(10);

The whereRaw aggregate does not add the field to the returned results. Also adding the additional filters after adding the new field does not work either.

I would also be ok with just running that raw query directly if that is possible? Using the WorkOrder::raw() does not work.

Any help or pointing in the right direction would be great. Thank you!