Welcome Guest, Not a member yet? Register   Sign In
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

(This post was last modified: 02-18-2019, 04:02 AM by objecttothis.)

(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




Theme © iAndrew 2016 - Forum software by © MyBB