FQL: Trouble joining tables where id is in format USERID_LINKID

Question

I am running the following FQL via the php sdk:

{
"posts" : "SELECT post_id, actor_id, message, type FROM stream WHERE type = '80' AND message != '' AND filter_key in (SELECT filter_key FROM stream_filter WHERE uid='588853265' AND type='newsfeed') AND is_hidden = 0 ORDER BY created_time DESC LIMIT 50", 

"actors" : "SELECT uid,name FROM user WHERE uid IN (SELECT actor_id FROM #posts)",

"links" : "SELECT owner, url FROM link WHERE link_id IN (SELECT post_id FROM #posts)"
}

I get results as expected for posts and for actors but not for links (results are empty). I believe the problem is that the link table uses a normal ID but my 'posts' from 'stream' show the ID in the format: USERID_LINKID.

I have played around with substr() and strlen() to get it to work with no luck.

Answer 1

There doesn't seem to be a way to get a link_id from a stream post. Your query is getting the id of the post, but not the link.

However, I don't think you need it. Get rid of the #links sub-query and add attachment to the SELECT statement in your #posts sub-query. You can get the link url at attachment->media->url.

One other critique: You might want to add additional sub-queries for #actors. The actor_id returned by the stream table can be a user, page, or group. Your FQL only resolves users. You'll end up with unknown IDs for links posted by groups and pages.