CodeIgniter Forums
Bug with large IN query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: General Help (https://forum.codeigniter.com/forum-24.html)
+--- Thread: Bug with large IN query (/thread-71271.html)



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_ids250); //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));