0

Need help with the SELECT something.name syntax

I have an issue trying to get this query to work :

CODE
SELECT * FROM lastfm.track.gettoptags WHERE api_key="b25b959554ed76058ac220b7b2e0a026" and (artist, track) in (SELECT artist, name FROM lastfm.user.getrecenttracks WHERE user="Mobman02" and api_key="b25b959554ed76058ac220b7b2e0a026" and limit="10")


In fact, it's my second select which fail, I can get it select the artists / tracks (SELECT artist, name FROM lastfm.user.getrecenttracks).
I have tried a lot of requests, but nothing work as expected. Also the documentation don't say that community tables can't get the SELECT what to work.

So my question is : How to select only the artist and track name from the Lastfm API answer ?

Here is a XML response example, just in case :

CODE
<?xml version="1.0" encoding="UTF-8"?>
<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"
yahoo:count="1" yahoo:created="2010-11-24T14:20:35Z" yahoo:lang="en-US">
<results>
<lfm status="ok">
<recenttracks page="1" perPage="10" totalPages="5442" user="Mobman02">
<track nowplaying="true">
<artist mbid="a0955511-1324-4e9b-834f-e1e140502850">Monster Magnet</artist>
<name>Look To Your Orb For The Warning</name>
<streamable>1</streamable>
<mbid/>
<album mbid="290aa602-c4c3-4a64-a1fa-3681ea2dc503">Dopes To Infinity</album>
<url>http://www.last.fm/music/Monster+Magnet/_/Look+To+Your+Orb+For+The+Warning</url>
<image size="small">http://userserve-ak.last.fm/serve/34s/8600403.jpg</image>
<image size="medium">http://userserve-ak.last.fm/serve/64s/8600403.jpg</image>
<image size="large">http://userserve-ak.last.fm/serve/126/8600403.jpg</image>
<image size="extralarge">http://userserve-ak.last.fm/serve/300x300/8600403.jpg</image>
</track>
<track>
<artist mbid="">Rescue Rangers</artist>
<name>Sounds of the Katana</name>
<streamable>1</streamable>
<mbid/>
<album mbid="">Guitars and dust dancing</album>
<url>http://www.last.fm/music/Rescue+Rangers/_/Sounds+of+the+Katana</url>
<image size="small">http://userserve-ak.last.fm/serve/34s/50699575.jpg</image>
<image size="medium">http://userserve-ak.last.fm/serve/64s/50699575.jpg</image>
<image size="large">http://userserve-ak.last.fm/serve/126/50699575.jpg</image>
<image size="extralarge">http://userserve-ak.last.fm/serve/300x300/50699575.jpg</image>
<date uts="1290608217">24 Nov 2010, 14:16</date>
</track>
<track>
<artist mbid="f96de591-5dae-41da-855b-eb387d88d7d4">Fu Manchu</artist>
<name>Hell On Wheels</name>
<streamable>0</streamable>
<mbid/>
<album mbid="58e32f5c-a89a-4be3-b6e8-731d6193ef23">King Of The Road</album>
<url>http://www.last.fm/music/Fu+Manchu/_/Hell+On+Wheels</url>
<image size="small">http://userserve-ak.last.fm/serve/34s/6946539.jpg</image>
<image size="medium">http://userserve-ak.last.fm/serve/64s/6946539.jpg</image>
<image size="large">http://userserve-ak.last.fm/serve/126/6946539.jpg</image>
<image size="extralarge">http://userserve-ak.last.fm/serve/300x300/6946539.jpg</image>
<date uts="1290607931">24 Nov 2010, 14:12</date>
</track>
[...]
</recenttracks>
</lfm>
</results>
</query>


And extra question : How can I select in my root select fields from the child ? (like SELECT gettoptags.*, getrecenttracks.date FROM lastfm.track.gettoptags in SQL).

PS : that's not my personnal API key but it should works for tests

Thx a lot \o/

by
3 Replies
  • Hi Damien,

    I guess the problem is iin th definition for the table lastfm.user.getrecenttracks. It´s not defining the item path once you query the lastfm service.

    I got the table and added what I think is missing, so you can try your second select:

    CODE
    use "store://LEEef4obywe7cRb09ssR6W" as table;SELECT artist, name FROM table WHERE user="Mobman02" and api_key="b25b959554ed76058ac220b7b2e0a026" and limit="10"


    And if you want to do the full query:
    CODE
    use "store://LEEef4obywe7cRb09ssR6W" as table;SELECT * FROM lastfm.track.gettoptags WHERE api_key="b25b959554ed76058ac220b7b2e0a026" and (artist, track) in (SELECT artist, name FROM table WHERE user="Mobman02" and api_key="b25b959554ed76058ac220b7b2e0a026" and limit="10")


    Hope this is what you were looking for.

    Cheers,
    Francisco.

    QUOTE (Damien @ Nov 24 2010, 07:11 AM) <{POST_SNAPBACK}>
    I have an issue trying to get this query to work :

    CODE
    SELECT * FROM lastfm.track.gettoptags WHERE api_key="b25b959554ed76058ac220b7b2e0a026" and (artist, track) in (SELECT artist, name FROM lastfm.user.getrecenttracks WHERE user="Mobman02" and api_key="b25b959554ed76058ac220b7b2e0a026" and limit="10")


    In fact, it's my second select which fail, I can get it select the artists / tracks (SELECT artist, name FROM lastfm.user.getrecenttracks).
    I have tried a lot of requests, but nothing work as expected. Also the documentation don't say that community tables can't get the SELECT what to work.

    So my question is : How to select only the artist and track name from the Lastfm API answer ?

    Here is a XML response example, just in case :

    CODE
    <?xml version="1.0" encoding="UTF-8"?>
    <query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"
    yahoo:count="1" yahoo:created="2010-11-24T14:20:35Z" yahoo:lang="en-US">
    <results>
    <lfm status="ok">
    <recenttracks page="1" perPage="10" totalPages="5442" user="Mobman02">
    <track nowplaying="true">
    <artist mbid="a0955511-1324-4e9b-834f-e1e140502850">Monster Magnet</artist>
    <name>Look To Your Orb For The Warning</name>
    <streamable>1</streamable>
    <mbid/>
    <album mbid="290aa602-c4c3-4a64-a1fa-3681ea2dc503">Dopes To Infinity</album>
    <url>http://www.last.fm/music/Monster+Magnet/_/Look+To+Your+Orb+For+The+Warning</url>
    <image size="small">http://userserve-ak.last.fm/serve/34s/8600403.jpg</image>
    <image size="medium">http://userserve-ak.last.fm/serve/64s/8600403.jpg</image>
    <image size="large">http://userserve-ak.last.fm/serve/126/8600403.jpg</image>
    <image size="extralarge">http://userserve-ak.last.fm/serve/300x300/8600403.jpg</image>
    </track>
    <track>
    <artist mbid="">Rescue Rangers</artist>
    <name>Sounds of the Katana</name>
    <streamable>1</streamable>
    <mbid/>
    <album mbid="">Guitars and dust dancing</album>
    <url>http://www.last.fm/music/Rescue+Rangers/_/Sounds+of+the+Katana</url>
    <image size="small">http://userserve-ak.last.fm/serve/34s/50699575.jpg</image>
    <image size="medium">http://userserve-ak.last.fm/serve/64s/50699575.jpg</image>
    <image size="large">http://userserve-ak.last.fm/serve/126/50699575.jpg</image>
    <image size="extralarge">http://userserve-ak.last.fm/serve/300x300/50699575.jpg</image>
    <date uts="1290608217">24 Nov 2010, 14:16</date>
    </track>
    <track>
    <artist mbid="f96de591-5dae-41da-855b-eb387d88d7d4">Fu Manchu</artist>
    <name>Hell On Wheels</name>
    <streamable>0</streamable>
    <mbid/>
    <album mbid="58e32f5c-a89a-4be3-b6e8-731d6193ef23">King Of The Road</album>
    <url>http://www.last.fm/music/Fu+Manchu/_/Hell+On+Wheels</url>
    <image size="small">http://userserve-ak.last.fm/serve/34s/6946539.jpg</image>
    <image size="medium">http://userserve-ak.last.fm/serve/64s/6946539.jpg</image>
    <image size="large">http://userserve-ak.last.fm/serve/126/6946539.jpg</image>
    <image size="extralarge">http://userserve-ak.last.fm/serve/300x300/6946539.jpg</image>
    <date uts="1290607931">24 Nov 2010, 14:12</date>
    </track>
    [...]
    </recenttracks>
    </lfm>
    </results>
    </query>


    And extra question : How can I select in my root select fields from the child ? (like SELECT gettoptags.*, getrecenttracks.date FROM lastfm.track.gettoptags in SQL).

    PS : that's not my personnal API key but it should works for tests

    Thx a lot \o/
    0
  • Can I say I love you ? :lBye and thank again.
    0
  • Hi again Damien,

    I just went to github, took a look to the code and added the missing item path, here is the result:

    CODE
    <?xml version="1.0" encoding="UTF-8"?>
    <table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
    <meta>
    <author>Jamie Matthews</author>
    <description>YQL table for Last.fm User.getRecentTracks API method. Get a list of the recent tracks listened to by this user. Also includes the currently playing track with the nowplaying=&quot;true&quot; attribute if the user is currently listening.</description>
    <documentationURL>http://www.last.fm/api/show?service=278</documentationURL>
    </meta>
    <bindings>
    <select itemPath="lfm.recenttracks.track" produces="XML">
    <urls>
    <url>http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks</url>
    </urls>
    <inputs>
    <key id="user" type="xs:string" paramType="query" required="true" />
    <key id="limit" type="xs:string" paramType="query" required="false" />
    <key id="page" type="xs:string" paramType="query" required="false" />
    <key id="api_key" type="xs:string" paramType="query" required="true" />

    </inputs>
    </select>
    </bindings>
    </table>


    Maybe you can fork the project in github and modify the source, so more people will be able to use it!

    Happy hacking,
    Francisco.

    QUOTE (Damien @ Nov 25 2010, 04:43 PM) <{POST_SNAPBACK}>
    Can I say I love you ? :lBye and thank again.
    0

Recent Posts

in YQL