Add columns at query (QBSelect)

Classic Servoy
1

Hi All,
Sorry, I’m a bit ‘green’ in this part. Maybe one of you can help me.
I want to make a combination of 2 tables and from both tables of them I want a few fields. Like
SELECT o.pickup_date, t.order_number FROM transport_order o INNER JOIN transport t ON o.transport_id = t.transport_id WHERE o.pickup_date …etc.

I figured this out:

    /** @type {QBSelect<db:/test_db/transport_order>} */
    var query = databaseManager.createSelect('db:/test_db/transport_order'); 
    query.where.add(query.columns.pickup_date.between(pickUpDate_from,pickUpDate_to));

    /** @type {QBJoin<db:/test_db/transport>} */
    var transportJN = query.joins.add("db:/test_db/transport", JSRelation.INNER_JOIN);
    transportJN.on.add(query.columns.transport_id.eq(transportJN.columns.transport_id));
   
    query.result.add(transportJN.columns.order_number);
    var testFS = databaseManager.getFoundSet(query);
    application.output(testFS.getRecord(1).order_number);

For a reason or the other, I don’t get the expected result (null)
I try this also without the relation, like: ```
query.result.add(query.columns.pickup_date,“test”);

but this also didn't get the column "test" in the foundset.

What I'm doing wrong?

Thanks in advance!
2

The problem is that you cannot use the function databaseManager.getFoundSet for any arbitrary query. It must be a query which returns the primary key of the main table of the query. Try using databaseManager.getDataSetByQuery instead.

3

Thanks Jeroen,

This works fine, only I don’t reach my final goal. I wanted to get a foundset so I can use excelexport like:

var changedFile = plugins.excelxport.excelExport(testFS, ["order_number"], orgFile, "Test");

How can I fix this?

Thanks again.

4

In that case have a look at databaseManager.createDataSourceByQuery.

5

Thanks for your fast reply Jeroen.

This didn’t help.
The databaseManager.createDataSourceByQuery returns a String, while plugins.excelxport.excelExport needs a Object as first parameter.

Other solutions availible?

6

e.valstar:
Thanks for your fast reply Jeroen.

This didn’t help.
The databaseManager.createDataSourceByQuery returns a String, while plugins.excelxport.excelExport needs a Object as first parameter.

Other solutions availible?

Did you have a look at the documentation of this function? The string it returns is a datasource uri which you can use in databaseManager.getFoundSet(uri) to get the foundset object.

7

Thanks Jeroen,

This worked out!
And, now I readed the documentation of this function, and put it in the solution.

Thanks for your time.