How can I use WHERE on a ManagedSet?

Question

I have a many-to-many relation, having P, PF and F I want to filter P using F through PF. Like:

final query = Query<P>(context)
      ..where( (p)=>p.pfSet.firstWhere( (pf)=>pf.f.cod == 1 ).f ).isNotNull();

and the classes:

class P extends ManagedObject<_P> implements _P {}
class _P{
  @primaryKey
  int cod;
  ...

  ManagedSet<ProdutoFilial> pfSet;
}

class PF extends ManagedObject<_PF> implements _PF {}
class _PF{

  @primaryKey
  int cod;
  @(Relate #pfSet)
  P p;
  @(Relate #pfSet)
  F f;
  bool active;    
}

class F extends ManagedObject<_F> implements _F {}
class _F{

  @primaryKey
  int cod;
  ...
  ManagedSet<ProdutoFilial> pfSet;

}

How can I filter this?

Answer 1

Can use a own query that will stay on where:

..predicate = QueryPredicate(
          " exists (select f.cod from pf where pf.fcod = @fcod and pf.pcod = p.cod) ",
          { "fcod": 1 });

if you use join in your code query the table name will change.

Answer 2

QueryPredicate is not a very good solution because you cannot set a condition in a joined table (we don't know the aliases of joined tables). I created my own procedure - try it:

    QueryExpression<dynamic, dynamic> RelationWhereExt(Query query, List<String> links) {
    ManagedEntity e = query.entity;
    KeyPath path;

    ManagedPropertyDescription property;
    links.forEach((link) {
      property = e.properties[link];
      if (path==null){
        // рутовое условие
        path = KeyPath(property);
      } else {
        // следующие условия
        path.add(property);
      }

      if (property is ManagedRelationshipDescription) {
        e = (property as ManagedRelationshipDescription).destinationEntity; // меняем entity
      }
    });

    QueryExpression<dynamic, dynamic> queryExpression = QueryExpression<dynamic, dynamic>(path);
    (query as MySqlQuery).expressions.add(queryExpression);

    return queryExpression;
  }

In you case: RelationWhereExt(query, ['pfSet', 'f']).isNotNull();