How to analyse what join statements to take to go from starting table to end table.
I have company database with many many foreign connections on each table in the db schema, the number of tables is more than 100 tables… Foreign connection are spanning over more than 7 tables many times…
Is there a way in datagrip to enter starting table and end table so that datagrip would analyse if connection is possible and over which foreign keys this connection is possible? I need this to get a list of tables with appropriate foreign keys to create appropriate join statement for select query.
Please sign in to leave a comment.
Hi Jar Fmf
If I understood your scenario correctly, you are writing join / multijoin queries of tables having lots of foreign keys. As you don't know all the table relations, you want the code completion to suggest possible valid relations of table columns that can used in ON conditions. Is that what you're trying to achieve? Please also clarify the relation type of your tables (pk-fk, unique etc)
Hi Aleksandr Molchanov
For example if I have 3 tables… (user → contact → messages)…
If I want to query all user contacts there is no problem datagrip hintings are helping me 100% with that regard,
but if I want to get all user messages then there is a problem… I would like some tool in which i would select starting table (user) and ending table (messages) and this tool would perform breath first search over starting table connections to find fastest route over connected tables to the end table and display over which keys or connection the join must be written to get all user messages for example.
This tool which would perform breath first search would really help somebody write complex join queries when you don't know internal database schema but only start end end table where results should be extracted… For example 300+ db tables which are interconected over many foreign keys.
We're currently discussing what can be done to achieve this task.
I'll get back to you shortly on this.
Thank you for being patient Jar Fmf
Our code completion doesn't have this functionality. Therefore, you may take advantage of our diagram view to achieve this task. To do this, you need to highlight two tables with Shift and select Analyze Graph | Focus on Paths between Two Nodes from the context menu.
However, in some cases, when having a large diagram with many tables in it, it could be causing performance issues we have reported here. Just so you know, it's being worked on. To stay updated on the implementation progress, I'd encourage you to follow this issue.