gistfile1.txt

$product_tracking_query = (new ProductTracking)->newQuery();
$product_tracking_query->select('product_id', 'subscriber_id', DB::raw('count(*) as total'));
$product_tracking_query->with('product');
$product_tracking_query->whereBetween('product_tracking.created_at', $daterange);
$product_tracking_query->orderBy('total', 'DESC');
$product_tracking_query->groupBy('product_id');
$product_tracking_collection = $product_tracking_query->paginate(($request->input('per_page') !== null) ? $request->input('per_page') : 10);

$product_ids = $product_tracking_collection->pluck('product_id')->toArray();

$message_guided = ProductTracking::whereIn('product_id', $product_ids)
				->where('msg_type',0)
				->select(DB::raw('count(*) as message_guided'))
				->groupBy('product_id')
				->get()
				->keyBy('product_id');

$message_nlp = ProductTracking::whereIn('product_id', $product_ids)
				->where('msg_type',1)
				->select(DB::raw('count(*) as message_nlp'))
				->groupBy('product_id')
				->get()
				->keyBy('product_id');

$user_male = ProductTracking::whereIn('product_id', $product_ids)
				->withCount([
					'subscriber' => function($q){
						$q->where('gender', '=', 'male');
					}
				])
				->groupBy('product_id')
				->get()
				->keyBy('product_id');

$user_female = ProductTracking::whereIn('product_id', $product_ids)
				->withCount([
					'subscriber' => function($q){
						$q->where('gender', '=', 'female');
					}
				])
				->groupBy('product_id')
				->get()
				->keyBy('product_id');

$product_tracking_collection->map(function ($product_tracking) use ($message_guided, $message_nlp, $user_male, $user_female){
	$product_tracking['message_guided'] = array_key_exists($product_tracking->product_id,$message_guided) ? $message_guided[$product_tracking->product_id]->message_guided : 0;
	$product_tracking['message_nlp']    = array_key_exists($product_tracking->product_id,$message_nlp) ? $message_nlp[$product_tracking->product_id]->message_nlp : 0;
	$product_tracking['user_male']      = array_key_exists($product_tracking->product_id,$user_male) ? $user_male[$product_tracking->product_id]->subscriber_count : 0;
	$product_tracking['user_female']    = array_key_exists($product_tracking->product_id,$user_female) ? $user_female[$product_tracking->product_id]->subscriber_count : 0;
});