Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Joining a table onto itself
 

Pages: 1
LLBLGen Pro Runtime Framework
Joining a table onto itself
Page:1/1 

  Print all messages in this thread  
Poster Message
MattWoberts
User



Location:
Lancaster, UK
Joined on:
08-Apr-2004 10:24:31
Posted:
497 posts
# Posted on: 15-Sep-2005 17:43:05.  
Hi guys,

I need to re-create this SQL query in LLBL:

Code:

SELECT s.Code, s.DisplayString, ss.DisplayString
FROM System_String s JOIN System_String ss ON s.Code = ss.Code
WHERE s.Language_code = 'en' and ss.Language_code = 'fr'
AND ss.DisplayString = '';


I am joining the "System_String" table onto itself in this code, in order to give me the english string (DisplayString) and the code for any french strings that are empty...

My table looks like this:

System_String (Code, Language_Code, DisplayString)

Can anyone point me in the right direction here - do I need to define a relationship in code to do this??

Thanks!
Matt  Top
Paul.Lewis
User



Location:
Columbus Ohio USA
Joined on:
22-Aug-2005 21:06:58
Posted:
147 posts
# Posted on: 16-Sep-2005 04:56:38.  
Matt,

I grabbed this quote from another Forum thread.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2709

Quote:
M:n relations with self are currently not supported (in 1.0.2004.1). If you want I can switch it on in 1.0.2004.2. It was disabled because the runtime engine couldn't alias in the past (before 1.0.2004.1, which wasn't a gui update).

Though as with all relations with self, as you're working on the same table, queries could be more optimial if you simply specify the filters yourself.


If possible, rework your query to not use self joins. Another alternative is to place the self join in a stored procedure.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38020 posts
# Posted on: 16-Sep-2005 10:17:22.  
Do this self join in code by creating an EntityRelation object manually and by specifying the s and ss aliasses.

I tried to make it a single table query, but you then don't get the french data out of it as it's a different set (though from the same table).
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
tmpcl
User



Location:

Joined on:
21-Sep-2006 00:06:15
Posted:
20 posts
# Posted on: 20-Sep-2007 21:46:19.  
Otis wrote:
Do this self join in code by creating an EntityRelation object manually and by specifying the s and ss aliasses.


How do you specify which alias to use in the where clause?

Code:

DepartmentEmployeeCollection departmentEmployees = new DepartmentEmployeeCollection();

EntityRelation selfRelation = new EntityRelation(DepartmentEmployeeFields.EmployeeId, DepartmentEmployeeFields.EmployeeId,RelationType.OneToMany);

RelationCollection relation = new RelationCollection();
relation.Add(selfRelation, "a");
relation.Add(EmployeeEntity.Relations.DepartmentEmployeeEntityUsingEmployeeId);

PredicateExpression filter = new PredicateExpression();
***filter.Add(DepartmentEmployeeFields.DepartmentId == CurrentDepartment);
filter.Add(EmployeeFields.Active == 1);


Adding the filter I flagged with the *** causes the where to contain DepartmentEmployee.DeparmentId = 25. What I really need is to use the "a" alias in the where like a.DepartmentId = 25

The above code returns something like this:

Code:

SELECT DISTINCT <fields...>
FROM         DepartmentEmployee LPA_a1
INNER JOIN     DepartmentEmployee ON LPA_a1.EmployeeID=DepartmentEmployee.EmployeeID
INNER JOIN     Employee ON Employee.EmployeeID=DepartmentEmployee.EmployeeID
WHERE         DepartmentEmployee.DepartmentID = 25
AND         Employee.Active = 1


I need it to look like this or some code that will give me this:

Code:

SELECT DISTINCT <fields...>
FROM         DepartmentEmployee LPA_a1
INNER JOIN     DepartmentEmployee ON LPA_a1.EmployeeID=DepartmentEmployee.EmployeeID
INNER JOIN     Employee ON Employee.EmployeeID=DepartmentEmployee.EmployeeID
WHERE         LPA_a1.DepartmentID = 25
AND         Employee.Active = 1



  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38020 posts
# Posted on: 21-Sep-2007 12:20:19.  
Use .SetObjectAlias() on the field in the predicate. So:
filter.Add(DepartmentEmployeeFields.DepartmentId.SetObjectAlias("a") == CurrentDepartment);
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
tmpcl
User



Location:

Joined on:
21-Sep-2006 00:06:15
Posted:
20 posts
# Posted on: 21-Sep-2007 18:12:37.  
Otis wrote:
Use .SetObjectAlias() on the field in the predicate. So:
filter.Add(DepartmentEmployeeFields.DepartmentId.SetObjectAlias("a") == CurrentDepartment);


Great! Thanks for the info.


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.