根据Laravel中的几个值对字段进行排序

Question

我有一组城市代码

$cities=[9,12,14,2,3,4,5,6,8,10,11,13]

我的帖子表有一个名为city_id 的外键

我想要根据这个数组的值对帖子进行排序

以这种方式：城市9的第一个帖子，然后是城市12的帖子，然后城市14的帖子等将被加载

我试过用这种方法，但这是错误的

$posts->orderByRaw('city_id in ? desc',$cities);

你能帮我找到最好的解决方案吗？

Answer 1

我唯一能想到做这样的事情的方法(至少现在(是做这样的

$all_posts = [];
$cities=[9,12,14,2,3,4,5,6,8,10,11,13];
foreach ($cities as city) {
$city_posts = Post::whereRaw('city_id = ?', $city)->orderByRaw('created_at DESC');
array_push($all_posts, $city_posts);
}
dd($all_posts);

Answer 2

首先找到与cities相关的所有posts，然后找到给定顺序的sort，如

$cities = [9,12,14,2,3,4,5,6,8,10,11,13]; // order you'd like to see with posts
$posts = Post::whereIn('city_id', $cities)->sort(function($a, $b) uses ($cities) {
$pos_a = array_search($a->getAttributes()['city_id'], $cities);
$pos_b = array_search($b->getAttributes()['city_id'], $cities);
return $pos_a - $pos_b;
})->get();
// $posts contains the required order with city_ids

Answer 3

您可以将原始查询与"CASE something THEN index"一起使用，这样您就可以告诉查询将某物视为索引，这样您可以将0分配给数组中的第一个项。

$sql .= "....";
foreach($cities as $index => $city) {
$sql .= "CASE {$city} THEN {$index}";
}
$sql .= "....";

Answer 4

多亏了friends解决方案，我使用了这种方法，我认为它比friends 建议的方法复杂

$posts=$posts->orderByRaw('FIELD(city_id, '.$cities->implode( ', ').') asc')->orderBy('created_at','desc')->get();