Brain block with query builder!

Classic Servoy
1

Hi,

I am trying to rewrite an SQL query using query builder (in order to take advantage of design time checking, to make the code database agnostic and because I think that it makes my code far easier to maintain) but I cannot get my head around how to structure the query. I am trying to return a dataset that consists of three fields from three related tables. Using the example_data database an equivalent to my query would be:

query = "SELECT orders.orderid, \
         customers.companyname,  \
         employees.lastname  \
         FROM    orders \
         JOIN    customers \
         ON      customers.customerid   =  orders.customerid \
         JOIN    employees \
         ON      employees.employeeid   =  orders.orderid "
         result = databaseManager.getDataSetByQuery("example_data",query,null,100);

This returns a dataset that has three fields (one from each table) showing the order ID, customer name and employee surname.

I have tried to replicate this using the query builder syntax but cannot find a way to add just the columns I want to the result set. What I have so far is:

    var args = null;
    /** @type {QBSelect<db:/example_data/orders>} */
    var query = databaseManager.createSelect('db:/example_data/orders');
	
    /** @type {JSDataSet} */
    var result = null;

    query.result.add(query.columns.orderid);

    result = databaseManager.getDataSetByQuery(query,100);

This gives a dataset that contains the orderid but nothing more. I ave researched the wiki and the forums but can find no examples of adding a column from a related table to the result set but lots that show how to filter the results using related table columns in the ‘where’ clause. I have tried to replicate this type of syntax in my query but Servoy reports everything I try as an error in the designer (I don’t even get to the stage of running it!). The examples I have tried to follow are from the QBCondition page on the wiki:
https://wiki.servoy.com/display/public/DOCS/QBWhereCondition

as well as the release notes for 6.1 which describes the query builder :
https://wiki.servoy.com/display/SERV61/Query+builder

The first link would result in something similar to:

query.result.add(query.joins.orders_to_employees.joins.orders_to_employees.columns.lastname

but the developer doesn’t like this syntax and errors on the second ‘joins’. I have also tried following the syntax in the second link:

query.result.add(query.joins.orders_to_employees.columns.lastname

but again the developer doesn’t like this and errors on the ‘columns’ element.

Can anyone point me in the right direction please?

Thanks

Steve

2

Hi Steve,

first try to add the joins, using something like:

var joinEmp = query.joins.aadd('db:/example_data/employees', JSRelation.INNER_JOIN);
joinEmp.on.add(…...)

Then you can get a QBColumn as:

var columnEmp = joinEmp.getColumn('lastname');

This QBColumn you can add to your QBResult as:

query.result.add(columnEmp)

You should do a similar thing for the customers join

Hope this helps

3

Hi Steve,

Assuming you example query as this;

var query= ' SELECT orders.orderid,customers.companyname,employees.lastname\
                FROM orders\
                JOIN customers \
                ON customers.customerid = orders.customerid \
                JOIN employees \
                ON employees.employeeid = orders.employeeid;'

	var result = databaseManager.getDataSetByQuery("example_data",query,null,-1);

	application.output('Normal query : ' + result.getAsHTML());                           // 830 rows

This is the Query Builder Statements :

    // Create a select object
	/**@type {QBSelect<db:/example_data/orders>}*/
	var q = databaseManager.createSelect('db:/example_data/orders');	

	// Add joins from orders to customers
	/**@type {QBJoin<db:/example_data/customers>}*/
	var joinCust = q.joins.add('db:/example_data/customers');
	joinCust.on.add(q.columns.customerid.eq(joinCust.columns.customerid));
	var custColmn = joinCust.getColumn('companyname');
	
	// Add joins from orders to employees
	/**@type {QBJoin<db:/example_data/employees>}*/
	var joinEmp = q.joins.add('db:/example_data/employees');
	joinEmp.on.add(q.columns.employeeid.eq(joinEmp.columns.employeeid));
	var empColmn = joinEmp.getColumn('lastname');
	
	// Get the results
	q.result.add(q.columns.orderid);
	q.result.add(custColmn);
	q.result.add(empColmn);
	
	var result = databaseManager.getDataSetByQuery(q,-1);
	
	application.output('QB select : ' + result.getMaxRowIndex());            // 830 rows

Hope this will help you getting the correct dataset.

Thanks,

4

Thanks for the replies and assistance.

Using the help from both I have refined my code and got the following fully working:

    /** @type {QBSelect<db:/example_data/orders>} */
    var query = databaseManager.createSelect('db:/example_data/orders');
    /** @type {QBJoin<db:/example_data/customers>} */
    var customer = query.joins.orders_to_customers; 
    /** @type {QBJoin<db:/example_data/employees>} */
    var employees = query.joins.orders_to_employees; 
	
    /** @type {JSDataSet} */
    var result = null;

    query.result.add(query.columns.orderid);
    query.result.add(customer.columns.companyname);
    query.result.add(employees.columns.lastname);

    result = databaseManager.getDataSetByQuery(query,100);

This takes advantage of two predefined relationships within the solution (orders_to_customers and orders_to_employees) and doesn’t require separate variables to hold the columns required for the result sets.

It does seem that the documentation on the wiki is both lacking in some details and possibly incorrect as the syntax shown in the where of this link does not work:
https://wiki.servoy.com/display/public/DOCS/QBWhereCondition#QBWhereCondition-parent

Thanks Marc and Sovan for your help

Steve