Project

General

Profile

DBO: Select using unions

Added by Emeric Poupon almost 5 years ago

Hello,

I don't manage to write a Dbo query involving an union.

I can write write it by hand (and it works), but it does not work with Wt::Dbo::Query.

Here is the query I want:

SELECT DISTINCT * FROM release r INNER JOIN artist a ON t_a.artist_id = a.id INNER JOIN track_artist t_a ON t_a.track_id = r."id" INNER JOIN track t ON t.release_id = r.id WHERE (a.id = 5)
UNION
SELECT DISTINCT * FROM release r INNER JOIN artist a ON t_r_a.artist_id = a.id INNER JOIN track_release_artist t_r_a ON t_r_a.track_id = t.id INNER JOIN track t ON t.release_id = r.id WHERE (a.id = 5)
ORDER BY t.year,r.name;

-> works fine using sqlite3 command line program.

Using Dbo:

SELECT DISTINCT r from release r INNER JOIN artist a ON t_a.artist_id = a.id INNER JOIN track_artist t_a ON t_a.track_id = t.id INNER JOIN track t ON t.release_id = r.id WHERE (a.id = ?)
UNION
SELECT DISTINCT r FROM release r INNER JOIN artist a ON t_r_a.artist_id = a.id INNER JOIN track_release_artist t_r_a ON t_r_a.track_id = t.id INNER JOIN track t ON t.release_id = r.id WHERE (a.id = ?) ORDER BY t.year,r.name

Here is the error I got:

[2019-Apr-30 20:52:23.679] 1547 [/ QvEptpyiTH9gS8Ri] [error] - [UI] Error while handling auth event: Sqlite3: SELECT DISTINCT r."id" as col0, r."version" as col1, r."name" as col2, r."mbid" as col3 from release r INNER JOIN artist a ON t_a.artist_id = a.id INNER JOIN track_artist t_a ON t_a.track_id = t.id INNER JOIN r."id" as col0, r."version" as col1, r."name" as col2, r."mbid" as col3rack t ON t.release_id = r.id WHERE (a.id = ?) UNION SELECT DISTINCT r FROM release r INNER JOIN artist a ON t_r_a.artist_id = a.id INNER JOIN track_release_artist t_r_a ON t_r_a.track_id = t.id INNER JOIN track t ON t.release_id = r.id WHERE (a.id = ?) ORDER BY t.year,r.name: near "t": syntax error

Looks like there are some weird stuff around the colX fields.

What do you think?


Replies (1)

RE: DBO: Select using unions - Added by Roel Standaert almost 5 years ago

At first I thought this was likely not supported, but looking closer at SqlQueryParse, it does seem like that should be properly supported.

I think this workaround should work (putting the union in a subquery):

SELECT r FROM (
SELECT DISTINCT * FROM release r INNER JOIN artist a ON t_a.artist_id = a.id INNER JOIN track_artist t_a ON t_a.track_id = r."id" INNER JOIN track t ON t.release_id = r.id WHERE (a.id = 5)
UNION
SELECT DISTINCT * FROM release r INNER JOIN artist a ON t_r_a.artist_id = a.id INNER JOIN track_release_artist t_r_a ON t_r_a.track_id = t.id INNER JOIN track t ON t.release_id = r.id WHERE (a.id = 5)
ORDER BY t.year,r.name) r;

I'll see what's going wrong exactly.

    (1-1/1)