0

I’ve need to create on-the-fly a association between the same table, processo_judicial, where another table contains reference to “both”.

My current code is:

$pjTable = TableRegistry::get('ProcessoJudicial');

$pjTable->belongsToMany('Originario',
[
'className'=>'ProcessoJudicial',
])
->setForeignKey('valor')
->setBindingKey('numero')
->setThrough('ProcessoJudicialOutroParametro')
->setConditions(['ProcessoJudicialOutroParametro.nome'=>'PROCESSO_ORIGINARIO']);


$pjs = $pjTable->find()->contain('Originario')
->where(['ProcessoJudicial.id IN'=>[134997,134998,135002,135041,135052]])
->limit(10);
debug($pjs->toArray());

The problem is that this table has values like:

enter image description here

In processo_judicial I have the field “numero” without “non-digits chars”:

enter image description here

So I need someway to CakeORM change this SQL comparison using this:

WHERE ProcessoJudicialOutroParametro.valor 
in (c0, c1, c2)

to this:

WHERE (replace(replace(ProcessoJudicialOutroParametro.valor,'-',''),'.','')
in (c0, c1, c2)

How can I achieve this?

celsowm
  • 846
  • 9
  • 34
  • 59
  • Why are the values formatted differently in the first place, is that a legacy database that cannot be changed? – ndm May 21 '20 at 14:02
  • unfortunately it cannot be changed :( – celsowm May 21 '20 at 16:39
  • Well that's too bad, a full table scan over millions of rows doesn't sound nice. You'd need custom association and loader classes to change the key in the comparison, it's not overly complicated, and I can post an example of that, but I'm wondering whether `valor` uses a format that could be applied to `numero` on the fly, that way the query could still utilize possible indices? `2010.8.19` looks like a date? – ndm May 22 '20 at 08:55

0 Answers0