Hi guys.

I'm having a bit of an issue creating the code for this. I am trying to filter a list of entities based on the sum of a few fields in the entities child entity. Basically i have 3 tables involved in this query as listed below. The top table is the one I am trying get the entities from.

PurchaseRequest Id Title...

PurchaseRequestVendorGroup Id PurchaseRequestId Foriegn key to PurchaseRequest.Id GroupName

PurchaseRequestItems Id PurchaseRequestVendorGroupId Foriegn key to PurchaseRequestVendorGroup.Id Item Quantity Price

What I want is to get all purchase requests where the sum of the items total Price * Quantity is > x amount.

I wrote a stored proc which gets the databack that i want, but i'm not sure how to translate this into a llblgen filter.

SELECT 
    Id,
    Title,
    ...
FROM
    PurchaseRequest
    INNER JOIN
        PurchaseRequestVendorGroups ON PurchaseRequestVendorGroups.PurchaseRequestId = PurchaseRequest.PurchaseRequestId
WHERE 
    (SELECT SUM(Price * Quantity) AS Total
    FROM    PurchaseRequestItem
    WHERE   (PurchaseRequestVendorGroupId = PurchaseRequestVendorGroups.PuchaseRequestVendorGroupId)
    GROUP BY PurchaseRequestVendorGroupId) > 1000

I think the following select can do the job, right?

SELECT PR.Title
FROM PurchaseRequest PR
INNER JOIN PurchaseRequestVendorGroup PRVG 
ON PR.Id = PRVG.PurchaseRequestId
INNER JOIN PurchaseRequestItems PRIs 
ON PRVG.Id = PRIs.PurchaseRequestVendorGroupId
GROUP BY PR.Title 
HAVING SUM(PRIs.Price*PRIs.Quantity) > 10000

Try to do this by LLBLGen Pro, construct a filter for the HAVING, and assign it to the GroupByClause.HavingClause

The select you wrote does give me the result I am expecting as does the first on that I wrote. I am still not sure how to put this into a predicate expression to filter the results my entity collection getmulti() method returns. You mentioned

Try to do this by LLBLGen Pro, construct a filter for the HAVING, and assign it to the GroupByClause.HavingClause

but i don't see where I would add a groupby or having clause. What llbl objects would i use to code this?

Zak,

Take a look at the LLBLGen Pro Programmer Guide:

Page - Generated code - Using the typed view and typed list classes, Adapter Section - Using GroupByCollection and Having Clauses

In it you'll find a great example

So what your saying is that this cannot be done using entity collection objects?

I would rather not have to make a typed list with the same fields as my entity object just so that I can get a collection of rows back.

Zak wrote:

So what your saying is that this cannot be done using entity collection objects?

I would rather not have to make a typed list with the same fields as my entity object just so that I can get a collection of rows back.

You can do it, write the where clause using a FieldCompareSetPredicate. The FieldCompareSetPredicate accepts a groupby clause so you can setup the aggregation and groupby + having clause. You have to use the having clause.

If you can't get it working, please post the code you have so far using the FieldCompareSetPredicate and I'll help you further.

If you can create the filter properly, you should be able to pass the filter into the prefetch path.

I'm not sure how the group by collection would work then, although you can specify additional filter relations in the prefetch as well, which might allow for this.

Two other options that might be easier:

1. Create a view for purchaserequests that applies your criteria, then generate it as an entity that relates 1-1 with the existing purchase request entity (the table). Now set your prefetch to get the view first, then the table.

<parent object> ---> <view entity> ---> <table entity>

Now you will only purchase requests that match the criteria set forth in the view.

1. Use a FieldCompareSetPredicate to determine which purchase request rows/entities to return, and pass this into the prefetch path for purchase requests. The FieldCompareSetPredicate is basically like using an IN clause in SQL.

This would look something like (pseudocode):

SELECT
   purchaserequest.id
FROM
   purchaserequest
WHERE
  purchaserequest.id IN
  (SELECT purchaserequestid FROM purchaserequestvendorgroup WHERE <criteria>)

I think either of these options would work. I'm curious whether the original solution could be made to work (creating a predicate with having/groupby and additional relations, then adding it to the prefetch).

Note: the above is assuming that you are fetching this collection as part of a larger graph. If your base object is PurchaseRequest, and you are fetching a collection of them, you would add the filter to the fetch (FetchEntityCollection) and not the prefetch path.

Option 1a.

You could also set up the view to always return the aggregate as a field, and then use a predicate filter on that field (which could then be a WHERE clause and not a HAVING clause, if I'm not mistaken).

Hey Otis.

This is what i've gotten so far the query being created by this has a where statement but that statement is blank.

 llbl.IExpression totalExpression = new llbl.Expression(DAL.HelperClasses.PurchaseRequestItemFields.Price, llbl.ExOp.Mul, DAL.HelperClasses.PurchaseRequestItemFields.Quantity);

                llbl.IEntityField totalField = new llbl.EntityField();
                totalField.ExpressionToApply = totalExpression;
                totalField.AggregateFunctionToApply = llbl.AggregateFunction.Sum;

                llbl.IGroupByCollection groupBy = new llbl.GroupByCollection();
                
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.AddedBy);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.ApplicantFullName);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.ApplicantPhone);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.ApplicantWindowsLogin);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.ApproverFullName);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.ApproverPhone);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.ApproverWindowsLogin);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.DeliveryAddress);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.DeliveryCity);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.DeliveryCountry);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.DeliveryDateRequired);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.DeliveryLocationId);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.DeliveryPhone);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.DeliveryPostalCode);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.DeliveryProvince);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.IsConfidential);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.ProjectId);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.PurchaseRequestId);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.StatusId);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.Title);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.TypeId);
                groupBy.Add(DAL.HelperClasses.PurchaseRequestFields.UpdatedOn);


                llbl.IPredicateExpression havingFilter = new llbl.PredicateExpression();
                havingFilter.Add(new llbl.FieldCompareValuePredicate(totalField, llbl.ComparisonOperator.GreaterEqual, txtMinAmount.Text));
                groupBy.HavingClause = havingFilter;

                llbl.FieldCompareSetPredicate subFilter = new llbl.FieldCompareSetPredicate(null, totalField, llbl.SetOperator.Equal, null);
                subFilter.GroupByClause = groupBy;

I think that's because I haven't set any predicate for the "subFilter" FieldCompareSetPredicate. But I'm not sure how to set that up, since i don't have a field to compare the set to.

Where abouts am i going wrong.

Your original query is actually the same as:

SELECT
    Id,
    Title,
    ...
FROM
    PurchaseRequest
    INNER JOIN
        PurchaseRequestVendorGroups ON PurchaseRequestVendorGroups.PurchaseRequestId = PurchaseRequest.PurchaseRequestId
WHERE
    PurchaseRequestVendorGroupId IN
    (
        SELECT  PurchaseRequestVendorGroupId
        FROM    PurchaseRequestItem
        GROUP BY PurchaseRequestVendorGroupId
        HAVING SUM(Price * Quantity) > 1000
    )

which I can write as:

RelationCollection relations = new RelationCollection();
relations.Add(PurchaseRequestEntity.Relations.PurchaseRequestVendorGroup);

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(PurchaseRequestVendorGroupsFields.PurchaseRequestVendorGroupId);
groupBy.HavingClause = new PredicateExpression(
    (PurchaseRequestItemFields.Price
        .SetExpression(PurchaseRequestItemFields.Price * PurchaseRequestItemFields.Quantity)
        .SetAggregateFunction(AggregateFunction.Sum)
        > 1000));

PredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(
    PurchaseRequestVendorGroupsFields.PurchaseRequestVendorGroupId, 
    PurchaseRequestVendorGroupsFields.PurchaseRequestVendorGroupId, 
    SetOperator.In, null, null, string.Empty, 0, null, false, groupBy));

PurchaseRequestCollection purchaseRequests = new PurchaseRequestCollection();
purchaseRequests.GetMulti(filter, relations);

Also, if anyone is interested in the following Adapter code based on Customers - Orders - [Orders Details] tables on Northwind (which are a replica of your tables):

            DataAccessAdapter adapter = new DataAccessAdapter();
            EntityCollection EC = new EntityCollection(new CustomersEntityFactory());

            IExpression totalExpression = new Expression(OrderDetailsFields.UnitPrice, ExOp.Mul, OrderDetailsFields.Quantity);
            IEntityField2 totalField = EntityFieldFactory.Create(OrderDetailsFieldIndex.OrderId);
            totalField.ExpressionToApply = totalExpression;
            totalField.AggregateFunctionToApply = AggregateFunction.Sum;
            
            IGroupByCollection groupBy = new GroupByCollection();
            groupBy.Add(OrderDetailsFields.OrderId);
            IPredicateExpression havingFilter = new PredicateExpression();
            havingFilter.Add(new FieldCompareValuePredicate(totalField, null, ComparisonOperator.GreaterThan, 5000.0M));
            groupBy.HavingClause = havingFilter;

            IPredicateExpression filter = new PredicateExpression();            
            filter.Add(new FieldCompareSetPredicate(EntityFieldFactory.Create(OrdersFieldIndex.OrderId), null,
                EntityFieldFactory.Create(OrderDetailsFieldIndex.OrderId), null,
                SetOperator.In, null, null, "", 0, null, false, groupBy));

            RelationPredicateBucket Bucket = new RelationPredicateBucket();
            Bucket.PredicateExpression.Add(filter);
            Bucket.Relations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId);
            adapter.FetchEntityCollection(EC, Bucket);
            

Sweet thanks so much Otis. Once I seen the way you had rewritten the SQL query things started to make more sense.. or maybe it's just because it's a fresh new day. Who knows?

Just in case other people actually reference this thread there was two things that I had to change to make sure the Sub-Select in the where statement is selecting from the correct table. I've added the changed code below the original (which is commented out).

Otis wrote:

RelationCollection relations = new RelationCollection();
relations.Add(PurchaseRequestEntity.Relations.PurchaseRequestVendorGroup);

GroupByCollection groupBy = new GroupByCollection();
//groupBy.Add(PurchaseRequestVendorGroupsFields.PurchaseRequestVendorGroupId);
groupBy.Add(PurchaseRequestItemFields.PurchaseRequestVendorGroupId);
groupBy.HavingClause = new PredicateExpression(
    (PurchaseRequestItemFields.Price
        .SetExpression(PurchaseRequestItemFields.Price * PurchaseRequestItemFields.Quantity)
        .SetAggregateFunction(AggregateFunction.Sum)
        > 1000));

PredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(
    PurchaseRequestVendorGroupsFields.PurchaseRequestVendorGroupId, 
    //PurchaseRequestVendorGroupsFields.PurchaseRequestVendorGroupId,
    PurchaseRequestItemFields.PurchaseRequestVendorGroupId, 
    SetOperator.In, null, null, string.Empty, 0, null, false, groupBy));

PurchaseRequestCollection purchaseRequests = new PurchaseRequestCollection();
purchaseRequests.GetMulti(filter, relations);

Again thanks for all the help guys.