以下功能以二维数组格式返回所有播放器。以下是功能: -
public function get_football_players($type = FOOTBALL_TEXT, $filter = '', $start = '', $limit = '', $player = '') {
$this->db->select('tblmarkets_football.*, tblmarkets.symbol, tblmarkets.type');
$this->db->from('tblmarkets_football');
$this->db->join('tblmarkets','tblmarkets.id = tblmarkets_football.market_id','left');
if($type != ''){
$this->db->where('type', $type);
}
$this->db->where('active', 1);
switch ($filter) {
case 'forward':
$this->db->where('position', 'Forward');
break;
case 'midfielders':
$this->db->where('position', 'Midfielder');
break;
case 'defenders':
$this->db->where('position', 'Defender');
break;
case 'goalkeepers':
$this->db->where('position', 'Goalkeeper');
break;
case 'highest_price':
case 'lowest_price':
$tmp = $this->external_api_model->get_filtered_football_pairs($limit, $start, $filter);
$players = json_decode($tmp, true);
$this->db->where_in('symbol', $players);
break;
}
if($player != ''){
$this->db->like('tblmarkets_football.name', $player);
}
if($filter != 'highest_price' && $filter != 'lowest_price'){
$this->db->limit($limit, $start);
}
$result = $this->db->get()->result();
return $result;
}
当某人应用最高价格过滤器时, get_filtered_football_pairs API以阵列格式返回所有玩家。我希望所有这些球员都以我收到的顺序显示在顶部。$players
具有所有播放器名称。目前它们被随机显示。
任何帮助将不胜感激。预先感谢。
编辑
$ players看起来如下: -
Array
(
[0] => paul-pogba
[1] => neymar
[2] => kylian-mbappé
[3] => lionel-messi
[4] => jadon-sancho
[5] => eden-hazard
[6] => marcus-rashford
[7] => mohamed-salah
[8] => harry-kane
[9] => vinicius-junior
[10] => callum-hudson-odoi
[11] => raheem-sterling
[12] => ousmane-dembele
[13] => anthony-martial
[14] => paulo-dybala
[15] => gareth-bale
[16] => joshua-kimmich
[17] => cristiano-ronaldo
[18] => kai-havertz
[19] => leroy-sané
)
我想要以这样的顺序意味着首先显示Paul-Pogba,然后是Neymar等。
这是未经测试的,但是逻辑是逃脱了$player
阵列中的元素,然后将优先处理应用于合格的symbol
值,然后将次级排序订单作为symbol
上升(否则,只需在将球员与非玩家分开后随机留下。
case 'highest_price':
case 'lowest_price':
$tmp = $this->external_api_model->get_filtered_football_pairs($limit, $start, $filter);
$players = json_decode($tmp, true);
$this->db->where_in('symbol', $players);
foreach ($players as &$p) {
$p = "'" . $this->db->escape_str($p) . "'";
}
$this->db->order_by("IF(FIELD(symbol," . implode(",", $players) . ")=0,1,0),
FIELD(symbol," . implode(",", $players) . ")");
break;
这是一个db-fiddle演示,显示如何执行订购:https://www.db-fiddle.com/f/9yxto3nehcu9vlpw6mely3/1
帮助理解我的技术:https://dba.stackexchange.com/a/109126/157408
解释该子句的0,1
部分...
它在非$players
之前将$players
分开。所有$players
将位于0
组中,非$players
将在1
组中。然后辅助排序将使$players
以您的首选顺序(打破0
领带)。
P.S。我认为使$start
和$limit
空字符串的默认值不明智 - 数字值似乎更适合。
我通常会尝试远离开关块,因为我发现这太冗长了。另一种样式可能是这样的(取决于您的预期输入和逻辑要求,您可以进一步煮沸):
if (in_array($filter, ['forward', 'midfielders', 'defenders', 'goalkeepers'])) {
$this->db->where('position', ucfirst(rtrim($filter, 's')));
}
if (in_array($filter, ['highest_price', 'lowest_price'])) {
$tmp = $this->external_api_model->get_filtered_football_pairs($limit, $start, $filter);
$players = json_decode($tmp, true);
$this->db->where_in('symbol', $players);
foreach ($players as &$p) {
$p = "'" . $this->db->escape_str($p) . "'";
}
$this->db->order_by("IF(FIELD(symbol," . implode(",", $players) . ")=0,1,0),
FIELD(symbol," . implode(",", $players) . ")");
} else {
$this->db->limit($limit, $start);
}
if ($player != '') {
$this->db->like('tblmarkets_football.name', $player);
}
假设桌子中的某个地方有一个price
字段,您需要做的就是:
case 'highest_price':
$tmp = $this->external_api_model->get_filtered_football_pairs($limit, $start, $filter);
$players = json_decode($tmp, true);
$this->db->where_in('symbol', $players);
$this->db->order_by('your_price_field', 'DESC');
break;
case 'lowest_price':
$tmp = $this->external_api_model->get_filtered_football_pairs($limit, $start, $filter);
$players = json_decode($tmp, true);
$this->db->where_in('symbol', $players);
$this->db->order_by('your_price_field', 'ASC');
break;
做到这一点的另一种方法是在模型中使用不同的方法,这些方法取决于switch
的情况,但价格"过滤"仅需要order_by
查询构建器语句