I'm using the SelfServicing template and want to implement sorting objects by relevance. This is my current (simplified) structure:

video: - id (int) - name (nvarchar)

property - id (int) - name (nvarchar)

member - id (int) - name (nvarchar)

video_property - videoID (relates to video.id) - propertyID (relateds to property.id)

member_property - memberID (relates to member.id) - propertyID (relates to property.id)

now my question: i want to get a list of video's, and order it based on the number of 'property matches' for a certain user, so the video that has the highest number of properties that are 'shared' with the member will turn up first. How can i do something like this?

the SQL example would be something like:

select id, name from video order by (select count(*) from video_property where videoID=video.id and propertyID in (select propertyID from member_property where memberID=@memberID)) desc

Hi there,

Specifying custom query at SortClause is supported using the EmitAliasForExpressionAggregateField flag on the SorClause (See this thread). You have the latest version and runtime library version right?

So this is what you are going to do:

1. Work on your fetch first. This is, make sure the fetch works without the sort.

2. Construct the subquey of your sort expression. Here you will use Aggregate functions and Scalar query expression.

3. Construct the Sorter based on the step 2 (creating a field and apply that expression to the field) then set the flag EmitAliasForExpressionAggregateField to false (at SortClause).

4. Try to fetch that. If you have problems please post more info. This include: your code snippet and the generated sql.

Thanks for you reply.

Yes i'm using the latest version. I have now the following (please note that the object 'video' i referred to in my first post is in the snippet a 'node':

IPredicateExpression filter = new PredicateExpression();

IEntityField orderField = NodePropertiesFields.PropertyId.SetAggregateFunction(AggregateFunction.Count);
ISortClause clause = new SortClause(orderField, SortOperator.Descending);
clause.EmitAliasForExpressionAggregateField = false;
sort.Add(clause);

NodeCollection nodes = new NodeCollection();
nodes.GetMulti(filter, 0, sort);

this generates the following SQL:

SELECT   TOP (100) PERCENT id AS F4_0, nodeTypeID AS F4_1, title AS F4_2, friendlyURL AS F4_3, featured AS F4_4
FROM         dbo.node
ORDER BY COUNT(dbo.node_properties.propertyID) DESC

and that throws the following exception:

The multi-part identifier "dbo.node_properties.propertyID" could not be bound.

somehow i have to construct a subquery with a select and a WHERE part but i don't know how to do this. Any help would be appreciated

I think for this to work you should include the Count field in your Select list.

SELECT id, name, (select count(*) from video_property where videoID=video.id and propertyID in (select propertyID from member_property where memberID=@memberID)) as Counter 
FROM video 
ORDER BY Counter desc  

And to implement this, you should use Scalar query expressions

Is there no other way? I have a target-per-entity hierarchy so then i'd have to fetch all objects using GetMulti, and then do a in-memory sort based on the datatable result of the Scalar query expression

Why would you have to do this in-memory ? If the sort clause is being emitted into the SQL as in Walaa's example the entities would be already sorted in the collection.

Matt

Because i have my Entity classes generated from LLBLGen, and since i'm using the target-per-entity hierarchy i can't just add a field to an existing entity right? U'll have to specify a seperate datatable to get the fields from, thus losing the polymorphistic fetch which i really need

Please read the section titled Extending the CustomerEntityFactory in the following article: http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

Or a linq solution as discussed here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13908

After some struggles i've got it working, for anyone interested (or future readers):

        SortExpression sort = new SortExpression();

        IEntityField propertyCount = new EntityField(
            "PropertyCount",
            new ScalarQueryExpression(
                    NodePropertiesFields.PropertyId.SetAggregateFunction(AggregateFunction.Count),
                    (NodePropertiesFields.NodeId == NodeFields.Id) & (
                        new FieldCompareSetPredicate(
                            NodePropertiesFields.PropertyId, 
                            MemberPropertiesFields.PropertyId, 
                            SetOperator.In, 
                            (MemberPropertiesFields.MemberId == member.Id)
                        )
                    )
                )
            );
            
            ISortClause clause = new SortClause(propertyCount, SortOperator.Descending);
            clause.EmitAliasForExpressionAggregateField = false;
            sort.Add(clause);

Thanks for the feedback.