How to add joins to the query on adding a table from the tree?
Last modified:
Question:
Is there any javascript event I can use to capture the event when a user drag/drops a table from the treeview into the design section?
Basically I'm looking to capture this event and then go to my server side code (once more then 1 table is dropped into the design) to call the database and work out the relationships between the tables and if exists populate any middle men tables.
Our database has no relationships inside SQL itself, there is a table that currently holds which table is linked to which one so I'll be able to work it out but just need an event to tie into. Any suggestions?
Answer:
At first, you can modify the metadata container which holds the information extracted from the database schema.
Active Query Builder has the full set of methods to modify the content of metadata container: add, remove, change properties of objects, fields and foreign keys. So, even your database doesn't contain this information, you can complement it there.
You can save your metadata to the XML file and add the references between tables according to your schema, then use this XML instead of the live database connection (this will reduce the server load as well). If this method is not suitable due to frequent schema changes, you can add information about foreign keys programmatically.
If this information exists in the metadata container, the users will also see buttons next to the linked fields in datasources on the Design Pane which will help your end-users to build queries faster.
Also, if you set the QueryBuilder.BehaviourOptions.AddLinkedObjects and DeleteUnusedObjects properties to true, the component will add intermediate objects (and remove those which are not used anywhere else) automatically.
Second, you can act as you described: the QueryBuilder.DataSourceAdded event allows you to add joins after a table is added to the query by the user. You can find code samples in these articles: