Laravel orderBy with column value

Question

This is the code that I currently have:

$programmefundings = Programmefunding::where('status', '!=', 'draft')->orderByRaw("FIELD(status , 'open', 'announced', 'delayed', 'closed') ASC")->orderBy('date_start', 'desc')->get();

So it gets all of my programmefundings except the ones with the status 'draft', reorders them from 'open' to 'closed' and arranges them by 'date_start'.

I need to reorder the programmefundings which have the status 'announced' by another column 'announcement_date' in 'asc' order without disturbing the 'open' to 'closed' structure it currently has and without effecting any other programmefindings which do not have the status value 'announced'.

Is this possible and does anyone know how to do this?

Thank you!

Answer 1

You should be able to do this with an IF statement.

$programmefundings = Programmefunding::where('status', '!=', 'draft')
    ->orderByRaw("FIELD(status , 'open', 'announced', 'delayed', 'closed') ASC")
    ->orderByRaw("IF(status = 'announced', accouncement_date, date_start) DESC")
    ->get();

Answer 2

I think what you are looking to do is something like this. It will orderby all items with a status of announced first by announcement date, then it will sort everything else by date_start.

$programmefundings = Programmefunding::where('status', '!=', 'draft')
    ->orderByRaw("case when status = 'announced' then announcement_date end asc")
    ->orderBy('date_start', 'desc')->get();