CODEWITHSUNDEEP
mysqlregexcakephp
Yssybyl
Yssybyl

Reputation: 75

CakePHP MySQL repetition-operator operand invalid - generated SQL works correctly, but cake reports error

Lots of questions relating to 1139: repetition-operator operand invalid but I've not yet found one where the query is fine within mySQL but fails when called from within cake.

I am doing a find all from cake and want to limit the results to ids where there is a valid UK mobile number (the field could contain both non-mobiles and non-UK numbers). I worked out the SQL I needed via MySQL:

select 
    distinct(client_id) 
from 
    phone_details 
where 
    number rlike '^((00|\\+)44|0)7[[:digit:]]{9}$'

This gives me the results I expect. So, I code up the same in Cake:

$res = $this->PhoneDetail->find('all', array(
  'recursive' => -1,
  'conditions' => array(
      'PhoneDetail.number rlike \'^((00|\\+)44|0)7[[:digit:]]{9}$\''
  ),
  'fields' => array('DISTINCT(PhoneDetail.client_id)')
));

This results in an error:

SQLSTATE[42000]: Syntax error or access violation: 1139 Got error 'repetition-operator operand invalid' from regexp

The error goes on to show the attempted query:

SELECT 
    DISTINCT(`PhoneDetail`.`id`) 
FROM 
    `db`.`phone_details` AS `PhoneDetail` 
WHERE 
    `PhoneDetail`.`number` rlike '^((00|\\+)44|0)7[[:digit:]]{9}$'

Now, I can't see the difference between what I put initially in MySQL and what cake is generating, and pasting the erroring query into MySQL also works correctly! So I wonder if Cake is doing some escapes to the regex causing a problem that are being stripped out again before printing the error message. If so, how do I get around this?

Note: I've heavily simplified the queries to make this easier to follow but removing the regex makes this all work perfectly so am happy that this is where the error lies.

I have also tried using REGEXP with the same results.

Any suggestions would be fantastic!

Upvotes: 2

Views: 588

Answers (1)

Yssybyl
Yssybyl

Reputation: 75

It was exactly as AD7six suggested - changing the version of the regex in CakePHP to:

$res = $this->PhoneDetail->find('all', array(
  'recursive' => -1,
  'conditions' => array(
      'PhoneDetail.number rlike \'^((00|\\\\+)44|0)7[[:digit:]]{9}$\''
  ),
  'fields' => array('DISTINCT(PhoneDetail.client_id)')
));

worked as expected.

Upvotes: 1

Related Questions