Hi

I'm trying to code this query with llblgen but I don't succeed:

SELECT DISTINCT t2.*
FROM   [MyTable] t1
    LEFT JOIN [MyTable2]
        ON t1.[T2_ID] = [MyTable2].[T2_ID]
    LEFT JOIN [MyTable3]
        ON [MyTable3].[T3_ID] = [MyTable2].[T3_ID]
    CROSS JOIN [MyTable] t2
WHERE  
(
    (
        t2.[FIELD_1] = '70D988F3-B747-DF11-85A7-00247EF9438E'
        AND 
        (
            t1.[LAST_MODIF] >= '2012-11-30T16:45:22.650'
            OR  [MyTable2].[LAST_MODIF] >= '2012-11-30T16:45:22.650'
            OR  [MyTable3].[LAST_MODIF] >= '2012-11-30T16:45:22.650'
        )
        AND t1.LEFT_ID BETWEEN t2.LEFT_ID AND t2.RIGHT_ID
    )
)
ORDER BY t2.LEFT_ID 

In fact, I'm using nested set model to handle an hierarchy, and I'm trying to select all nodes modified since a given date, and all their parents. This query works in SQL (althought I don't like the cross join) but I don't know how to translate it to llblgen ?

Thank you for your help, Fab

PS: I'm using llblgen 3.5

Hi Fab,

• Please elaborate more about the 'nested set model to handle a hierarchy'.

• Also, post the code you have so far. BTW, take a look at Filtering and sorting.

• What does the cross join there?

The nested set is a way to organize an hierarchy in db: http://en.wikipedia.org/wiki/Nested_set_model

The cross join here is used to select all parents , i.e. all node with left < currentNode.left and righ > currentNode.Right, where currentNode is the node modified (the one that meet the filter on the date)

Currently I don't have any code because I'm still looking how to do it. My problem was how to select all field from the second table, because by default llblgen select fields from the 1st table. So I've search a way to change the original query.

In fact, I'm more looking how to simplify the query so that it's more performant and easier to translate in llblgen. I currently ended with this query, but I'm not yet sure that it'll produce the same results (it seems, I'm still testing)

SELECT DISTINCT [MyTable].*
FROM   [MyTable]
    CROSS JOIN [MyTable] t2
WHERE  
(
    (
        t2.MY_ID IN 
        (
            SELECT [MyTable].MY_ID 
            FROM [MyTable]
                LEFT JOIN [MyTable2] ON [MyTable].MY_ID = [MyTable2].MY_ID
                LEFT JOIN [MyTable3] ON [MyTable3].[EM_ID] = [MyTable2].[EM_ID]
            WHERE 
                [MyTable].[FIELD_1] = '70D988F3-B747-DF11-85A7-00247EF9438E'
                AND 
                (
                    [MyTable].[LAST_MODIF] >= '2012-11-30T16:45:22.650'
                    OR  [MyTable2].[LAST_MODIF] >= '2012-11-30T16:45:22.650'
                    OR  [MyTable3].[LAST_MODIF] >= '2012-11-30T16:45:22.650'
                )
        )
        
        AND t2.LEFT_ID BETWEEN [MyTable].LEFT_ID AND [MyTable].RIGHT_ID
    )
)
ORDER BY [MyTable].[VF_LEFT]  

For this query I shouldn't have any problem to translate to llblgen And as bonus it seems to be more efficient:

1st: CPU time = 109 ms, elapsed time = 515 ms. 2nd: CPU time = 47 ms, elapsed time = 195 ms.

IMHO, I recommend pulling all records and create the hierarchy at the client side. I believe processing the records at the client side would be faster than at the database side.