DBO: Select using unions
Added by Emeric Poupon almost 6 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 6 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.