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

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');


$pjs = $pjTable->find()->contain('Originario')
->where([' IN'=>[134997,134998,135002,135041,135052]])

The problem is that this table has values like:

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

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?

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?

I cannot change any table