嵌套的“如果"在选择位置进行条件动态过滤 [英] Nested "ifs" in a select-where for a conditional dynamic filtering
问题描述
最近，我创建了一个基于codeigniter 3-php和PHPMyADMIN-mysql的新网站，比使用ajax进行动态搜索要多得多. 我的问题是，当我有超过100万个500,000个结果时，查询开始花费很多时间.
Recently I created a new website based on codeigniter 3-php and PHPMyADMIN-mysql and I integate one pagination more than that i did a dynamic search with ajax. My problem is when I have more than 1 million 500 thousand results the query starts to put a lot of time.
对于这个解决方案，我考虑使用触发器. 因此，在使用触发器时，我会遇到一个麻烦，就是尝试创建一个动态过滤器时，我举一个例子来更好地理解这一点，我想知道与存储在另一个动态表中的过滤器相对应的行数.
And for this solution I considered using triggers. So I have a little trouble when I use triggers is when I try to make a dynamic filter, I make an example to better understand,I want to know the number of rows corresponding to a filter stored in another dynamic table.
BEGIN
UPDATE max_tare
SET max_row = (SELECT COUNT(*)FROM history
IF(new.client != '') THEN
WHERE nom LIKE CONCAT('%', NEW.client, '%') END IF;
IF(new.commune != '') THEN
WHERE commune LIKE CONCAT('%', NEW.commune, '%') END IF;
IF(new.type != '') THEN
WHERE type LIKE CONCAT('%', NEW.type, '%') END IF;
IF(new.matricule != '') THEN
WHERE mat LIKE CONCAT('%', NEW.matricule, '%') END IF;
IF(new.tare != '') THEN
WHERE tare LIKE CONCAT('%', NEW.tare, '%') END IF;
WHERE cancled = 0),
max_tare =(SELECT SUM(tare) FROM history
WHERE cancled = 0) WHERE id = 1;
END
问题是，如果我删除所有ifs statement条件，则我的过滤器将不起作用，如果我的过滤器包含一个空字段，它将结果破坏为0，我希望如果它的空字段不计为过滤参数.
我也尝试过案例陈述，这也无济于事.
The problem is if I delete all the ifs statement conditions, my filter will not work, if my filter contain an empty field, it broke the result to 0, I want if its an empty field its doesn't count as a parameter of the filtering.
I have tried also case statements this also doesn't help.
当我运行代码时，这就是我得到的错误:
When I run the code this is what I get like error:
MySQL回答:#1064-'
IF'(new.client！='')附近的语法错误 其中的名称类似于CONCAT('％'，NEW.client，'％')END IF;
MySQL replied: # 1064 - Syntax error near '
IF' (new.client! = '') THEN WHERE name LIKE CONCAT ('%', NEW.client, '%')END IF;
总有一种可行的长效解决方案，但我不需要，因为我不能称其为解决方案:
是要取出外面的东西，并针对每种情况从中进行选择，换句话说:
There is somehow a long way solution that work but I don't need because I can't call it as a solution:
It is to take out the outside, and make a select from for each case obtained, in other words:
if (client! = 0 and common == 0 and type == 0 and number == 0 and tare == 0) then
{filter by client only}
otherwise-if (client! = 0 and common! = 0 and type == 0 and number == 0 and tare == 0) then
{filter by client and common only} .... ect
我将尝试举例说明一下: 这是我的历史记录表:
I will try to make an example to make it more clear: This is my history table:
| history |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| id | nom | commune | type | mat | tare | rfid | cancled |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 1 | EPIC paris | france | white | 01248-816-16 | 7600 | ABCF44C | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 2 | EPIC london | UK | white | 06854-315-16 | 5233 | A8CG27C | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 3 | NET barça | ESP | red | 03254-615-16 | 8900 | HBC54AC | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 4 | NET Dubai | arab | blue | 35251-117-16 | 11200 | HDK7BV5 | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 5 | EPIC roma | ita | red | 36524-618-16 | 7300 | NBL53DC | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 6 | SNC beta | alpha | green | 69358-117-16 | 5400 | JDLF8ND | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 7 | EPIC tokyo | japan | yellow | 46258-712-16 | 8700 | K5ND55D | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 8 | SARL Fit | body | black | 69531-614-16 | 9600 | AIES5HJ | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
我想检索包含cancled = 0的行数，并以它们的名称显示EPIC
I want to retrieve the number of rows that contain cancled = 0 and in their name they have EPIC
所以我的文件管理器数组将如下所示:
So my filer array will be like this :
| temp_fetch |
+----+--------+---------+------+--------------+------+
| id | client | commune | type | matricule | tare |
+----+--------+---------+------+--------------+------+
| 1 | EPIC | | | | |
+----+--------+---------+------+--------------+------+
因此，我应该有这样的东西
As a result I should have something like this
| max_tare |
+----------------------------+
| id | max_row | max_tare |
+----+------------+----------+
| 1 | 2 | 14900 |
+----+------------+----------+
如果有帮助，我会在这里放上我的PHP代码，但是要加载太多结果
If its help i will put there my php code that work but take too much to load the results
$this->db->from('history');
if ($query[1] != '') {
$this->db->like('nom', $query[1]);
}
if ($query[2] != '') {
$this->db->like('commune', $query[2]);
}
if ($query[3] != '') {
$this->db->like('type', $query[3]);
}
if ($query[4] != '') {
$this->db->like('mat', $query[4]);
}
if ($query[10] != '') {
$this->db->like('rfid', $query[10]);
}
if ($query[5] != '') {
$this->db->like('tare', $query[5]);
}
if ($query[6] != '') {
$this->db->where('date >', $query[6]);
}
if ($query[7] != '') {
$this->db->where('date <', $query[7]);
}
if ($query[11] != '') {
$this->db->where('time_plode >', $query[11]);
}
if ($query[12] != '') {
$this->db->where('time_plode <', $query[12]);
}
$this->db->where('cancled', 0);
return $this->db->count_all_results();
推荐答案
您要查找join和group by吗?
select f.id,
count(*) as num_rows,
sum(tare) as sum_tare
from history h join
temp_fetch f
on h.nom like concat('%', f.client, '%') or
h.commune like concat('%', f.commune, '%') or
h.type like concat('%', f.type, '%') or
h.mat like concat('%', f.matricule, '%') or
h.tare like concat('%', f.tare, '%')
where f.cancled = 0
group by f.id;
这篇关于嵌套的“如果"在选择位置进行条件动态过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
