row count from second table with more than one table join

I have three table:

TABLE1:

jobposts (id,user_id,qualification_id)

TABLE2:

appliedjobs (id,jobposts_id,message)

TABLE3:

qulificationmasters (id, qualificationame)

jobposts_id from TABLE is foreign key of id of jobposts

I need all records from jobposts with row count of appliedjobs

$jobposteddetails = DB::table('jobposts')
    ->rightJoin('qulificationmasters','qulificationmasters.id', '=', 'jobposts.qualification_id')
    ->select('jobposts.*','qulificationmasters.QualificationName')
    ->select(DB::raw('count(*) as counts, id') WHERE jobposts.id = appliedjobs.jobposts_id)
    ->where('jobposts.user_id', '=', $user->id)
    ->get();

I need rowcount of appliedjobs with all rows from above query.

vinubangs

6 years ago

Level 1

I tried by this:

$jobposteddetails = DB::table('jobposts')
    ->rightJoin('qulificationmasters','qulificationmasters.id', '=', 'jobposts.qualification_id')   
    ->leftJoin('appliedjobs','appliedjobs.jobposts_id', '=', 'jobposts.id')
   ->select('jobposts.*','qulificationmasters.QualificationName', DB::raw('count(appliedjobs.id) as counts'))
            ->where('jobposts.user_id', '=', $user->id)
            ->groupBy('appliedjobs.id')
            ->get();

But gives error like: it wants every column in groupby of jobposts and qulificationmasters.id

Tray2

6 years ago

Level 73

Something like

$jobPosts = DB:SELECT('SELECT j.*, count(a.id) applicants FROM jobposts j, appliedjobs a WHERE a.user_id = ? AND j.id = a.jobpost_id', $user->id);

vinubangs

6 years ago

Level 1

Thankyou for reply.

But my case is something different. Please see my above try, which is posted after question.

Please or to participate in this conversation.