Complex example queries

This page contains some more complex example queries (see Query language).

FreeDB - Queen cover songs

This example is for the FreeDB sample project found in the samples folder. It finds songs that are covers of a Queen song or more specifically songs that have the same name as some Queen song.

1  importPackage(org.wandora.query2);
2  new Join(
3      new Players("http://www.wandora.net/freedb/track",
4                  "http://www.wandora.net/freedb/artist")
5      .as("#artist").of("#track").from(
6          new Regex("^([^\\(]*).*$","$1",0).as("#trackname").from(
7              new BaseName().from(
8                  new Instances().as("#track").from("http://www.wandora.net/freedb/track")
9              )
10         )
11     ),
12     new Regex("^([^\\(]*).*$","$1",0).as("#queenname").from(
13         new BaseName().from(
14             new Players(
15                 "http://www.wandora.net/freedb/track",
16                 "http://www.wandora.net/freedb/track"
17             ).as("#queentrack").from(
18                 new Topics().as("#queen").from("http://www.wandora.net/freedb/artist/QUEEN")
19             )
20         )
21     )
22 ).where(new Of("#trackname"),"=",new Of("#queenname"))
23 .where(new Of("#artist"),"t!=",new Of("#queen"))

This query consists of two main parts which are joined with a Join query and then the relevant rows are selected with two where clauses.

The first part on lines 3 to 11 selects all tracks (line 8) their base names (line 7) then modifies the basename with a regular expression that removes the id number of the song from the base name (line 6). Finally artist of the track is selected (on lines 3 and 4).

The other part does same thing but only for Queen tracks. It starts with the Queen topic which is also the artist (line 18). Then we get players of track associations, this selects all tracks of Queen (lines 14 to 16). Finally we get the base name (line 13) and modify it with the same regular expression as before (line 12).

Joining these two parts gets us 6 columns, "#track", "#trackname", "#artist", "#queen", "#queentrack", "#queenname". The where clause on line 22 compares "#trackname" and "#queenname" and only selects rows where these are equal. The final where clause on line 23 checks that the "#artist" isn't actually queen. All Queen songs are of course selected in the first part of the join and would match the exact same songs on the other part of the join.