mardi 17 novembre 2020

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.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire