Automatic querying through selection of registered data sets
Methods and systems described herein are directed to creating customized queries on data sets via selection of search elements based on meta-data from data set registrations. In some implementations, an automatic query system can register database elements with associated meta-data by receiving programming data objects, corresponding to database elements, with meta-data and adding the programming objects as selectable values for a user interface (UI). Each data object can define one or more elements for a table and/or column(s) within a table in a database. The automatic query system can provide the UI to a user to receive table and column selections. The automatic query system can select a root table to join the selected tables into, and generate a join query for the root table by inserting search string snippets, corresponding to selected tables and columns, into a query template.
1. A method for automatically building and executing a join query, the method comprising:
providing a user interface (UI) with a flow for table selections and column selections, wherein the UI is based on registered programming data objects corresponding to one or more database elements;
wherein each of the programming data objects defines the one or more database elements, each for a table or column within the table, and
wherein each of the one or more database elements has corresponding meta-data defining what is displayed in the UI for the table selections or the column selections;
receiving, from the UI, two or more table selections and one or more column selections for the join query;
computing a number of connections between each of the selected tables, wherein the number of connections is a number of columns the selected tables have in common;
selecting a root table for the join query as the table with a highest number of connections;
resolving code snippets, from the programming data objects corresponding to the selected two or more tables and the selected one or more columns, into search string snippets;
generating the join query by:
inserting, into a root table slot in a query template, the search string snippet for the root table;
inserting, into a join table slot in the query template, one or more search string snippets for the one or more selected tables that are not the root table; and
inserting, into a column select slot in the query template, the one or more search string snippets for the one or more selected columns; and
executing the join query and providing results.
2. The method of claim 1 , further comprising:
adding the programming data objects as selectable elements for the UI, wherein tables are added as selectable elements, and
wherein the UI is configured to show columns, as selectable elements, from corresponding selected tables.
3. The method of claim 1 ,
wherein the UI flow further includes a portion for value filter selections; and
wherein the method further comprises:
receiving, from the UI, value filter selections for the join query; and
generating the value filter selections, into search string snippets for the value filter selections.
4. The method of claim 3 , wherein generating the join query further comprises:
inserting, into a value filter select slot in the query template, the search string snippets for the value filter selections.
5. The method of claim 1 , wherein selecting the root table further comprises:
determining column labels that the two or more selected tables have in common.
6. The method of claim 1 , wherein the UI is configured to:
filter a list of selectable tables to present tables that can be joined with previously selected tables.
7. The method of claim 6 , wherein filtering the list of selectable tables further comprises:
removing one or more tables from the list of selectable tables when the one or more tables has no connections with the previously selected tables.
8. A computing system for automatically building and executing a join query, the computing system comprising:
one or more processors; and
one or more memories storing instructions that, when executed by the one or more processor, cause the computing system to perform a process comprising:
providing a user interface (UI) with a flow for table selections and column selections, wherein the UI is based on registered programming data objects corresponding to one or more database elements;
wherein each of the programming data objects defines the one or more database elements, each for a table or column within the table, and
wherein each of the one or more database elements has corresponding meta-data defining what is displayed in the UI for the table selections or the column selections;
receiving, from the UI, two or more table selections and one or more column selections for the join query;
computing a number of column connections between each of the selected tables;
selecting a root table for the join query as the table with a highest number of column connections;
resolving code snippets, from the programming data objects corresponding to the selected two or more tables and the selected one or more columns, into search string snippets;
generating the join query by:
inserting the search string snippets into corresponding slots in a query template; and
executing the join query and providing results.
9. The computing system of claim 8 , wherein the inserting the search string snippets into corresponding slots comprises:
inserting, into a root table slot in the query template, the search string snippet for the root table;
inserting, into a join table slot in the query template, one or more search string snippets for the one or more selected tables that are not the root table;
inserting, into a column select slot in the query template, the one or more search string snippets for the one or more selected columns; and
inserting, into a value filter select slot in the query template, one or more search string snippets for one or more selected value filters.
10. The computing system of claim 8 , wherein the process further comprises:
adding the programming data objects as selectable elements for the UI, wherein tables are added as selectable elements, and
wherein the UI is configured to show columns, as selectable elements, from corresponding selected tables.
11. The computing system of claim 8 ,
wherein the UI flow further includes a portion for value filter selections; and
wherein the process further comprises:
receiving, from the UI, value filter selections for the join query; and
generating the value filter selections, into search string snippets for the value filter selections.
12. The computing system of claim 8 , wherein the process of selecting the root table further comprises:
determining column labels that the two or more selected tables have in common.
13. The computing system of claim 8 , wherein the UI is configured to:
filter a list of selectable tables to present tables that can be joined with previously selected tables.
14. The computing system of claim 13 , wherein the filtering the list of selectable tables comprises:
removing one or more tables from the list of selectable tables when the one or more tables has no common columns with the previously selected tables.
15. A non-transitory computer-readable medium storing instructions that, when executed by a computing system, cause the computing system to perform operations for automatically building and executing a query, the operations comprising:
providing a user interface (UI) with a flow for table selections and column selections, wherein the UI is based on registered programming data objects corresponding to one or more database elements;
wherein each of the programming data objects defines the one or more database elements, each for a table or column within the table, and
wherein each of the one or more database elements has corresponding meta-data defining what is displayed in the UI for the table selections or the column selections;
receiving, from the UI, one or more table selections and one or more column selections for the query;
resolving code snippets, from the programming data objects corresponding to the selected one or more tables and the selected one or more columns, into search string snippets;
generating the query by:
inserting the search string snippets into corresponding slots in a query template; and
executing the query and providing results.
16. The non-transitory computer-readable medium of claim 15 , wherein the inserting the search string snippets into corresponding slots comprises:
inserting, into a root table slot in the query template, the search string snippet for a determined root table;
inserting, into a join table slot in the query template, one or more search string snippets for the one or more selected tables that are not the determined root table; and
inserting, into a column select slot in the query template, the one or more search string snippets for the one or more selected columns.
17. The non-transitory computer-readable medium of claim 15 , wherein the operations further comprise:
adding the programming data objects as selectable elements for the UI, wherein tables are added as selectable elements, and
wherein the UI is configured to show columns, as selectable elements, from corresponding selected tables.
18. The non-transitory computer-readable medium of claim 15 , storage wherein the UI flow further includes a portion for value filter selections; and
wherein the operations further comprise:
receiving, from the UI, value filter selections for the query; and
generating the value filter selections, into search string snippets for the value filter selections.
19. The non-transitory computer-readable medium of claim 15 , wherein the operations further comprise:
computing a number of connections between each of the selected tables, wherein the number of connections is a number of columns the selected tables have in common; and
selecting a root table for the query as the table with a highest number of connections.
20. The non-transitory computer-readable medium of claim 15 , wherein the UI is configured to:
filter a list of selectable tables to present tables that can be joined with previously selected tables.