Bug with large IN query

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 

@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.

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

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(); 


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.

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 ?

@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/datab...y-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));