current pipeline

HistoryModel.php

	function get_message_summary($start_date, $end_date)
	{
		// get the date range correct
		$match_stage = [
			'$match' => [
				'date_sent' => [
					'$gte' => $start_date,
					'$lt'  => $end_date,
				],
			],
		];

		// Define the aggregation pipeline
		$pipeline = [
			$match_stage,
			[
				'$addFields' => [
					'formatted_date' => [
						'$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$date_sent'],
					],
				],
			],
			[
				'$lookup' => [
					'from'         => 'clickhistory',
					'localField'   => 'tracking',
					'foreignField' => 'click_id',
					'as'           => 'click_info',
				],
			],
			[
				'$unwind' => [
					'path'                       => '$click_info',
					'preserveNullAndEmptyArrays' => TRUE,
				],
			],
			[
				'$lookup' => [
					'from'         => 'conversionhistory',
					'localField'   => 'tracking',
					'foreignField' => 'click_id',
					'as'           => 'conversion_info',
				],
			],
			[
				'$unwind' => [
					'path'                       => '$conversion_info',
					'preserveNullAndEmptyArrays' => TRUE,
				],
			],
			[
				'$group' => [
					'_id'            => [
						'drop_date'     => '$formatted_date',
						'drop_id'       => '$drop_id',
						'sender_id'     => '$sender_id',
						'list_id'       => '$list_id',
						'vendor'        => '$vendor',
						'campaign_id'   => '$campaign_id',
						'carrier_group' => '$carrier_group',
						'carrier'       => '$carrier',
						'template_id'   => '$template_id',
						'status'        => '$status',
					],
					'total_messages' => ['$sum' => 1],
					'total_payout'   => ['$sum' => ['$toDouble' => '$conversion_info.payout']],
					'total_clicks'   => ['$sum' => ['$cond' => [['$gt' => ['$click_info.click_id', NULL]], 1, 0]]],
				],
			],
			[
				'$sort' => ['_id.drop_date' => 1],
			],
		];

		try {
			// Execute the aggregation
			$result = $this->collection->aggregate($pipeline);
		} catch (\MongoDB\Driver\Exception\Exception $exception) {
			exit('Exception: ' . $exception->getMessage());
		}
		// Collect and return the results
		$summary = [];

		foreach ($result as $doc) {
			$summary[] = $doc;
		}

		return $summary;
	}