How to get Expiry date flag from pivot table Joining the parent table grouped by parent_id

I have a parent table with jobs and a pivot table with job provider (boards) and expiry dates.

Adjob table

id:1, job_title: Experienced full stack developer, .. .. location:Europe is_active:1

Boards table

id:1, adjob_id:1, board_name:Adzuna, expiry_date:2020-12-20,

id:2 adjob_id:1, board_name:Seek, expiry_date:2020-11-10,

id:3, adjob_id:1, board_name:Jora, expiry_date:2020-11-25,

This is the query used

$ads= Adjob::select([
            'adjobs.id',
            'adjobs.response',
            'adjobs.post_date',
            'adjobs.job_title',
            'adjobs.created_by',
            \DB::raw('(CASE 
                    WHEN (boards.board_name = "Adzuna" && (NOW() < boards.expiry_date && datediff(expiry_date,NOW()) <= 7)) THEN "1" 
                    WHEN (boards.board_name = "Jora" && (NOW() < boards.expiry_date && datediff(expiry_date,NOW()) <= 7)) THEN "1" 
                    WHEN (boards.board_name = "Seek" && (NOW() < boards.expiry_date && datediff(expiry_date,NOW()) <= 7)) THEN "1" 
                    ELSE "0" 
                    END) AS expiry')
            ])
            ->leftJoin('boards', 'adjobs.id', 'boards.adjob_id')
            
            ->where([['adjobs.active',1]])
            ->groupBy('adjobs.id')
            ->get();                    

But it is only checking the first values (i.e) Adzuna expiry only, because it is grouped by Parent table id i.e adjobs.id. How to check all the three expiry date using above the query.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/35DtZH5
via IFTTT