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?

Documentation says to do orderBy like this. ``$this->db->order_by('title', 'DESC');`` — Dula, Nov 21 '21 at 22:48
@Dula The question is really about how to use the `FIELD` function in codeigniter, not how to do basic ordering. — Andrew, Nov 22 '21 at 15:01

score 2 · Answer 1 · answered Nov 22 '21 at 14:58

2

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);

answered Nov 22 '21 at 14:58

steven7mwesigwa

5,701
3
20
34

Yes I know but I want to do this with QueryBuilder – starlings Nov 22 '21 at 16:55

score 1 · Answer 2 · answered Oct 12 '22 at 09:01

1

I believe this should work, for the query builder: $builder->orderBy("FIELD(c.id,1,0,3,4)");

answered Oct 12 '22 at 09:01

Baz

11
1

What is the equivalent of ORDER BY FIELD in Codeigniter 4

2 Answers2