How to add a virtual field on belongsToMany->setForeignKey?

Need Help
1

Hi !
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:

image
image1020×334 20.8 KB

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

image
image1025×340 14.4 KB

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 achive this?

2

As formulated, this is actually a SQL question, not a Cake question. You are looking for something that runs in the SQL query, not PHP code.

However! Do you have any control over the processo_judicial_outro_parametro table? If so, you might be able to create an additional column in there which has the number without the punctuation in it, which your query could look at.

Or, why does the processo_judicial table not have the punctuation in it? Any chance that it could be added back in there?

3

I cannot change any table