How to get unread messages using FQL?

Question

I am trying to get the body and the sender of all unread inbox .

To get all conversation's threads with unread messages I used this query:

SELECT thread_id from unified_thread WHERE folder='inbox' AND unread=1

to get the unread message of a thread I used this query

SELECT sender,body FROM unified_message WHERE unread=1

I have tried the following nested query :

SELECT sender,body FROM unified_message WHERE thread_id IN (SELECT thread_id FROM unified_thread  WHERE folder = 'inbox' AND unread=1) AND unread=1"

but I only get unread message from one thread and not all unread threads.

I also tried multiquery like this:

String query1="SELECT thread_id FROM unified_thread WHERE folder='inbox' AND unread=1";
String query2="SELECT timestamp,sender,body FROM unified_message WHERE unread=1 AND thread_id IN (SELECT thread_id FROM #query1)";
Bundle params = new Bundle();
JSONObject jsonFQL=new JSONObject();
try {
        jsonFQL.put("query1",query1);
        jsonFQL.put("query2",query2);
} catch (JSONException e) {
        e.printStackTrace();
}

params.putString("method","fql.multiquery");
params.putString("queries", jsonFQL.toString());
new Request(session,"/fql",params,HttpMethod.GET,new Request.Callback(){
      public void onCompleted(Response response) {
                                     ...
                                 }
                     ).executeAsync(); ....

but I got error:

errorMessage: Unsupported method, fql.multiquery

Then I tried with INNER JOIN:

SELECT unified_message.sender,unified_message.body 
            FROM unified_message 
            INNER JOIN unified_thread 
            ON unified_message.thread_id=unified_thread.thread_id
            WHERE unified_thread.unread=1

but I got this error:

Parser error: unexpected 'INNER' at position...

I learnt JOIN is not supported in FQL

Can somebody give me a hand to do this query in FQL ??

example of needed output: I have 5 conversations with different people, but only 3 conversations have unread messages. So I would like to get something like this:

UNREAD MESSAGES

Sender: Anna 
Body: hello dude 
Body: how are you? 
Body: I miss you

Sender: John
Body: please help me 

Sender: Erick 
Body: nice
Body: buddy

Possible duplicated of http://stackoverflow.com/questions/13222111/fql-multiquery-writing-join-queries or http://stackoverflow.com/questions/6553962/fql-join-can-i-join-two-tables-with-fql — Cedric Simon, Aug 05 '14 at 14:29
I do not consider it is duplicated, I read them before posting this, that is why I wrote the multiquery and nested query that I used as suggested in those links , but It is not working — Dany19, Aug 05 '14 at 15:09
FQL will be deprecated on version API 2.1, so i'm not encourage you to use FQL for new development. Some threads may not be able to fetch because of FQL bug, but no point from their API team to fix the deprecated API bug. — 林果皞, Aug 11 '14 at 18:19

score 0 · Answer 1 · answered Aug 11 '14 at 10:05

Multi-query:

Evaluates a series of FQL (Facebook Query Language) queries in one call and returns the data at one time.

This method takes a JSON-encoded dictionary called ''queries'' where the individual queries use the exact same syntax as a simple query. However, this method allows for more complex queries to be made. You can fetch data from one query and use it in another query within the same call. The WHERE clause is optional in the latter query, since it references data that’s already been fetched. To reference the results of one query in another query within the same call, specify its name in the FROM clause, preceded by #.

For example, say you want to get some data about a user attending an event. Normally, you’d have to perform two queries in a row, waiting for the results of the first query before running the second query, since the second query depends on data from the first one. But with fql.multiquery, you can run them at the same time, and get all the results you need, giving you better performance than running a series of fql.query calls. First, you need to get the user ID and RSVP status of each attendee, so you’d formulate the first query – query1 – like this:

*

"query1":"SELECT uid, rsvp_status FROM event_member WHERE eid=12345678"

*

Then to get each attendee’s profile data (name, URL, and picture in this instance), you’d make a second query – query2 – which references the results from query1. You formulate query2 like this:

"*

query2":"SELECT name, url, pic FROM profile WHERE id IN (SELECT uid FROM #query1)

*"

https://developers.facebook.com/docs/technical-guides/fql

score 0 · Answer 2 · answered Aug 11 '14 at 18:03

I am not entirely sure if this fixes your problem, but it seems that the parameter is "q", not "queries":

params.putString("q", jsonFQL.toString());

...if you take a look at the Facebook docs:

https://developers.facebook.com/docs/technical-guides/fql

Could be possible that they changed it recently, although it is deprecated so i dont think they will put much effort into it.

score 0 · Answer 3 · answered Aug 11 '14 at 18:22

This nested query you placed

SELECT sender,body FROM unified_message WHERE thread_id IN (SELECT thread_id FROM unified_thread WHERE folder = 'inbox' AND unread=1) AND unread=1

should work. If it doesn't, consider filing a bug.

Breaking it down,

SELECT thread_id FROM unified_thread WHERE folder = 'inbox' AND unread=1

Should give you the thread ids for threads with unread messages. The only catch here is that unified_thread doesn't necessarily return all threads even with LIMITapplied. So what you did here was good (as long as the unread response set is small enough)

If at this point, the numbers aren't matching what you have, it doesn't make sense to move further in the query. File a bug.

The larger query

SELECT sender,body FROM unified_message WHERE thread_id IN (SELECT thread_id FROM unified_thread WHERE folder = 'inbox' AND unread=1) AND unread=1

Checks all messages within the thread, so as long as your thread ids are valid and correctly counted then there is nothing to worry about here.

Ensure you are getting the correct count of unread threads first If not, as I said before, file a bug.

So that with this query

SELECT thread_id, participants, link FROM unified_thread WHERE folder = 'inbox' AND unread

Cross check that the participants and links match what you have in your inbox.

Stéphane Bruckert · Accepted Answer · 2014-08-11T18:37:28.187

This works:

  SELECT sender, body FROM unified_message 
   WHERE thread_id IN 
         (SELECT thread_id FROM unified_thread WHERE folder = 'inbox' AND unread=1) 
     AND unread=1 
ORDER BY timestamp DESC

It is important to sort the messages you retrieve in a descending order using:

ORDER BY timestamp DESC

Otherwise, only the first older messages of the conversation will be examined, whereas unread messages should be recent.

Just for your knowledge, here is the corresponding multi-query, which gives the same result:

{
 "threads":"SELECT thread_id FROM unified_thread WHERE folder='inbox' AND unread=1",
 "messages":"SELECT sender, body FROM unified_message WHERE thread_id IN (SELECT thread_id FROM #threads) AND unread=1 ORDER BY timestamp DESC"
}

However, you should not use FQL anymore:

Version 2.0 of the Facebook Platform API is the last version where FQL will be available. Versions after 2.0 will not support FQL. Please migrate your applications to use Graph API instead of FQL. Please see our changelog for current version information.

I suggest you use the following Graph API tables:

By the way, FQL doesn't have such INNER JOIN.

How to get unread messages using FQL?

4 Answers4

Linked