Stuck on query with JOIN + AND

Posts   
 
    
renesisx
User
Posts: 44
Joined: 07-Aug-2006
# Posted on: 04-Sep-2006 13:02:35   

This is roughly the query I'm trying to replicate:


SELECT *
FROM Case
LEFT JOIN Stage ON Stage.TransactionTypeId = Case.TransactionTypeId
LEFT JOIN CaseStageStatus ON CaseStageStatus.CaseID = Case.CaseID
AND CaseStageStatus.StageID = Stage.StageID
WHERE Case.CaseID = 2

I've tried this, but the results are wrong:


        Dim fields As New ResultsetFields(6)
        fields.DefineField(StageFields.Description, 0, "Description", "Stage")
        fields.DefineField(CaseStageStatusFields.CaseID, 1, "CaseID", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.StageID, 2, "StageID", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.CaseStageStatusID, 3, "CaseStageStatusID", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.Completed, 4, "Completed", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.ExpectedCompletion, 5, "ExpectedCompletion", "CaseStageStatus")

        Dim relations As IRelationCollection = New RelationCollection()
        relations.Add(CaseEntity.Relations.TransactionTypeEntityUsingTransactionTypeID, JoinHint.Left)
        relations.Add(TransactionTypeEntity.Relations.StageEntityUsingTransactionTypeID, JoinHint.Left)
        relations.Add(CaseStageStatusEntity.Relations.CaseEntityUsingCaseID, JoinHint.Left)
        relations.Add(CaseStageStatusEntity.Relations.StageEntityUsingStageID, JoinHint.Left)

        Dim objCaseSelectFilter As New PredicateExpression()
        objCaseSelectFilter.Add(CaseFields.CaseID = 2)

        Dim data As New System.Data.DataTable
        Dim dao As New TypedListDAO()
        dao.GetMultiAsDataTable(fields, data, 0, Nothing, objCaseSelectFilter, relations, False, Nothing, Nothing, 0, 0)

So, the first row is correct, e.g.


"Contract received" | 2 | 1 | 3 | NULL | 28 / 10 / 2006

The second row is:


"Invoice sent" | 2 | 1 | 3 | NULL | 28 / 10 / 2006

Where it should be:


"Invoice sent" | NULL | NULL | NULL | NULL | NULL

Any support people around today who can help?

I need to get this working by tomorrow...!

mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 04-Sep-2006 13:27:46   

Hi,

Try setting relation.CustomFilter property instead of filter.

renesisx
User
Posts: 44
Joined: 07-Aug-2006
# Posted on: 04-Sep-2006 13:35:36   

mihies wrote:

Hi,

Try setting relation.CustomFilter property instead of filter.

Could you show me where you mean? I'm not sure where to set this property..

The problem seems to be two-fold, looking at the output SQL that is generated.

1) It ignores 2 of my join hints and uses RIGHT JOIN 2) It totally ignores the second JOIN on "ConveyancingCaseStageStatusEntity" - it obviously doesn't like joining on the same table twice.

mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 04-Sep-2006 13:51:39   

If you want to put AND clause into relation then you should write something like: IEntityRelation relation = CaseStageStatusEntity.Relations.CaseEntityUsingCaseID relation.CustomFilter = CaseFields.CaseID = 2 relations.Add(relation)

So the condition will appear as a right part of AND clause in JOIN.

Don't know about the join problem though.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 04-Sep-2006 16:22:45   

This thread deals with the same probs as this thread? http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7428

Frans Bouma | Lead developer LLBLGen Pro
renesisx
User
Posts: 44
Joined: 07-Aug-2006
# Posted on: 04-Sep-2006 17:13:39   

Otis wrote:

This thread deals with the same probs as this thread? http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7428

Pretty much.

I'm fairly desperate to find the answer though. The documentation doesn't cover this and LLBLGen is vast.

I'm not trying to be lazy, but it could take many days of trawling all the LLBLGen structures and stepping through the generated code to try and find the answer, while there might be someone on here who has hit the same issue in the past and has the experience to point me in the right direction quickly.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 04-Sep-2006 18:01:44   

renesisx wrote:

Otis wrote:

This thread deals with the same probs as this thread? http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7428

Pretty much.

I'm fairly desperate to find the answer though. The documentation doesn't cover this and LLBLGen is vast.

I'm not trying to be lazy, but it could take many days of trawling all the LLBLGen structures and stepping through the generated code to try and find the answer, while there might be someone on here who has hit the same issue in the past and has the experience to point me in the right direction quickly.

Though the solution Miha gave in this thread, does that work or does that produce another query?

What you might have problems with is the automatic switch between LEFT and RIGHT joins by llblgen pro.

Looking at your relations added:


        relations.Add(CaseEntity.Relations.TransactionTypeEntityUsingTransactionTypeID, JoinHint.Left)
        relations.Add(TransactionTypeEntity.Relations.StageEntityUsingTransactionTypeID, JoinHint.Left)
        relations.Add(CaseStageStatusEntity.Relations.CaseEntityUsingCaseID, JoinHint.Left)
        relations.Add(CaseStageStatusEntity.Relations.StageEntityUsingStageID, JoinHint.Left)

You define joinhints. These are semantical, and signal a joinhing between the START entity and the END entity in the relation specified.

So take this line for example:

relations.Add(CaseEntity.Relations.TransactionTypeEntityUsingTransactionTypeID, JoinHint.Left)

This means that you want: Case LEFT JOIN TransactionType

Ok, moving on to:

relations.Add(TransactionTypeEntity.Relations.StageEntityUsingTransactionTypeID, JoinHint.Left)

This means you want TransactionType LEFT JOIN Stage

Ok, next relation. THis is the one where it goes wrong I think. Please pay attention to what you wrote:

relations.Add(CaseStageStatusEntity.Relations.CaseEntityUsingCaseID, JoinHint.Left)

Now, this means you want: CaseStageStatus LEFT JOIN Case

However 'Case' is already in the join list, namely due to the first relation you specified.

This thus means it will do: ... RIGHT JOIN CaseStageStatus

Because you've specified a LEFT JOIN **away ** from Case, towards CaseStageStatus. As in a JOIN list you can only specify 2 tables in the first JOIN statement, and all other JOIN statements involve the complete join list at that point and the table to join, LLBLGen Pro has to switch to RIGHT JOIN in this case, as Case is already in the join list.

Looking at the names of the entities, I think you want a Join hint of Right here so it will become ... LEFT JOIN CaseStageStatus

That's also why I suggested a right join hint in the other thread.

The AND predicate clause to add to the ON clause of the join is done as discussed above with the CustomFilter property of the relation you add.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 04-Sep-2006 18:16:56   

Erm, the query you posted and the relations you added don't match.

First of all: when you use a LEFT or RIGHT join, order is important.

Ok, your query is:


SELECT  *
FROM    Case LEFT JOIN Stage 
        ON Stage.TransactionTypeId = Case.TransactionTypeId
        LEFT JOIN CaseStageStatus 
        ON CaseStageStatus.CaseID = Case.CaseID
        AND CaseStageStatus.StageID = Stage.StageID
WHERE Case.CaseID = 2

The first join is thus between Case and Stage. However the first relation you add is between Case and TransactionType. So, I think the code should be:


Dim relations As IRelationCollection = New RelationCollection()
relations.Add(CaseEntity.Relations.StageEntityUsingTransactionTypeID, JoinHint.Left)
Dim relationToAdd As EntityRelation = CaseEntity.Relations.CaseStageStatusEntityUsingCaseID
relationToAdd.CustomFilter = New PredicateExpression(CaseStageStatusFields.StageID = StageFields.StageID)
relations.Add(relationToAdd, JoinHint.Left)

Though I have a hard time understanding why you combine LEFT joins with a double-entity ON clause: to me the query is a bit farfetched, but I hope the code I stated above works.

Frans Bouma | Lead developer LLBLGen Pro
renesisx
User
Posts: 44
Joined: 07-Aug-2006
# Posted on: 04-Sep-2006 19:04:06   

I apologise about that - it's because in SQL we could cheat and miss out the middle TransactionType table which has foreign keys in the Stage and Case tables.

This is the actual working query we are trying to replicate:


SELECT Stage.DisplayOrder, Stage.Description, CaseStageStatus.CaseID, CaseStageStatus.StageID, CaseStageStatus.CaseStageStatusID, CaseStageStatus.Completed, CaseStageStatus.ExpectedCompletion
FROM Case
LEFT JOIN TransactionType ON Case.TransactionTypeId = TransactionType.TransactionTypeId
LEFT JOIN Stage ON Stage.TransactionTypeId = TransactionType.TransactionTypeId
LEFT JOIN CaseStageStatus ON CaseStageStatus.CaseID = Case.CaseID
AND CaseStageStatus.StageID = Stage.StageID
WHERE Case.CaseID = 2
ORDER BY Stage.DisplayOrder

We couldn't get this to work:


IEntityRelation relation = CaseStageStatusEntity.Relations.CaseEntityUsingCaseID
relation.CustomFilter = CaseFields.CaseID = 2
relations.Add(relation)

We tried this instead (bearing in mind we're in VB):


        Dim objCaseSelectFilter As New PredicateExpression()
        objCaseSelectFilter.Add(CaseStageStatusFields.CaseID = 2 Or CaseStageStatusFields.CaseID = DBNull.Value)

        Dim relation As IEntityRelation = CaseStageStatusEntity.Relations.CaseEntityUsingCaseID
        relation.CustomFilter = objCaseSelectFilter
        relations.Add(relation)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 04-Sep-2006 20:44:54   

renesisx wrote:

I apologise about that - it's because in SQL we could cheat and miss out the middle TransactionType table which has foreign keys in the Stage and Case tables.

With LLBLGen Pro you can cheat as well of course wink You can create an EntityRelation instance which simply represents a relation between case and stage. After all, The Relations classes do that too wink

This is the actual working query we are trying to replicate:


SELECT Stage.DisplayOrder, Stage.Description, CaseStageStatus.CaseID, CaseStageStatus.StageID, CaseStageStatus.CaseStageStatusID, CaseStageStatus.Completed, CaseStageStatus.ExpectedCompletion
FROM Case
LEFT JOIN TransactionType ON Case.TransactionTypeId = TransactionType.TransactionTypeId
LEFT JOIN Stage ON Stage.TransactionTypeId = TransactionType.TransactionTypeId
LEFT JOIN CaseStageStatus ON CaseStageStatus.CaseID = Case.CaseID
AND CaseStageStatus.StageID = Stage.StageID
WHERE Case.CaseID = 2
ORDER BY Stage.DisplayOrder

It's sad you didn't specify this query from the beginning. It's key for us we have the info as it is, so please next time, use the real code wink

We couldn't get this to work:


IEntityRelation relation = CaseStageStatusEntity.Relations.CaseEntityUsingCaseID
relation.CustomFilter = CaseFields.CaseID = 2
relations.Add(relation)

I think this is because Miha didn't check the exact syntax. You need to specify a PredicateExpression, as CustomFilter is a property of type PredicateExpression. Please see my code above in my previous posting.

We tried this instead (bearing in mind we're in VB):


        Dim objCaseSelectFilter As New PredicateExpression()
        objCaseSelectFilter.Add(CaseStageStatusFields.CaseID = 2 Or CaseStageStatusFields.CaseID = DBNull.Value)

        Dim relation As IEntityRelation = CaseStageStatusEntity.Relations.CaseEntityUsingCaseID
        relation.CustomFilter = objCaseSelectFilter
        relations.Add(relation)

Why test on null? It's not in your original query.

Did my previous posting and example code help you further?

Frans Bouma | Lead developer LLBLGen Pro
renesisx
User
Posts: 44
Joined: 07-Aug-2006
# Posted on: 05-Sep-2006 18:29:15   

Thanks for all your help.

For reference I'm showing the final query and the final code below:

OK, I quickly cleaned the query up a bit:


SELECT Stage.DisplayOrder, Stage.Description, CaseStageStatus.CaseID, CaseStageStatus.StageID, CaseStageStatus.CaseStageStatusID, CaseStageStatus.Completed, CaseStageStatus.ExpectedCompletion
FROM Case 
JOIN TransactionType ON Case.TransactionTypeId = TransactionType.TransactionTypeId
JOIN Stage ON Stage.TransactionTypeId = TransactionType.TransactionTypeId
LEFT JOIN CaseStageStatus ON CaseStageStatus.CaseID = Case.CaseID
AND CaseStageStatus.StageID = Stage.StageID
WHERE Case.CaseID = 2
ORDER BY Stage.DisplayOrder

This is the code we ended up with:


        Dim fields As New ResultsetFields(6)
        fields.DefineField(StageFields.Description, 0, "Description", "Stage")
        fields.DefineField(CaseStageStatusFields.CaseID, 1, "CaseID", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.StageID, 2, "StageID", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.CaseStageStatusID, 3, "CaseStageStatusID", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.Completed, 4, "Completed", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.ExpectedCompletion, 5, "ExpectedCompletion", "CaseStageStatus")

        Dim relations As IRelationCollection = New RelationCollection()
        relations.Add(TransactionTypeEntity.Relations.CaseEntityUsingTransactionTypeID)
        relations.Add(TransactionTypeEntity.Relations.StageEntityUsingTransactionTypeID)

        Dim relationToAdd As EntityRelation = CaseEntity.Relations.CaseStageStatusEntityUsingCaseID
        relationToAdd.CustomFilter = New PredicateExpression(CaseStageStatusFields.StageID = StageFields.StageID)
        relations.Add(relationToAdd, JoinHint.Left)

        Dim objCaseSelectFilter As New PredicateExpression()
        objCaseSelectFilter.Add(CaseFields.CaseID = 2)

        Dim sorter As New SortExpression(StageFields.DisplayOrder Or SortOperator.Ascending)

        Dim data As New System.Data.DataTable
        Dim dao As New TypedListDAO()
        dao.GetMultiAsDataTable(fields, data, 0, sorter, objCaseSelectFilter, relations, False, Nothing, Nothing, 0, 0)

Cheers guys!