What is the equivalent of ORDER BY FIELD in Codeigniter 4

Question

I tried several methods but couldn't get the right result.

ORDER BY FIELD(c.id, '1,0,3,4')

What is the equivalent in query builder?

Do you have any suggestions?

Answer 1

You're free to run raw SQL queries in Codeigniter v4.

Standard Query With Multiple Results (Object Version)

$db = \Config\Database::connect();

$query   = $db->query('SELECT * FROM user WHERE id IN (3,2,1,4) ORDER BY FIELD(id,3,2,1,4)');
$results = $query->getResult();

foreach ($results as $row) {
    echo $row->first_name;
    echo $row->last_name;
    echo $row->email;
}

echo 'Total Results: ' . count($results);

Answer 2

I believe this should work, for the query builder:

$builder->orderBy("FIELD(c.id,1,0,3,4)");