How to get this into a predicate expression.

Posts   
 
    
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 15-Nov-2004 22:41:43   

I don't know if this is even possible, but wondered if anyone had any ideas.

Let's say I have a table structure as follows

Item
  ID
  StartDate
  EndDate

ItemInfo
  ID
  ItemID
  FieldID
  Value

Field
  ID
  Name

So that for each Item - there could be a dynamic number of ItemInfo entires for each possible Field value.

The problem that I am now facing is trying to sort my ItemCollection sorted by results found in a Field - ItemInfo pair.

For example

I want to retrieve all Items that have contains an ItemInfo value of 'Smith' for the Field with the Name "LastName" - additionally (and this is where I am having problems) I want it sorted by a value from ItemInfo with a Field with the name "State".

I could describe it in a sql with something like

 SELECT Item.*, (select ItemInfo.Value from ItemInfo JOIN Field ON ItemInfo.FieldID = Field.FieldID WHERE Field.Name = 'State' AND ItemInfo.ID = Item.ID) AS SortOrder
FROM Item 
  JOIN ItemInfo ON Item.ItemID = ItemInfo.ItemID
  JOIN Field ON ItemInfo.FieldID = Field.FieldID
  WHERE ItemInfo.Value = 'Smith'
    AND Field.Name = 'LastName'
  ORDER BY SortOrder

But I am at a loss for trying to get this into a predicate - I know that I will have to use an alias, but am just unclear on how to go about doing it... Any help would be greatly appreciated.

Thanks and as always thanks for dedication of the forum members and esp. Frans.

Hal

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 16-Nov-2004 11:03:49   

If I'm not mistaken, the query you want is:


SELECT  Item.*
FROM    Item INNER JOIN ItemInfo
        On  Item.Id = ItemInfo.ItemID
        INNER JOIN Field
        ON  ItemInfo.FieldID = Field.ID
            AND
            Field.Name='State'
WHERE   Item.ID IN
        (
            SELECT  ItemID
            FROM    ItemInfo II INNER JOIN Field F
                    ON  II.FieldID = F.ID
                        AND 
                        ItemInfo.Value='Smith'
                        AND
                        Field.Name='LastName'
        )
ORDER BY
        ItemInfo.Value ASC

The query consists of two parts: a join part for the sorting and a where part for the filtering. The join part is constructed with a RelationCollection (let's assume you're using SelfServicing, otherwise you need a Relationpredicatebucket and use its Relations property) to which you add 2 relations and to the second, you also specify a CustomFilter to add the Field.Name='State' predicate.

The WHERE part is a FieldCompareSetPredicate. You pass to that predicate also a RelationCollection and to that relationcollection you add 1 relation, and you also add a CustomFilter with 2 CompareValue predicates, one for ItemInfo.Value='Smith' and one for Field.Name='LastName'. I don't think you need the aliassing in the subquery though, so try the SQL first if it works without the aliassing.

Frans Bouma | Lead developer LLBLGen Pro
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 16-Nov-2004 16:31:44   

Perfect... thanks again - as always...

Hal