Mysql Codeigniter Active Record - 如何做一个where_in查询并返回正确的结果顺序？ [英] Mysql Codeigniter Active Record - How do I do a where_in query and return the correct order of results?

问题描述

我有一组以特定顺序传递的ID，需要保留。
我从几个左边连接查询与每个ID相关的数据。
从搜索返回的ID，所以必须保持该顺序，以使结果有效（否则它使搜索而无意义）。

I have a set of IDs passed in a particular order which needs to be retained. I am querying from several left joins the data related to each ID. The IDs are returned from a search so the order must be kept to make the results valid (otherwise it makes searching rather pointless).

我的代码看起来东西

$this->db->where_in('id', $array_of_ordered_ids);

例如 -

$this->db->where_in('id', array(4,5,2,6));

将按2,4,5,6的顺序返回结果。

Will return the results in order 2,4,5,6.

我想保留订单并返回结果4,5,2,6。

I'd like for it to retain the order and return the results 4,5,2,6.

谢谢， p>

Thank you,

推荐答案

要按照数组中的顺序排序结果，您可以执行以下操作：

To order the result by the order in your array, you can do the following:

$array_of_ordered_ids = array(4,5,2,6);

正如你已经知道数字的顺序，你可以使用Mysql FIELD（） 功能：

As you already know the order of the numbers, you can use the Mysql FIELD()^Docs function:

ORDER BY FIELD(id, 4, 5, 2, 6);

要创建此类字符串，可以使用 implode ^文档 ：

To create such a string, you can use implode^Docs:

$order = sprintf('FIELD(id, %s)', implode(', ', $array_of_ordered_ids));

试一试：

$array_of_ordered_ids = array(4,5,2,6);
$this->db->where_in('id', $array_of_ordered_ids);
$order = sprintf('FIELD(id, %s)', implode(', ', $array_of_ordered_ids));
$this->db->order_by($order); 

这篇关于Mysql Codeigniter Active Record - 如何做一个where_in查询并返回正确的结果顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！