How to model multiple many-to-one relationships using query builder?

2 posts, 0 answers
  1. Drew
    Drew avatar
    1 posts
    Member since:
    Nov 2018

    Posted 07 Dec 2018 Link to this post

    We have a table that has 3 independent keys which point to a single second table; each of these keys is independent and represents a separate relationship. An example might be a single order which can relate to multiple salespeople.

    Something like this:

    Table 1

    • Table 1 PK
    • FK1 => Table 2 PK
    • FK2 => Table 2 PK
    • FK3 => Table 2 PK
    • Column A
    • Column B

    Table 2

    • Table 2 PK
    • Column C
    • Column D

    I would expect to be able to connect the two tables, automatically adding multiple duplicate columns from the second table in my result set (perhaps with a prefix or suffix derived from the foreign key). So we might see something like this:

    Joined table (expected)

    • Table 1 PK
    • Column A
    • Column B
    • FK1 Table 2 PK
    • FK1 Table 2 Column C
    • FK1 Table 2 Column D
    • FK2 Table 2 PK
    • FK2 Table 2 Column C
    • FK2 Table 2 Column D
    • FK3 Table 2 PK
    • FK3 Table 2 Column C
    • FK3 Table 2 Column D

    When I try to include both tables in the query builder, the second table only appears once in the resulting columns. Additionally, no matter how I set up the relationships, the query builder attempts to group all 3 relationships together (join on FK1 = T2 PK AND FK2 = T2 PK AND FK3 = T2 PK). This is not reflective of the data model. We also only see one set of table 2's columns in the result, something like this:

    Joined table (actual)

    •  Table 1 PK
    • Column A
    • Column B
    • Table 2 PK
    • Table 2 Column C
    • Table 2 Column D

    Is there any way to do what I'm trying to do using the query builder? Or will I need to write manual SQL for this? Thanks!

  2. Silviya
    Admin
    Silviya avatar
    335 posts

    Posted 12 Dec 2018 Link to this post

    Hello,

    I noticed that you've opened a support ticket on the same topic, so I'll post my answer here as well:

    "The Query Builder will automatically detect any foreign key relationships between the added tables. However, you are right, if a certain table has more than one foreign key to certain other table, only the first foreign key would be displayed in query designer's Relationships Pane. 
    I have tested with the AdventureWorks Database. The Sales.SalesOrderHeader table has two foreign key to Person.Address table (to BillToAddressID and ShipToAdressID fields), but there is only one relationship to this table - https://www.screencast.com/t/XoYAejsz4D (the first one).

    Any additional relationships should be added manually (you will need to write them by hand inside the select statement). In this case you could use Manual mode to specify an join between ShipToAdressID  and AddressID fields - https://www.screencast.com/t/reMyFA8I.

    Note that we consider the requirement as a valid feature request. If you wish, you could log this as new feature request in our feedback portal for voting from other users. The new features implementation in Telerik Reporting is prioritized based on several factors, one of which is user voting."

    Regards,
    Silviya
    Progress Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top