expr function enhancement

[jeroni]

I was developing a filter to use a "starts with" on an integer (using postgresql). The solution was to cast that integer via "expr" function and use a simple regular expression to filter those numbers.
My client side grid had the capability to sort by another entity which had the same name column as the main entity using the filter so I had a collision and had to handle alias by hand ("e0" in that case).

I think a good solution would be to enhance the expr function with $1, $2, $3.... params like this:

{ [expr('to_char($1)', ['client_code'])]: {$re: {...}} }

By this way it would be possible to check entity properties and assign alias references by Mikro itself.

[B4nan]

expr is identity function, it literally does nothing (just passing the input as its output), it is there just to escape strictly typed FilterQuery. Your proposal also don't count with nested entities, what if there were more properties called client_code on multiple tables?

Maybe try to describe more the use case for this, with concrete examples. But having wildcards like $1/$2/$3... seems like it won't help much.

[jeroni]

I undestand expr just passes the input as output. Here goes an example:

  /*

   @Entity()
   class Client {

      //....

      @Property()
      code!: number;

      @ManyToOne(entity: () => ClientType)
      type_id: ClientType;

      //....
   }

   @Entity()
   class ClientType {

      //...

      @Property()
      code!: number;

      @Property()
      name!: string;

      //...
   }

   */

  const payload = '103';

  const result1 = orm.em.find(
    Client,
    {
      $or: {
        [expr('cast(code as text)')]: {$re: `^${payload}`}, // A number starts with, bypass column check
        type_id: {
          [expr('cast(code as text)')]: {$re: `^${payload}`}
        }
      }
    },
    {
      orderBy: {
        type_id: {
          name: 'asc'
        }
      }
    }
  );

  /* Ambiguity problem
  select
    "e0".* from "Client" as "e0"
    left join "ClientType" as "e1" on "e0"."type_id" = "e1"."id"
  where
    cast(code as text) ~ '^100' or (cast(code as text) ~ '^100'
  order by "e1"."name" asc
   */
  
  // Proposal:
  const result2 = orm.em.find(
    Client,
    {
      $or: {
        [expr('cast($1 as text)', ['code'])]: {$re: `^${payload}`}, // Client.code < check on this property
        type_id: {
          [expr('cast($1 as text)'), ['code']]: {$re: `^${payload}`} // ClientType.code < check on this property
        }
      }
    },
    {
      orderBy: {
        type_id: {
          name: 'asc'
        }
      }
    }
  );
  /* Query:
    select
      "e0".* from "Client" as "e0"
      left join "ClientType" as "e1" on "e0"."type_id" = "e1"."id"
    where
      cast(e0.code as text) ~ '^100' or cast(e1.code as text) ~ '^100'
    order by "e1"."name" asc
  */

[B4nan]

Well but there is no context, you just call the expr twice, with the very same parameters. I don't see a way how it could work, the resolution would have to be done at the CriteriaNode, so when processing the query - but with current state of things, it would be considered as custom expression, hence no postprocessing would be done anyway...

[jeroni]

We have a generic api rest and we use filters to handle the result, so, as I see, the only solid solution would be assign an alias to the entitiy decorator and use it in expr "by hand". I think there is no way to get the assigned alias to each entity/table in a filter environment, or is it?.

[B4nan]

There is a method for getting alias for a join path on QB, but that all seems very verbose, as opposed to just passing the aliases here and there (which obviously won't work if you have too many moving parts, e.g. when things are too dynamic). Join path is a string like Book.author.favouriteBook (with some variations for pivot joins). And you'd have to use QB, as it works based on the QB instance state. On the other hand, if you want to have explicit aliases, QB is the only way. Adding an alias to entity metadata is wrong, that can never work with circular relations (and I am sure there would be many other issues with such approach, e.g. an entity has multiple properties of the same type).

[jeroni]

I understand... and precisely the moving parts is my concern. I will use an optional "query builder" strategy and pass that query builder through all custom hooks to fulfil it and be executed at the end of the generic process.

Thanks for your help and attention