• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Query Builder: regex error when passing large array to where_in

CI 3.1.9
PHP 5.6.38
FreeBSD 11.2
Apache 2.4
MySQL 5.7

Problem: passing a large array to where_in generates the php error and the update fails.
preg_match(): Compilation failed: regular expression is too large at offset 106661 /usr/local/www/public_html/pos_alpha/vendor/codeigniter/framework/system/database/DB_query_builder.php 2418

I need to be able to generate this SQL:
UPDATE ospos_attribute_values
INNER JOIN ospos_attribute_links
ON ospos_attribute_values.attribute_id = ospos_attribute_links.attribute_id
SET ospos_attribute_values.attribute_datetime = ospos_attribute_values.attribute_value
WHERE ospos_attribute_links.definition_id = [definition_id from PHP]

Query builder cannot use db->join() with db->update(), so my solution is to use query builder and break the query into two. One to pull the attribute_id's that share the definition_id that I want and put them in an array. and the second is to update the attribute_values table by setting the attribute_value to the attribute_datetime.

PHP Code:

//Get all the attribute_ids I want to update and store them in a simple array
$this->db->get()->result_array() as $row)
$attribute_ids[] = $row['attribute_id'];

//Set the datetime to the value of the value                    
$success $this->db->update('attribute_values');


The problem comes in that this array in particular contains large amounts of data (12,000+ items). I'm guessing that since MySQL and QueryBuilder were never designed to have WHERE IN clauses contain more than a small array (that's what joins in updates are for) that my problem is being caused by this. Should I just abandon Query Builder and use query() instead to make my join or is there a good solution that includes query builder that I'm just missing?

Messages In This Thread
Query Builder: regex error when passing large array to where_in - by objecttothis - 02-15-2019, 01:31 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.