I am stumped with this one: I have three tables Person -> Job -> Billing where a person owns many jobs and jobs own many billings. I want a PersonCollection where the sum of the Billings.Amount <= 0. At the same time, I want a prefetch path that pulls all of the jobs for each person where the sum of the billings.Amount <= 0. What is the best way to do this?

I use LLBLGen 2.0.0.0 Final March 21, 2007.

As a corollary, consider the following additional table Person -> Job -> Payments, where a Job has many payments. How can I query a PersonCollection where the Sum(Billings) - Sum(Payments) != 0 and then have a prefetch path on the PersonCollection for each job that then pulls each job that has Sum(Billings) - Sum(Payments) != 0. Thanks for the help!

-Seth

I'm I correctly assuming the SQL query you need to execute looks like the following?

SELECT * from Person
WHERE PersonID IN 
(
Select PersonID From Job
INNER JOIN Billing ON ...
GROUP BY PersonID
HAVING SUM(Billing.Amount) <=0
)

If true, then you need to use a FieldCompareSetPredicate to implement the IN Clause. Using the following overload:

public FieldCompareSetPredicate( 
   IEntityField field,
   IEntityField setField,
   SetOperator operatorToUse,
   IPredicate filter,
   IRelationCollection relations,
   string objectAlias,
   long maxNumberOfItemsToReturn,
   ISortExpression sorter,
   bool negate,
   IGroupByCollection groupByClause
)

You will have to set the relations paramtere (from Job to billing) And you will have to set the appropriate GroupByClause and its HavingClause.

Thanks for the reply. I guess I am not doing it right. The exact query I would like to get is

SELECT * from Person
WHERE PersonId IN
(
Select EstimatorId From Job
INNER JOIN Billing ON Job.JobId = Billing.JobId
GROUP BY EstimatorId 
HAVING SUM(Billing.Amount) <=0
)

Here EstimatorId and JobId are the PK-FK. I don't think I did that part right. The second thing is I want the prefetch path on the PersonCollection that fills in each job only if the SUM(Billing.Amount) <=0. This has really stumped me!

IPrefetchPath2 prefetchJobs = new PrefetchPath2((int)EntityType.PersonEntity);
            //add sorting for additional items here
            IPredicateExpression jobSelect = getFilter();
            prefetchJobs.Add(PersonEntity.PrefetchPathJob, -1, jobSelect);

            IRelationPredicateBucket selectFilter = new RelationPredicateBucket();
            IRelationCollection queryRelations = new RelationCollection();
            queryRelations.Add(PersonEntity.Relations.JobEntityUsingEstimatorId);
            queryRelations.Add(JobEntity.Relations.BillingEntityUsingJobId);

            IGroupByCollection groupBy = new GroupByCollection();
            groupBy.Add(JobFields.EstimatorId);
            groupBy.HavingClause = new PredicateExpression(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum) <= 0);

            IPredicate personPredicate = new FieldCompareSetPredicate(
                PersonFields.PersonId,
                null,
                JobFields.EstimatorId,
                null,
                SetOperator.In,
                null,
                queryRelations,
                null,
                -1,
                null,
                false,
                groupBy);

            selectFilter.PredicateExpression.Add(personPredicate);

            _adapter.OpenConnection();
            _adapter.FetchEntityCollection(estimatorCollection, selectFilter, prefetchJobs);
            _adapter.CloseConnection();

The second thing (which is similar but more complicated) is to do the same thing but have aggregates on two different tables (Billing, and Payment). These are both related to the Job by a many-to-one relationship (many Billings and Payments per Job). I want to do the same thing as above with the exception that I want to return a Job and corresponding Person collections when the SUM(Billing.Amount) - SUM(Payment.Amount) != 0. This is like the first query but with an additional aggregate. For some reason I cannot wrap my head around this one!

Fist of all, Did the first thing work? Did you received the expected results? I think this relation is unnecessary:

queryRelations.Add(PersonEntity.Relations.JobEntityUsingEstimatorId);

as your subquery don't require it:

...
INNER JOIN Billing ON Job.JobId = Billing.JobId
...

With the other case, I think you need to include the Payment relation at Subquery, at groupBy and Having, and it should work.

I did do some revisions:

            //Job->Billing
            IRelationCollection jobBillingRelation = new RelationCollection();
            jobBillingRelation.Add(JobEntity.Relations.BillingEntityUsingJobId);

            IGroupByCollection grouping = new GroupByCollection();
            grouping.Add(JobFields.EstimatorId);
            grouping.HavingClause = new PredicateExpression(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum) <= 0);

            IRelationPredicateBucket selectEstimators = new RelationPredicateBucket(
                new FieldCompareSetPredicate(
                    PersonFields.PersonId,
                    null,
                    JobFields.EstimatorId,
                    null,
                    SetOperator.In,
                    null,
                    jobBillingRelation,
                    "",
                    -1,
                    null,
                    false,
                    grouping));

            _adapter.OpenConnection();
            _adapter.FetchEntityCollection(estimatorCollection, selectEstimators);
            _adapter.CloseConnection();

But I found that it did not return anything. The predicate expression in the RelationPredicateBucket produced the following under the debug visualization:

(
    PersonEntity.[PersonId] IN 
    (
        SELECT JobEntity.[EstimatorId] FROM 
        (
            JobEntity  INNER JOIN BillingEntity  ON  JobEntity.JobId=BillingEntity.JobId
        )
        GROUP BY JobEntity.[EstimatorId] HAVING 
        (
            SUM
            (
                BillingEntity.[Amount]
            )
            <= @Amount1
        )
    )
)

@Amount1=0. That seems to be right to me... It did not retun any values as it should have. I am not sure what exactly the problem could be.

Please post the SQL generated (verbose level.... LLBLGenPro Help - Using generated code - and debugging).

Thanks for the help! I finally got it. The problem was the Join Hint was not picking up cases where the sum was equal to null (No Billings). Here is the full code in all of its glory:

            //relations
            //Job->Billing
            IRelationCollection jobBillingRelation = new RelationCollection();
            jobBillingRelation.Add(JobEntity.Relations.BillingEntityUsingJobId);
            jobBillingRelation.ObeyWeakRelations = true;

            IGroupByCollection grouping = new GroupByCollection();
            grouping.Add(JobFields.EstimatorId);
            grouping.Add(JobFields.JobId);
            grouping.HavingClause = new PredicateExpression(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum) <= 0);
            grouping.HavingClause.AddWithOr(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum) == DBNull.Value);

            IRelationPredicateBucket selectEstimators = new RelationPredicateBucket(
                new FieldCompareSetPredicate(
                    PersonFields.PersonId,
                    null,
                    JobFields.EstimatorId,
                    null,
                    SetOperator.In,
                    null,
                    jobBillingRelation,
                    "",
                    -1,
                    null,
                    false,
                    grouping));

            //Load Job Prefetch Path
            IPrefetchPath2 prefetchJobs = new PrefetchPath2((int)EntityType.PersonEntity);
            IGroupByCollection jobGrouping = new GroupByCollection();
            jobGrouping.Add(JobFields.JobId);
            jobGrouping.HavingClause = new PredicateExpression(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum) <= 0);
            jobGrouping.HavingClause.AddWithOr(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum) == DBNull.Value);

            IPredicateExpression jobSelection = new PredicateExpression(
                new FieldCompareSetPredicate(
                    JobFields.JobId,
                    null,
                    JobFields.JobId,
                    null,
                    SetOperator.In,
                    null,
                    jobBillingRelation,
                    "",
                    -1,
                    null,
                    false,
                    jobGrouping));

            ISortExpression sort = new SortExpression(JobFields.StartDate | SortOperator.Ascending);
            prefetchJobs.Add(PersonEntity.PrefetchPathJob, -1, jobSelection, null, sort);

            _adapter.OpenConnection();
            _adapter.FetchEntityCollection(estimatorCollection, selectEstimators, -1, null, prefetchJobs);
            _adapter.CloseConnection();

This little exercise really helped with understanding the DQE. Thanks for the help! I still need to figure out the other problem. I will post that code as well as soon as I am done.