Column Alias with QueryBuilder

Classic Servoy
1

I have the following SQL statement: select strasse as hptstrasse from adressen

I want to create this select with the QueryBuilder.

How do I have to define the result so that the column name will be ‘hptstrasse’ instead of ‘strasse’.
With qry.result.add(qry.columns.strasse) the column name will be ‘strasse’.

I need this because there are joins to other tables with the same column name.

2

Hi Gabriel,

had a quick look as I haven’t used the querybuilder so far…

seems to me that you can use query.result().add(query.getColumn(‘primaryTable’, ‘strasse’)).add(query.getColumn(‘join1Table’, ‘strasse’))

This way servoy will automatically take care of the correct alias.
The whole usecase of the querybuilder is to create your query as a Servoy object, that way Servoy can use Hibernate to translate it to any backend database.
If you are ‘making up’ column names, Servoy will be all lost as these columns don’t exist in the database.

Hope this helps

3

Hi Marc

On this way you will get a DataSet with two columns called ‘strasse’ with the correct values in it.
But if I want to create a FoundSet based on this DataSet Servoy shows a error because of the duplicated column names.

4

Gabriel,

The QueryBuilder currently has no support for column aliases, you can add a feature request for, it could be useful.

However, in your case, I don’t think you need this.
The QueryBuilder looks at the column objects, not just at the column names, so when you use a column tab1.col it knows it is from table tab1, even if there is also a tab2 in the query with column names ‘col’

If you want to do a loadRecords(qb) on a foundset, just set the query result to the column you select:

query.result.add(query.joins.myjoin.columns.coll)

Rob

5

GabrielWyss:
On this way you will get a DataSet with two columns called ‘strasse’ with the correct values in it.
But if I want to create a FoundSet based on this DataSet Servoy shows a error because of the duplicated column names.

I would recommend not to load a dataset on a foundset in order to show the records, but do that immediately, like:
foundset.loadRecords(query, [args]);

This way Servoy knows the underlying query of your data which is a benefit on the long term, especially when working with larger datasets.
Larger datasets will force Servoy to use tempTables which is slower.

6

There are two reasons, why I need unique field names.

  1. I use the FoundSet to transfer it to Jasper Report.
  2. I use the DataSet and/or the FoundSet with the IT2BE Word PlugIn.

In both cases, how can I tell there wich of the duplicate field I want to use?
Therefore I think unique field names are required.

I got a tip, that it is possible to rename the column name in the DataSet with ds.setValue(-1, 3, “newName”).
At the moment I will work on this way but I will create a feature request.

7

I create now a feature request

https://support.servoy.com/browse/SVY-3296

The QueryBuilder is a very good tool, an I advise everyone to have a look on it.
If you make a lot of own queries, with the QueryBuilder you are realy independet of the used database.

Please vote the feature request.