SELECT query with LEFT JOIN and multiple ORDER BY conditions via CodeIgniter's active record syntax

I have written a query with codeigniters database library, it looks like this,

public function getCandidateCredits($candidate_id)
{
    $this->db->select('*')
    ->from('credits')
    ->join('candidate_has_credits', 'candidate_has_credits.credits_credit_id = credits.credit_id', 'left')
    ->where('credits.candidates_candidate_id', (int)$candidate_id)
    ->order_by('candidate_has_credits.date_created', 'DESC')
    ->order_by('credits.credit_position', 'DESC');
    
    $query = $this->db->get();
    
    return $query->result_array();
}

What this query is meant to do is, get all a members credits, and the order them firstly by date (newest first), then by credit position (highest number first).

I am having a couple of problems though, the credits should be ordered by date, but only if there is no credit position, if there is a credit position (0 - 999) then that should take precendence where ordering the credits, if there is a date and a credit position then the credits should be ordered by credit position and then date.

Is this possible? I feel like I am miles away from where I need, the results I return seem to be return no obvious ordering. Not sure if it makes a difference but the date_created field is a DATETIME.

You are correctly using a left join, but have put the order by clauses in the wrong order, so first of all, flip them:

->order_by('credits.credit_position', 'DESC')
->order_by('candidate_has_credits.date_created', 'DESC')

This should do it, except that now those candidate_has_credit rows that do not have corresponding credits records (so the credit_position is null) will be in the end, and I assume you want those on top.

There is a small trick to push null values to top when using DESC sorting providing you know the maximum value available in that field:

->order_by("ifnull('credits.credit_position',1000) desc,candidate_has_credits.date_created desc")

Note that I am using the form of order_by method which contains only one parameter, that one should not escape the function and is marginally faster.

Check the ifnull documentation to see how it works.