Union with QBSelect

[nevenam]

Hi,
Do you plan to add the union operator to QBSelect? Is there a way to combine the result sets of select statements at the moment, besides using raw SQL statements?

[rgansevles]

Nevenam,

Servoy bases its sql generation on Hibernate which does not support unions.

You could try query with OR-ing 2 subselects to get the same result.

Rob

[huber]

Hi

Is this statement of QB not supporting the UNION operator still valid as per today? A small example with OR-ing would be helpful.

Nevenam,

Servoy bases its sql generation on Hibernate which does not support unions.

You could try query with OR-ing 2 subselects to get the same result.

Rob

Regards, Robert

[rafig]

Has anyone managed to get 'UNION' working with QB using 'or' option as stated here??
If so, please can you share your code?
If not, PLEASE can someone from Servoy share an example of how to do this?? (as was requested 2 years ago...)
Unfortunately, the online documentation is once again sorely lacking in certain useful examples, this being a perfect example...
I urgently need to be able to do this, as I'm having to use raw SQL instead, but that is not the 'best practice' way of doing things.
Have a good weekend
Thanks
Rafi

[ROCLASI]

I urgently need to be able to do this, as I'm having to use raw SQL instead, but that is not the 'best practice' way of doing things.i

I have to contest that using QB is 'best practice'. I know a lot of Servoy developers that don't use it, for good reasons.

QB has to cater to the lowest denominator as it tries to support all RDBMS vendors, which means it uses standard SQL with some support for dialect changes (TOP vs LIMIT, etc.).
And as we see in this thread it doesn't even do that as it doesn't support UNION/UNION ALL (which should have been named UNION DISTINCT/UNION, but that is another discussion altogether). But there is lot more not supported by QB that is in the SQL standard and supported by most if not all RDBMS vendors. What QB supports is in fact really really basic stuff.

Also SQL is code. I don't see the benefit of abstracting really readable code into something that is far less intelligible. Sure you get your warning markers when you misspell something. But that doesn't trump the benefits of using pure SQL. SQL is way easier to debug as you can simply drop it in any SQL editor and test it.

But QB makes my SQL more portable!
You have question yourself how many different RDBMS versions you need to support with your solution. It usually is not more then 2, a lot of the time it's just 1.

But what if we need to migrate to another RDBMS?
Consider this, using QB means you use standard SQL anyway. Odds are you can switch RDBMSs without changing any or very little SQL code if you would have used pure SQL.
And since you have full control of your code when using pure SQL you can also optimise your queries, because not all SQL is handled equally.

Once you learn the powerful features of the rich (standard) SQL language you will see that QB can't touch it. Common Table Expressions, Window Functions, UNION/UNION ALL, I use them all the time. And they all work well between the RDBMS vendors.
Again, SQL is code. And it's pretty standardised. As long as you stay away from the vendor specific syntaxes (they usually support the standard syntax too) your code can be highly portable.

Rafi, you say you urgently need to support UNIONS...I suggest you use pure SQL.


Just my (strong) opinion.
Hope this helps.

[rafig]

Hi Robert,

Rafi, you say you urgently need to support UNIONS...I suggest you use pure SQL.
Hope this helps.

Thanks for your reply.
I am completely with you and agree, and I am using SQL with 'UNION ALL', not QB (because I have no idea how to do it in QB due to lack of examples )
Apart from being able to move a solution from one back-end to another without having to change any SQL in Servoy code, QB understands data changes/broadcasting, so I think sometimes gives more up to date results based on any recent changes in data...
Let's see if someone can actually provide an example of doing UNION ALL using QB, it might help me and others...

[ROCLASI]

...QB understands data changes/broadcasting, so I think sometimes gives more up to date results based on any recent changes in data...

Why does QB need to know about data changes? It's a query language.
You load a FoundSet with it's primary key(s) and Servoy handles the rest. So it's because of the FoundSet that Servoy knows what caches to invalidate.
And if you use QB for DataSets, there is no data-broadcast and/or cache for DataSets.

[Ruben79]

We have seen major performance increases when using QBSelect, due the fact that the query from a QBSelect does not get wrapped as a subquery by Servoy anymore: SELECT pk FROM table WHERE pk IN (<sql>); Instead it is executed directly.
Especially with big tables and/or complex WHERE clauses this very noticable.

In one occasion where I needed a UNION of completely unrelated tables, I just load 2 datasets and merge them in code (not advisable with really big datasets of course).

Code: Select all

var ds1 = databaseManager.getDataSetByQuery(query1, -1)
var ds2 = databaseManager.getDataSetByQuery(query2, -1)

var count2 = ds2.getMaxRowIndex()

for (var i = 1; i <= count2 ; i++) {
   ds1.addRow(ds2.getRowAsArray(i))
}


[rafig]

Hi Ruben,
thanks for your reply...

We have seen major performance increases when using QBSelect, due the fact that the query from a QBSelect does not get wrapped as a subquery by Servoy anymore: SELECT pk FROM table WHERE pk IN (<sql>); Instead it is executed directly.
Especially with big tables and/or complex WHERE clauses this very noticable.

That's great to hear, another good reason for using QB...

In one occasion where I needed a UNION of completely unrelated tables, I just load 2 datasets and merge them in code (not advisable with really big datasets of course).

Thanks for the idea & code, I may well try that...
But what do you consider 'really big datasets'?? (my UNIONs return about 6k rows in total, might be more or less in production...)

Thanks
Rafi

[Ruben79]

Hi Rafi,

Nothing can beat the performance of the union being done in the sql itself and fetched in one dataset, that is clear.
Also my example of the query being wrapped as a subquery only applies when loading foundsets. It does not happen for datasets as far as I know.

But if you really want to use QBSelect for various reasons this is just an option.

I consider 6K records being not that much, but it depends a bit.. In general it just takes the time to execute both queries, fetch the dataset and then some cpu time to merge the dataset. Also depending how big the rows are, etc.
Is this smart client or NG client? NG client is a lot faster with loading datasets because less overhead and no transport needed from server to client.

It's just a matter of trying it out what works best in your case. QBSelect can be a nice tool and we use it by default especially with loading foundsets, but plain SQL sometimes is the best (or only) option.

[rafig]

Hi Ruben,
thanks for your reply.
For the moment I'm going to stick with the SQL/UNION as it works (& it's NG client)

(Still looking forward to seeing an example of QB with OR to replace SQL/UNION...)

[ptalbot]

I have to contest that using QB is 'best practice'. I know a lot of Servoy developers that don't use it, for good reasons.

100% agreed!

The QB syntax is atrocious and when trying to add joins from many tables you get all warnings for unrecognized colums, so you have to add @type annotations and intermediate variables. In the end, the resulting code is hard to read, which definitely defeats the purpose.

As usual, YMMV