- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Stuck on query with JOIN + AND
Joined: 07-Aug-2006
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...!
Joined: 07-Aug-2006
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.
Joined: 29-Jan-2006
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.
Joined: 17-Aug-2003
This thread deals with the same probs as this thread? http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7428
Joined: 07-Aug-2006
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.
Joined: 17-Aug-2003
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.
Joined: 17-Aug-2003
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.
Joined: 07-Aug-2006
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)
Joined: 17-Aug-2003
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 You can create an EntityRelation instance which simply represents a relation between case and stage. After all, The Relations classes do that too
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
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?
Joined: 07-Aug-2006
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!