Criteria API does not seem to have a way to filter by OneToMany relationships

[Todd Flora]

When attempting to filter using the criteria API, filtering by fields on the given Entity, or even on the ManyToOne associations works great, but because the child relationships (OneToMany) are not part of the parent query it seems that there is no way to use the criteria API to filter by fields on child records.

For instance given the relationship of Person to Address where address is a child one to many of Person, there is no way to filter Persons by Address.Postal Code.

Is this something planned for the future or maybe I am missing something on how to do this?

[Christophe Ravaut]

it looks like it works for me. This code works on my learning project.

procedure TForm1.Button3Click(Sender: TObject);
var
  oUsers: IList<TUser>;
  oUser: TUser;
  oSession: TSession;
  oUserCriteria: ICriteria<TUser>;
  oProperty: IProperty;
begin
  FConnection := TConnectionFactory.GetInstanceFromFile(dtADO, 'conn_ADO.json');
  oSession:= TSession.Create(FConnection);
  oUserCriteria:=  oSession.CreateCriteria<TUser>;
  oProperty:= TProperty<TPerson>.Create('FirstName');
  oUsers:= oUserCriteria.Add(oProperty.Eq(edtSearch.Text)).ToList;

  self.Memo1.Clear;
  for oUser in oUsers do
  begin
    self.Memo1.Lines.Add(oUser.Person.FirstName+ ' '+ oUser.Person.LastName);
    self.Memo1.Lines.Add('UserName: '+oUser.UserName);
    self.Memo1.Lines.Add('PassWord: '+oUser.PassWord);
    self.Memo1.Lines.Add('');
  end;
end;

[Todd Flora]

You have not provided the definition of TUser and TPerson here, but your example suggests that Person is a ManyToOne relationship to User, not a OneToMany Relationship. If it were a OneToMany then Person would be a List and would not be accessible with this statement "oUser.Person.FirstName"

It is child records not associate records that are the issue.

Stefan, I will provide a unit test to demonstrate later on today.

[Todd Flora]

Unit test demonstrating this issue.

[Todd Flora]

A simple solution that we are using for now:

We have created our own Criterion to allow a sub select to be created.

Here is the inteface

  TInSubSelectCriterion = class(TAbstractCriterion)
  private
    FPropertyName,
    FInSubSelect : String;
  protected
    function ToSqlString(const params: Spring.Collections.IList<Spring.Persistence.SQL.Params.TDBParam>;
      const command: TWhereCommand; const generator: ISQLGenerator; addToCommand: Boolean): string;
      override;
  public
    constructor Create(const PropertyName, InSubSelect : String);
  end;

And the Implementation

{ TInSubSelectCriterion }

constructor TInSubSelectCriterion.Create(const PropertyName, InSubSelect : String);
begin
  FPropertyName := PropertyName;
  FInSubSelect := InSubSelect;
end;

function TInSubSelectCriterion.ToSqlString(
  const params: Spring.Collections.IList<Spring.Persistence.SQL.Params.TDBParam>;
  const command: TWhereCommand; const generator: ISQLGenerator; addToCommand: Boolean): string;
var
  whereField: TSQLWhereField;
begin

  whereField := TSQLWhereField.Create(FPropertyName, GetCriterionTable(command));
  whereField.RightSQL := '(' + FInSubSelect + ')';
  whereField.WhereOperator := woIn;
  Result := generator.GenerateWhere(whereField);

  if addToCommand then
    command.WhereFields.Add(whereField)
  else
    whereField.Free;
end;

For now the Subselect is created by the developer and passed as the second parameter, Eventually it would be nice if the SubSelect could also be generated based on Criterion.

Just thought we'd share this in case you have others with a need for this right now.

This now allows for a child relationship to be used as a filter.

Example Usage

Assuming TCustomer and TCustAddress entities where TCustAddress is related to TCustomer with a OneToMany relationship.

procedure TestResourceSession.TestCriteria;
var
  Criteria : ICriteria<TCustomer>;
  InCriterion : ICriterion;
  Result : IObjectList;
  PostalCode : String;

begin
  PostalCode = '85214';
  Criteria := OrmSession.CreateCriteria<TCustomer>;

  InCriterion := TInSubSelectCriterion.Create('SID',
    'Select Distinct(s1.CUST_SID) from CUSTOMER_ADDRESS s1 Where s1.POSTAL_CODE = ' + PostalCode.QuotedString);
  Criteria.Add(InCriterion);
  Result := Criteria.ToList;
  Check(Result.Count = 2);
end;

[Stefan Glienke]

I looked a bit into this and I think the best way would be supporting exists/subquery (see http://stackoverflow.com/a/29003791/587106 for a similar example and how this can be solved in Hibernate) - rather than using the in operator.

However currently it is rather complicated to reference fields across different criterias and might require some internal refactorings and probably support for custom aliases.

[Todd Flora]

OK it Is what worked for us in the interim. I leave it to you to decide how best to implement it permanently.

[Stefan Glienke]

Removing milestone: Future version (automated comment)

[Stefan Glienke]

As already announced further development regarding new features and fixing non trivial bugs or design issues of the ORM will be put on hold.