Hi,

I'm having trouble with a fetch query. I need to join onto a table twice, to get the data I need. I am using custom relations, because they do not exist in the database.

Heres the SQL that I want (slightly modified):

select dpvr.*
from maintable dpvr, performance_cache pc, performance_cache pc2
where dpvr.view_id=9
and dpvr.parent_id = pc.record_id
and pc.record_type='audit'
and pc.site_id = 264
and [b]dpvr.record_id = pc2.record_id[/b]
and pc2.record_type='section'
and pc2.site_id=264
order by pc.bandingzone desc, pc2.bandingzone desc

I thought this would be OK in LLBLGen, so I added the code, thinking that all I needed to do was make sure I set alias names for the 2 relationships to force it to join to Performance_Cache twice. But, this does not work. Heres the LLBL magic:

IEntityRelation customRelation = new EntityRelation(RelationType.OneToOne);
customRelation.AddEntityFieldPair(EntityFieldFactory.Create(MainTable.Parent_ID),
EntityFieldFactory.Create(Performance_CacheFieldIndex.Record_ID));

IEntityRelation customRelation2 = new EntityRelation(RelationType.OneToOne);
customRelation2.AddEntityFieldPair(EntityFieldFactory.Create(MainTable.Record_ID),
EntityFieldFactory.Create(Performance_CacheFieldIndex.Record_ID));

// This should add the relationship twice, with a different alias for the table to join onto.
bucket.Relations.Add(customRelation, "pc", JoinHint.Inner);
bucket.Relations.Add(customRelation2, "pc2", JoinHint.Inner);

// The rest of the filtering...
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(Performance_CacheFieldIndex.Record_Type, ComparisonOperator.Equal, "AUDIT", "pc"));
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(Performance_CacheFieldIndex.Group_ID, ComparisonOperator.Equal, groupSort, "pc"));

bucket.PredicateExpression.Add(PredicateFactory.CompareValue(Performance_CacheFieldIndex.Record_Type, ComparisonOperator.Equal,
                            "SECTION", "pc2"));
.. rest of predicate filters ..

sorter = new SortExpression();
sorter.Add(SortClauseFactory.Create(Performance_CacheFieldIndex.BandingZone, SortOperator.Ascending, "pc"));
sorter.Add(SortClauseFactory.Create(Performance_CacheFieldIndex.BandingZone, SortOperator.Ascending, "pc2"));

But this isn't working The sorting and predicate filters part work fine, but its the joining onto teh same table twice that seems to catch LLBL out. The "aliasEndEntity" that I set when I add the relationships to the bucket should as far as I am aware allow me to do this...

Can anyone help me?

Thanks

Matt

You should alias both ends of the relation. Refer to Frans last post on the follwoing long thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5249

Sorry, it won't work.

Here's my code that adds the relationships:

 // Define a relationship
IEntityRelation customRelation = new EntityRelation(RelationType.OneToOne);
customRelation.AddEntityFieldPair(EntityFieldFactory.Create(MainTable.Parent_ID),
EntityFieldFactory.Create(Performance_CacheFieldIndex.Record_ID));

// This should add the relationship twice, with a different alias for the table to join onto.
bucket.Relations.Add(customRelation, "mt", "pc", JoinHint.Inner);

IEntityRelation customRelation2 = new EntityRelation(RelationType.OneToOne);
customRelation2.AddEntityFieldPair(EntityFieldFactory.Create(MainTable.Record_ID),
EntityFieldFactory.Create(Performance_CacheFieldIndex.Record_ID));

bucket.Relations.Add(customRelation2, "mt", "pc2", JoinHint.Inner);

But LLBLGen produces a SQL statement from this that contains all sorts of off table alises like "LPA_pc", that I didn't define. I'm thinking it must be related to either me getting the start/end entities the wrong way round, or maybe its my def. of the relationships, but I look at this code and it looks right to me

If you need more info, heres a snippet of the SQL produced:

SELECT [[MainTable].[View_ID], [[MainTable].[View_Type], [[MainTable].[Parent_ID], [[MainTable].[Parent_Seq], [[MainTable].[Parent_Title], [[MainTable].[Record_ID], [[MainTable].[Record_Seq], [[MainTable].[Record_Title], [[MainTable].[SysCode], 
[[MainTable].[Site_ID] 
FROM (( [[MainTable] [LPA_p1]  
INNER JOIN [[Performance_Cache] [LPA_d2]  ON  [LPA_p1].[Parent_ID]=[LPA_d2].[Record_ID]) 
INNER JOIN [[MainTable] [LPA_p3]  ON  [LPA_p3].[Record_ID]=[LPA_d2].[Record_ID]) 
....

Please help!!!!

The aliases you see in the query are produced by LLBLGen Pro from the aliases you specified. They're re-created by llblgen pro to be sure the aliases are workable on the db, so aliasses which are too long will also work. It uses an alias map set, so just specify the alias in strings as you want in the code, and the llblgen pro core will transfer these in the lpa aliases in the query.

As you create custom relations, be sure that at least one side is a PK.

Thanks for the info Frans - I understand what you are saying about the generated code creating its own alias names, that makes perfect sense.

But...its still not working, and I think its because of the tables I am using....

MainTable is actually a view, hence no PK's and explains why I am adding the relationships in code (they can't be added at the DB level).

Performance_Cache doesn't have a primary key, because its a demormalised table of sorts.

Is that why i'm getting grief - because there's no PK on either side? Does it shed any light? I still think that the code I have is right

You can define the PK for a view in the designer (so an entity mapped on a view can have a pk), which can help you out. Would that work, could you try if that fixes your problem?

Hi Frans,

Hmmmm, actually, adding a PK onto the view is a tricky one. If you look at the 2 relationships I add:

IEntityRelation customRelation = new EntityRelation(RelationType.OneToOne);
customRelation.AddEntityFieldPair(EntityFieldFactory.Create(MainView.Parent_ID),
EntityFieldFactory.Create(Performance_CacheFieldIndex.Record_ID));

IEntityRelation customRelation2 = new EntityRelation(RelationType.OneToOne);
customRelation2.AddEntityFieldPair(EntityFieldFactory.Create(MainView.Record_ID),
EntityFieldFactory.Create(Performance_CacheFieldIndex.Record_ID));

You'll see I have 2 relationships: View.Parent_ID --> Performance_Cache.Record_ID View.Record_ID --> Performance_Cache.Record_ID

So I use both View.Record_ID and view.Parent_ID. So, I'm unsure what I could actually set as the PK in the view..the only PK that actually works for the view is a composite PK...is it worth setting that?

Also, would it be worth trying to add the relationships in the view in the designer rather than the code?

Thanks

MattWoberts wrote:

Hi Frans,

Hmmmm, actually, adding a PK onto the view is a tricky one. If you look at the 2 relationships I add:

IEntityRelation customRelation = new EntityRelation(RelationType.OneToOne);
customRelation.AddEntityFieldPair(EntityFieldFactory.Create(MainView.Parent_ID),
EntityFieldFactory.Create(Performance_CacheFieldIndex.Record_ID));

IEntityRelation customRelation2 = new EntityRelation(RelationType.OneToOne);
customRelation2.AddEntityFieldPair(EntityFieldFactory.Create(MainView.Record_ID),
EntityFieldFactory.Create(Performance_CacheFieldIndex.Record_ID));

You'll see I have 2 relationships: View.Parent_ID --> Performance_Cache.Record_ID View.Record_ID --> Performance_Cache.Record_ID

The first field you add is the PK field, the second the FK field. You add MainView.Parent_ID first, then PerformanceCachefieldindex.recordid, isn't that the wrong order? Isn't record_id in performance_cache the PK side?

If the pk side is known in the designer, you can design the relation in the designer of course

Its working!!

As you said Frans, I needed to reverse the order that I was defining the entityfieldpair. I was getting the PK/FK thing you mentioned confused with the order that tables appeared in the join in the SQL I posted.

Thank you very much