Using Aggregrate functions with FetchCollection

Posts   
 
    
worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 27-Jan-2007 15:15:10   

Hi folks,

I'm trying to get a collection of entities (only every really returns one row, or none) where a related entities min value of a field is <= to a parameter. LLBL has been steadily eroding my sql skills but i believe this is possible.

What happens is when the below code runs the aggregrate doesn't get translated into the sql.

Now if i were writing sql i'd probably have a subquery that got a list of TaskIds that matched my Min(StartDate) / Max(StartDate) criteria and joined those id's onto the Task table. How do i make this happen?


public static Shivam.Web.EntityCollection<ProjectResourceEntity> RetrieveUsersProjects(int userId, DateTime startDate, DateTime endDate)
        {
            SortExpression sortExpression = new SortExpression(ProjectFields.Name | SortOperator.Ascending);
            RelationPredicateBucket bucket = new RelationPredicateBucket(ProjectResourceFields.UserId == userId);
            
            bucket.PredicateExpression.AddWithAnd(TaskFields.StartDate.SetAggregateFunction(AggregateFunction.Min) <= startDate);
            bucket.PredicateExpression.AddWithAnd(TaskFields.EndDate.SetAggregateFunction(AggregateFunction.Max) >= endDate);
            bucket.Relations.Add(ProjectResourceEntity.Relations.ProjectEntityUsingProjectId);
            bucket.Relations.Add(ProjectEntity.Relations.ProjectResourceEntityUsingProjectId);
            bucket.Relations.Add(ProjectEntity.Relations.TaskEntityUsingProjectId);         

            return Util.FetchCollection<ProjectResourceEntity>(null, (int)EntityType.ProjectResourceEntity, bucket, sortExpression,
                ProjectResourceEntity.PrefetchPathProjectUsingProjectId);
        }

The Util.FetchCollection method's code is missing here but imagine a method just passed everything to DataAccessAdapter.FetchEntityCollection

Chester
Support Team
Posts: 223
Joined: 15-Jul-2005
# Posted on: 28-Jan-2007 02:46:44   

worldspawn wrote:

Hi folks,

I'm trying to get a collection of entities (only every really returns one row, or none) where a related entities min value of a field is <= to a parameter. LLBL has been steadily eroding my sql skills but i believe this is possible.

Are you asking for the resulting sql similar (in principle) to something like this:


select x,y,z
from Orders
   inner join OrderDetails on Orders.OrderID = OrderDetails.OrderID
where OrderDetails.Quantity > @someValue

You're talking about a subquery though so I must be missing something. If not, the above is easily achieved with a RelationPredicateBucket filtering on (OrderDetailsFields.Quantity > 123).

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 28-Jan-2007 06:03:37   

Nope. Below is an approximation of what i'm trying to achieve. Note the use of aggregate functions.

I'm trying to get a list of all the projectresource rows for one user where that projectresource related project tasks fall within a date range. The Min/Max on the task table effectivily indicate the start and end date of the project.


declare @startDate datetime, @endDate datetime, @userId int

set @startDate = '1/1/2007'
set @endDate = '1/1/2008'
set @userId = 3

select distinct ProjectResource.* 
from ProjectResource
JOIN Project ON ProjectResource.ProjectId = Project.ProjectId
JOIN (SELECT ProjectId, Min(StartDate) [startDate], Max(EndDate) [endDate] FRom Task GROUP BY ProjectId) [foo]
    ON [foo].ProjectId = Project.ProjectId
WHERE
    ProjectResource.UserId = @userId
    AND
    StartDate <= @startDate and EndDate >= @endDate

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 28-Jan-2007 16:14:48   

the curent query isn't possible as sub-select within the from clause are not possible.

This query may work for you though

select pr.*,
          (select min(StartDate) from Task t where t.ProjectId = pr.ProjectId) as StartDate,
          (select max(EndDate) from Task t where t.ProjectId = pr.ProjectId) as EndDate,
from ProjectResource pr
where pr.UserId = @userId
 and    StartDate >= @startDate
 and    EndDate <= @endDate

The syntax may not be 100%, i don't have a db to test against right now, but it should be pretty close.

When i have extended entities like this I create an extended factory of the target entity. and override the CreateFields function. heres the code.

public class ExtendedOrderHeaderEntityFactory : OrderHeaderEntityFactory
{
    /// <summary>
    /// Create the fields for the customer entity and adjust the fields collection 
    /// with the entity field object which contains the scalar expression
    /// </summary>
    /// <returns></returns>
    public override IEntityFields2 CreateFields()
    {
        IEntityFields2 toReturn = base.CreateFields();
        toReturn.Expand(2);
        
        IEntityField2 GrossSales = new EntityField2("GrossSales", new ScalarQueryExpression(OrderDetailFields.Gross.SetAggregateFunction(AggregateFunction.Sum), (OrderDetailFields.OrderNumber == OrderHeaderFields.OrderNumber)));
        toReturn.DefineField(GrossSales, toReturn.Count - 2);
        IEntityField2 NetSales = new EntityField2("NetSales", new ScalarQueryExpression(OrderDetailFields.Net.SetAggregateFunction(AggregateFunction.Sum), (OrderDetailFields.OrderNumber == OrderHeaderFields.OrderNumber)));
        toReturn.DefineField(NetSales, toReturn.Count - 1);
        
        return toReturn;
    }
}

in this example I get a gross/net rollup of the order within the order header. you could do something very similar by getting the min/max dates you require.

I can't speak to wheter this is the best way to create addtional fields within the entity, if you need this information all the time then placing the code directly in the entity itself would be better than the factory.

once these fields are created just include the date predicates in within the filter.

IPredicateExpression filter = new PredicateExpression();
filter.Add(ProjectResourceFields[ProjectResourceFieldIndex.AmountOfFields + 1] >= StartDate);
filter.Add(ProjectResourceFields[ProjectResourceFieldIndex.AmountOfFields + 2] <= EndDate);
filter.Add(ProjectResourceFields.UserId == UserId);
worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 29-Jan-2007 01:25:51   

Thanks Jason.

I don't want the result of the aggregate function to return to the application. The end result that i want is just an entity collection. I guess I could get the resulting values in one query with a typedlist and then pass those values in for another query... but that'd suk.

It's not that I want a subquery in the from clause, i'm just trying to demonstrate what i want as a result. Any solution that achieves this is fine.

I'm sure LLBL could do this. It's never let me down so far, any bizarre scenario I can think of it's seems to be able to spit out a solution. Maybe this is one for Frans..?

(although ur code looks interesting Jason and i may use it elsewhere - thanks)

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 29-Jan-2007 03:51:53   

you could just use the scalar queries in the where clause. that way you could filter on the agregate dates without having them within colection itself. If you use this method you'll need a RelationPredicateBucket to relate ProjectResource to Task.

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 29-Jan-2007 23:48:07   

Thats what I've tried to do (see first post)


bucket.PredicateExpression.AddWithAnd(TaskFields.StartDate.SetAggregateFunction(AggregateFunction.Min) <= startDate);

It doesn't work, llbl never outputs the min function to the sql.

It'd be nice if the support team would offer some... support.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 30-Jan-2007 08:49:59   

select distinct ProjectResource.* from ProjectResource JOIN Project ON ProjectResource.ProjectId = Project.ProjectId JOIN (SELECT ProjectId, Min(StartDate) [startDate], Max(EndDate) [endDate] FRom Task GROUP BY ProjectId) [foo] ON [foo].ProjectId = Project.ProjectId WHERE ProjectResource.UserId = @userId AND StartDate <= @startDate and EndDate >= @endDate

Should be turned to:

SELECT  ProjectResource.* 
FROM ProjectResource
WHERE
ProjectResource.UserId = @userId
AND
ProjectId IN
 (
  SELECT ProjectId 
  FROM Task 
  GROUP BY ProjectId
  HAVING Min(StartDate) <= @startDate 
  AND Max(EndDate) >= @endDate 
  ) 

I removed the Join to the Project table, since ProjectResource & Task tables have the ProjectId field.

I think the above query should work, and then you should use a FieldCompareSetPredicate to implement it.

worldspawn avatar
worldspawn
User
Posts: 321
Joined: 26-Aug-2006
# Posted on: 02-Feb-2007 04:04:43   

Ok thanks Walaa. I'll give it a whirl.

Is there anyway to represent FieldCompareSetPredicate using operator overloads?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 02-Feb-2007 08:14:01   

Is there anyway to represent FieldCompareSetPredicate using operator overloads?

Unfortunatly no.