Query Builder assists you in browsing the database and creating basic queries. You can easily select data from multiple tables/databases, and Query Builder will generate the SQL for you. It relies on FOREIGN KEY relationships in the database to automate joins between multiple tables. Query Builder is visible by default when creating a new report. For older reports, simply click 'Show Query Builder.'
Query Builder is designed to speed up report creation by automating the most tedious parts of query building: remembering tables, columns, and relations between the columns.
Query Builder consists of four views of the database. The first view lists all databases visible to the user. The second view lists tables from the selected database, and the third view lists columns from the selected table. The final view is for the user query columns.
Select the columns you wish to include in the query. You can freely choose columns from different tables and databases.
Once the columns are selected, you can re-arrange them and choose aggregate functions for the query. After completing the column selection, you can generate the SQL command by clicking either of the 'Generate SQL' buttons. The 'Generate SQL into the selection' button will place the generated SQL code in the editor's current window at the insertion caret's position. The 'Generate SQL into a new tab' button will create a new tab and place the code there.
Generated SQL:
select c.name as 'Name',
p.name as 'Name',
sum(pr.amount) as 'Amount'
from acre.Company c, acre.Product p, acre.Production pr
where c.id = pr.company_id and p.id = pr.product_id
group by c.name, p.name;
Please be aware that if your database has not defined foreign key constraints, no joins will be generated (where clause is 1=1). You have two options: either create foreign keys for tables, or if you cannot modify the database, define the relations in myDBR using the 'mydbr_key_column_usage' table.