Filtering parent based on relative children collection information

[svantreeck]

Is your feature request related to a problem? Please describe.
I'd like to be able to filter a set of parent items based on some relative (based on sequences or date) data inside the child collection.

Describe the solution you'd like
With the data structure below, I'd like to make a query that would give me the Parent objects where the Child record with the earliest createdDate had a specific somethingIWantToFilterOn value.

const parentRepository: EntityRepository<Parent>;
const where = {
  children: { somethingIWantToFilterOn: 'value', createdDate: $min } // $min is my idea for the lowest createdDate for that child
}
const results = await parentRepository.findAndCount(where)

class Parent {
  @PrimaryKey()
  id: uuid;

  @OneToMany(() => Child, (child) => child.parent)
  children: Collection<Child>
}

class Child {
  @PrimaryKey()
  id: uuid;

  @ManyToOne(() => Parent)
  parent: Parent;

  @Property()
  somethingIWantToFilterOn: string;
  
  @Property()
  createdDate: Date;
}

Describe alternatives you've considered
I think I could use knex's query builder to basically do an inner join with a subselect to calculate the value, but it would be nice to be able to use some of the smart query conditions to build the query instead of jumping to knex.

I was also thinking if I could use a filter somehow that way I could write this as shorthand in a couple locations if it needed reuse.

Additional context
Add any other context about the feature request here.

Another option I was thinking of was creating something like firstChild|lastChild objects on the Parent...that might be more like the filter idea though.

[B4nan]

This is exactly how you can do it, it does not work for you? What query does it produce? What query do you expect? Let's not talk in pseudocode, as it just adds confusion, so try to create actual code snippet of what you have and provide the query you are trying to produce.

[svantreeck]

let me see if I can put together a small repro on this. When I was using the filter on a collection prop, I wasn't able to get typescript to let me use $max/$min etc for filtering, so never got to the part where it generated sql. Also, I'm not sure if the "exactly how you can do it" was referring to the smart query conditions or using query builder.

[B4nan]

there is no such operator as $min/$max

https://mikro-orm.io/docs/query-conditions/#list-of-supported-operators

[lightning003]

This is exactly how you can do it, it does not work for you? What query does it produce? What query do you expect? Let's not talk in pseudocode, as it just adds confusion, so try to create actual code snippet of what you have and provide the query you are trying to produce.

@B4nan ... Hello, I was trying similar stuff. I do not have repo ready but I am getting this when trying this type of filtering.

"exception": {
"stacktrace": [
    "DriverException: Maximum call stack size exceeded",
    "    at PostgreSqlExceptionConverter.convertException (proj/node_modules/@mikro-orm/core/platforms/ExceptionConverter.js:8:16)",
    "    at PostgreSqlExceptionConverter.convertException (proj/node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:40:22)",
    "    at PostgreSqlDriver.convertException (proj/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:202:54)",
    "    at proj/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:206:24",
    "    at processTicksAndRejections (internal/process/task_queues.js:93:5)",
    "    at PostgreSqlDriver.find (proj/node_modules/@mikro-orm/knex/AbstractSqlDriver.js:46:24)",
    "    at SqlEntityManager.find (proj/node_modules/@mikro-orm/core/EntityManager.js:93:25)",
    "    at async Promise.all (index 0)",
    "    at SqlEntityManager.findAndCount (proj/node_modules/@mikro-orm/core/EntityManager.js:203:35)",
    "",
  ]
}

Entity Structure is -

class Parent {
  @PrimaryKey()
  id: uuid;

  @OneToMany(() => Child, (child) => child.parent)
  children: Collection<Child>

  @OneToOne(() => Child)
  favChild: Child

  @OneToOne(() => Child)
  leastFavChild: Child
}

class Child {
  @PrimaryKey()
  id: uuid;

  @ManyToOne(() => Parent)
  parent: Parent;

  @Property()
  somethingIWantToFilterOn: string;
  
  @Property()
  createdDate: Date;
}

Basically I am trying to filter from favChild and leastfavChild both at same time, giving me above error. I also checked what type of params it created and I see circular in QueryHelper -> processObjectParams.

<ref *1> {
  '$and': [
    { 'e1.somethingIWantToFilterOn': [Object] },
    [Circular *1],
    { '$and': [Array] }
  ]
}

Also one thing is that if not using array with $and, if using key value conditions like mentioned in smart query then it works.

Works - dbFilter.favChild = {somethingIWantToFilterOn: value}; dbFilter.leastFavChild = {somethingIWantToFilterOn: value}
Works - dbFilter.favChild = {somethingIWantToFilterOn: value}
Works - dbFilter.favChild = {$and: [{somethingIWantToFilterOn: value}]}
Does not work - dbFilter.favChild = {$and: [{somethingIWantToFilterOn: value}]}; dbFilter.leastFavChild = {$and: [{somethingIWantToFilterOn: value}]};