Olli at 13:14, 13 August 2010

2010-08-13T13:14:05Z

Olli at 13:33, 24 August 2009

2009-08-24T13:33:24Z

New page

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("#trackname","=","#queenname")
23 .where("#artist","t!=","#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.

@@ Line 26: / Line 26: @@
          )
      )
-).where("#trackname","=","#queenname")
+).where(new Of("#trackname"),"=",new Of("#queenname"))
-.where("#artist","t!=","#queen")
+.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.

Complex example queries - Revision history

Olli at 13:14, 13 August 2010

Olli at 13:33, 24 August 2009