+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Bug with large IN query (/showthread.php?tid=71271)
Bug with large IN query - michaelv - 07-25-2018
hello
i found a bug when dealing with large In query (approx 1000 ids)
preg_match(): Compilation failed: regular expression is too large at offset 43029 database/DB_query_builder.php 2415
my query is:
Code:
SELECT blabla ...
FROM `table xxx` `x`
WHERE `x`.`afficher` = 1
AND `id_prod` IN (12666,29593,20398,17415,..... over 1000 ids)
GROUP BY `x`.`id_prod`
ORDER BY RAND()
LIMIT 24 RE: Bug with large IN query - php_rocs - 07-25-2018
@michaelv,
Suggestion 1: Is your not in list shorter then you in list?
Suggestion 2: Create a subquery of the table with the wanted values and then join it to itself. I'm assuming that there is an easier way to identify the wanted id_prods.
RE: Bug with large IN query - michaelv - 07-25-2018
hello
i managed to limit ids to 500 not more
but there is a problem somewhere in Core, so i give info de DEVS
regards
RE: Bug with large IN query - dave friend - 07-25-2018
It's actually a problem of regex. If the string gets too big it runs run into a recursion limit.
Instead of using Query Builder you might be better off using a hand-crafted query string as an argument to $this->db->query('your hand-crafted query');
Depending on how you are building the "in" list you might be able to break it down into manageable bites. Here's the basic idea.
PHP Code:
$this->db->group_start();
$ids_chunk = array_chunk($all_ids, 250); //or whatever size can be handled
foreach($ids_chunk as $chunk)
{
$this->db->or_where_in('id_prod', $chunk);
}
$this->db->group_end();
RE: Bug with large IN query - zurtri - 07-25-2018
I must admit having a list of 500 or 1000 IDs to check for in a IN() list, seems to be a lot.
I recognise that the world is a dirty place, and this might be the only solution - but it might be worth investigating if there is a better way.
I know this doesn't answer the exact question - but it is worth considering WHY you need such a list.
Having said all that - respect for what you're doing - sometimes we have to code nasty things for reasons not of our choosing.
RE: Bug with large IN query - michaelv - 07-26-2018
dear all thanks for your reply
i just follow the "bug" i found
this code was for previously viewd items on an ecommerce website
we never excpected a user to see more than 1000 produtcs in a session
but botnet do
i'll manage to recode this stuff or simply limit the previously view items to 100 or so
question, in term on performence, what would be better ?
100 lines like this:
$this->db->or_where_in('id_prod', $chunk);
or 100 Ids in a IN clause ?
RE: Bug with large IN query - php_rocs - 07-26-2018
@michaelv,
You could easily test that yourself. All you have to do is test both queries in MySQL Workbench.
Also, to give your query more flexibility try the query binding approach ( https://codeigniter.com/user_guide/database/queries.html?highlight=query%20builder#query-bindings ).
PHP Code:
$prod_list = array(12666,29593,20398,17415,..... over 1000 ids);
$qry = "SELECT blabla ... FROM xxx x WHERE x.afficher = 1 AND id_prod IN ? GROUP BY x.id_prod ORDER BY RAND() LIMIT ? ";
$this->db->query($qry,array($product_list,24));