Query that provides information about threads in a cluster

[tuxpiper]

For each cluster (or "pheme") we would need the threads that are present in it, and for each thread:

• The id of the thread
• Detected presence of tweets from twitter-verified authors in the thread
• A featured tweet belonging to that thread, that is already stored in graphdb. It can be the oldest, if we can't have a better strategy, but it could also be the most retweeted/responded to). For that featured tweet, we would need at least:
  • Text
  • Date
  • Author user details

[lauratolosi]

Hold on, here it goes, the monster query:

PREFIX sioc: <http://rdfs.org/sioc/ns#>
prefix pheme: <http://www.pheme.eu/ontology/pheme#>
PREFIX dlpo: <http://www.semanticdesktop.org/ontologies/2011/10/05/dlpo#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
select * {

{select ?thread (min(?date) as ?first) (sum(xsd:integer(xsd:boolean(?verified))) as ?countVerifiedUsers) {   # this subselect returns the thread, the date of the first tweet
    ?tweet a pheme:Tweet .
    ?tweet pheme:version "v7" .
    ?tweet sioc:has_container ?thread .
    ?tweet pheme:dataChannel "6c9fcb94" .  # This is for Brexit for example
    ?tweet pheme:createdAt ?date .
    ?tweet sioc:has_creator ?user .
    ?user pheme:twitterUserVerified ?verified .
    #?tweet pheme:eventId "-1" .    # Here restrict to the eventId/ cluster that you need; I assume it get faster?!? 

        } group by ?thread 
          having (count(?tweet) > 1)  # remove this if you want threads with 1 tweet as well 
}
    ?tweet sioc:has_container ?thread .   # this takes tweets that belong to the selected thread 
    ?tweet pheme:createdAt ?first .       # .. and are published on the selected date = first
    ?tweet dlpo:textualContent ?text .
    ?tweet sioc:has_creator ?user .
    ?user foaf:accountName ?accountName .
    ?user pheme:twitterFollowersCount ?numberOfFollowers .  # feel free to add/ remove user characteristics
    ?user pheme:twitterStatusesCount ?numberOfPosts .
    ?user foaf:depiction ?avatar .
}
limit 50 

This is what happens when you get to the expert :))