CODEWITHSUNDEEP
laravellaravel-5
iAmGroot
iAmGroot

Reputation: 868

Join multiple variable query in single variable query in laravel

Here is my sql query. I am trying to join three different variable in single variable, which will join in a single query. 

    $rData['ReportSent'] =  self::select(DB::raw('count(resident_emails.id) as ReportSent'), "residents.id as resident_id", "resident_emails.id")
                ->join("resident_emails","resident_emails.resident_id","=","residents.id")
                ->whereBetween('resident_emails.created_at', array($start, $end))
                ->where('residents.community_id', $communityId)
                ->where('residents.care_level', 'LIKE', '%'.$carelevel.'%')
                ->where('resident_emails.mail_type', 'Wellness Profile')
                ->groupBy('resident_emails.resident_id')
                ->get()->toArray();

    $rData['ReportWithPhotoSent'] =  self::select(DB::raw('count(resident_emails.id) as ReportWithPhotoSent'), "residents.id as resident_id", "resident_emails.id")
                ->join("resident_emails","resident_emails.resident_id","=","residents.id")
                ->whereBetween('resident_emails.created_at', array($start, $end))
                ->where('residents.community_id', $communityId)
                ->where('residents.care_level', 'LIKE', '%'.$carelevel.'%')
                ->where('resident_emails.mail_type', 'Wellness Profile and Photos')
                ->groupBy('resident_emails.resident_id')
                ->get()->toArray();

     $rData['MessageSent'] =  self::select(DB::raw('count(resident_emails.id) as MessageSent'), "residents.id as resident_id", "resident_emails.id")
                ->join("resident_emails","resident_emails.resident_id","=","residents.id")
                ->whereBetween('resident_emails.created_at', array($start, $end))
                ->where('residents.community_id', $communityId)
                ->where('residents.care_level', 'LIKE', '%'.$carelevel.'%')
                ->where('resident_emails.mail_type', 'Message')
                ->groupBy('resident_emails.resident_id')
                ->get()->toArray();

i am tryring this 
$rData['SentMailCount'] =  self::select(DB::raw('count(resident_emails.id) as ReportWithPhotoSent'), "residents.id as resident_id",             "resident_emails.id")
            ->join("resident_emails","resident_emails.resident_id","=","residents.id")
            ->whereBetween('resident_emails.created_at', array($start, $end))
            ->where('residents.community_id', $communityId)
            ->where('residents.care_level', 'LIKE', '%'.$carelevel.'%')
            ->where('resident_emails.mail_type', 'Wellness Profile and Photos')
        ->orWhere('resident_emails.mail_type', 'Wellness Profile')
        ->orWhere('resident_emails.mail_type', 'Message')
            ->groupBy('resident_emails.resident_id')
            ->get()->toArray();

But i want wellness profile count , Wellness Profile and Photos COUNT , mESSAGE cOUNT ON THREE DIFFRENT COLUMN

I want this in single query on single variable. I am trying to join three different variable in single variable. which will join in a single query.

Upvotes: 0

Views: 660

Answers (1)

party-ring
party-ring

Reputation: 1871

I would suggest storing mail types as an array and taking advantage of whereIn. You can also group by multiple columns if they are comma separated.

$mailTypes = ['Message', 'Wellness Profile and Photos', 'Wellness Profile'];

$allTypes = self::select(
    DB::raw('count(resident_emails.id) as MessageSent'), 
    "residents.id as resident_id", 
    "resident_emails.id"
)->join("resident_emails","resident_emails.resident_id","=","residents.id")
    ->whereBetween('resident_emails.created_at', array($start, $end))
    ->where('residents.community_id', $communityId)
    ->where('residents.care_level', 'LIKE', '%'.$carelevel.'%')
    ->whereIn('resident_emails.mail_type', $mailTypes)
    ->groupBy('resident_emails.mail_type', 'resident_emails.resident_id')
    ->get()
    ->toArray();

This gets all of the above in one query - not sure what you want your output data structure to be, so you may have to tweak a little.

Upvotes: 1

Related Questions