HomeActive Query BuilderMultiple Relations in one table

Multiple Relations in one table


Lets say I have a table called "Projects" that holds information about a project, and the people assigned to it. And I've also got a table called "Users" which defines people and their names.

The Projects table might have three fields in it
"managerid"
"leaderid" and
"delegateid"

all which are foreign keys for the User's table's primary key, "userid".

So normally I'd build a Select statement like this:

SELECT p.Title, p.EndDate
m.Name as ManagerName,
l.Name as LeaderName,
d.Name as DelegateName
FROM Projects p
INNER JOIN Users m on m.userid = p.managerid
INNER JOIN Users l on l.userid = p.leaderid
INNER JOIN Users d on d.userid = p.delegateid


How do I setup the metadata in AQB to allow this to be done visually? I've tried setting up multiple fields in the relations tag of the Users table:


<pre>
-------------------------------------------------

<relations>
<relation>
<key_fields><field_name><item name="USERID" case_sens="1"/></field_name></key_fields>
<child_database name="MyDB" case_sens="1"/>
<child_schema name="dbo" case_sens="1"/>
<child_name name="PROJECTS" case_sens="1"/>
<child_fields>
<field_name><item name="MANAGERID" case_sens="1"/></field_name>
<field_name><item name="LEADERID" case_sens="1"/></field_name>
<field_name><item name="DELEGATEID" case_sens="1"/></field_name>
</child_fields>
</relation>
</relations>

-------------------------------------------------
</pre>

But that makes no joins appear when I drag the tables.

I've also tried setting up multiple <relation> tags, one for each field. But that also doesn't work - I get multiple joins on a single table.

What I'd expect is either a popup window to ask what field I intended to join, or when I drag multiple copies of the same table into the query, it would join on a different field each time.
Gordon | email
July 10 2008, 09:32 AM
Dear Gordon,

You wrote:
> I've also tried setting up multiple "relation" tags, one for each field.
> But that also doesn't work - I get multiple joins on a single table.

You right, this is the way a component works. It couldn't know, what join you're going to set between these tables, so he adds all three joins automatically; the user have to remove unwanted joins at the diagram.

My suggestion is to add the Users table to the Metadata Container three times, with different "alternate names". These names will be visible during the query building time, but you will get correct SQL in result. See the Alternate Names demos to find example of the XML file with alternate names.

Unfortunately, there is one problem that does not allow you to act in this way, and this problem is that foreign keys are stored at the wrong side. We were thinking about changing of this situation long time ago, and now it seems the right time to change it. Again, we will need some time to make it happen.

Sincerely
Sergey Smagin
Sergey Smagin | email
16 hours, 10 mins since original post
I just tried to do this, and there's a problem.

When I add the users table three times, with different alt names, and enable "UseAltNames" in the control... I can't drag them onto the diagram pane.

No matter which user table I drag, the one that appears in the control is the first one in the metadata.
Gordon | email
5 days, 2 hours since original post
And when I put different names for the table, e.g. "User1" "User2" and "User3".... if I set UseAltNames to true, the generated SQL uses the alternate names both as aliases, and as the actual table names. E.g. if the "User1" table had an alt name of "Leader User", the SQL generated is

SELECT *
FROM Projects p
Inner Join [Leader User] [Leader User] On ....
Inner Join [Manager User] [Manager User] On ...


So is there no way to get the alt names to show in the table selection list, but not be treated as literal sql table names in the SQL?
Gordon | email
5 days, 3 hours since original post
Nevermind I found the FormattedServerSQL property. And I guess I can tack a unique numeric onto the end of each table and trim it out of the SQL statement on the other end.
Gordon | email
5 days, 3 hours since original post
Dear Gordon,

You right, you should use the ServerSQL or FormattedServerSQL (if your server takes care about formatting ;) properties.

I believe that we can fix the issue with identical table names too.

Sergey
Sergey Smagin | email
5 days, 19 hours since original post
Dear Gordon,

The bug with dragging tables with identical table names, but different alternate names is fixed now. You may download the updated build now.

Another problem is that relations are stored in XML file at the wrong side (at the key table), so you can't create three joins to the tables with the same real names and different alternate names, because real names are used for linking. Moving relations to the right side (to the child table) causes wrong drawing of "1"-"infinite" signs. The new version allows you to place relation to any side:

We've made the new attribute for "relation" tag: <relation type="fk">. Existence of this attribute will cause inversion of the "1"-"infinite" signs drawing, so everything will seem correct from the user's point of view, and you'll be able to place one relation for each child table as you need.

This way, you won't have to add numeric suffixes and remove them back on query execution.

Hope this helps you to cope with your task.

PS: Implementation of the other suggestions shouldn't be expected too soon. We're planning to include them in the next major release that hopefully will be available in 4-6 months. Sorry for the inconveniences this may cause you.

Sincerely
Sergey Smagin
Sergey Smagin | email
3 weeks since original post
This topic is closed