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

#1
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.
Code:
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

Background:
I need to be able to generate this SQL:
Code:
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:
                $this->db->trans_start();

            
//Get all the attribute_ids I want to update and store them in a simple array
                
$this->db->select('attribute_id');
                
$this->db->from('attribute_links');
                
$this->db->where('definition_id',$definition_id);
                                
                foreach(
$this->db->get()->result_array() as $row)
                {
                    
$attribute_ids[] = $row['attribute_id'];
                }

            
//Set the datetime to the value of the value                    
                
$this->db->set('attribute_datetime','attribute_value');
                
$this->db->where_in('attribute_id',$attribute_ids);
                
$success $this->db->update('attribute_values');

                
$this->db->trans_complete(); 

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?
Reply

#2
@objecttothis,

Why is your Where clause so big? Can you make it smaller?
Reply

#3
I would chunk the data into smaller pieces. Should work fine then.
Reply

#4
(02-15-2019, 11:48 AM)php_rocs Wrote: @objecttothis,

Why is your Where clause so big?  Can you make it smaller?

My WHERE clause isn't large. The WHERE IN clause is large however because of the number of attribute_ids that need to have the data 'moved' from one column to the next. It can be chunked into smaller segments, but there is still a significant performance hit. This is why JOIN with UPDATE exists in SQL.
Reply

#5
(02-15-2019, 11:52 AM)kilishan Wrote: I would chunk the data into smaller pieces. Should work fine then.

Yes, it doesn't error if I put the UPDATE into the foreach loop but that means that I am doing 12,000+ hits against the database which takes longer than if the WHERE_IN array could handle a 12k long array.

In the end I abandoned Query Builder for this transaction in favor of db->query() due to the limitations of not being able to do a join with an update or get an efficient solution by breaking the query into two.
Reply

#6
(02-18-2019, 04:01 AM)objecttothis Wrote:
(02-15-2019, 11:52 AM)kilishan Wrote: I would chunk the data into smaller pieces. Should work fine then.

Yes, it doesn't error if I put the UPDATE into the foreach loop but that means that I am doing 12,000+ hits against the database which takes longer than if the WHERE_IN array could handle a 12k long array.  

In the end I abandoned Query Builder for this transaction in favor of db->query() due to the limitations of not being able to do a join with an update or get an efficient solution by breaking the query into two.

Well you wouldn't chunk one row at a time, just someone your server can easier handle - like 1000 rows or 2000? Drastically cuts the number of queries down, and still protects your server's memory usage as the size of the dataset grows.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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