Syntax Error using expr in a dependent entity column

[juliocastroacid]

Is your feature request related to a problem? Please describe.

Using entity repositories, I'm trying to filter by a numeric column with a smart query condition ($ilike specifically).

Since the column is numeric I need to cast it. I've read here that one approach could be to use expr. But the behaviour is not as expected.

The column I'm trying to filter comes from a dependent entity, so the expr result doesn't fit in the final join query. Here is the where clause:

{
      y: { z: { target: { $ilike: '%550%' } } }
}

which results in the following query and exception:

DriverException: 

select ...
from "x" as "e0" 
left join "y" as "e1" on ...
left join "z" as "e2" on ...
left join "_" as "e3" on ...
where "e2"."target" ilike '%550%'

 - operator does not exist: integer ~~* unknow

which is correct since the column is numeric. But if I try something like this:

{
      y: { z: { [expr('cast(target as text)')]: { $ilike: '%550%' } } }
}

Then I get a syntax error:

SyntaxErrorException: 

select ...
from "x" as "e0" 
left join "y" as "e1" on ...
left join "z" as "e2" on ...
left join "_" as "e3" on ...
where e2.cast(target as text) ilike '%550%' 

- syntax error at or near "as

I've read here that we can even remove the expr call but it doesn't work neither.

I also tried combinations with and without quotes in the expression and the final result is always a syntax error.

Describe the solution you'd like

I'll be happy to have a functionality to cast the results of a certain column to take advantage of the smart query conditions.

I've tried to use query builder but I'm forced to do the joins and the queries manually to get it done. Maybe there is an alternative way to do it but I didn't find it in the docs or other issues.

Describe alternatives you've considered

As I mentioned, query builder but I think is not a satisfactory solution.

Additional context
I don't know why the resulting query makes another join (e3). Probably this doesn't affect the issue but I comment it just in case.

[B4nan]

You can't use custom fragments in nested conditions. The fact they are nested implies they need to be aliased.

Will allow the :: casting so we can do this:

{
  y: { z: { [expr('target::text')]: { $ilike: '%550%' } } }
}

But in general the same restriction still apply, we can't use any function as it will be prefixed with the alias. expr is just an identity function, it has no information about the context, therefore can't handle aliasing on its own. Kinda related to #2217, I don't have any solution for that.

[juliocastroacid]

@B4nan. I see, using target::text doesn't work directly because it tries to match it with a column. But if we add spaces to it: target :: varchar then everything seems to work.

I understand that for complex situations it may be better to follow another approach. But in my case this workaround works perfectly.

Thanks a lot!