Wikidata sparql get related actors

Question

I was attempting to write a query to get, given an actor, the actors that were cast members in the same movies (s)he played in.

The following query gives me the movies the actor played in.

SELECT distinct ?actorLabel ?movieLabel  ?boxOffice
WHERE {
  {
    SELECT ?actor WHERE {
      ?actor rdfs:label "Johnny Depp"@en.
      ?actor wdt:P106 ?occupation.
      ?occupation wdt:P279+ wd:Q33999.
    }
  }
  ?movie wdt:P161 ?actor.
  ?movie wdt:P2142 ?boxOffice.

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?boxOffice)
limit 10

so now i want to get all the related actors from each of those movies. i attempted to do this with a subquery as such

SELECT distinct ?actorLabel ?movieLabel  ?boxOffice ?relatedActor ?relatedActorLabel
WHERE {
  {
    SELECT ?actor WHERE {
      ?actor rdfs:label "Johnny Depp"@en.
      ?actor wdt:P106 ?occupation.
      ?occupation wdt:P279+ wd:Q33999.
    }
  }
  ?movie wdt:P161 ?actor.
  ?movie wdt:P2142 ?boxOffice.
  {
    SELECT ?relatedActor WHERE {
      ?movie wdt:P161 ?relatedActor
    }
    limit 5
  }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?boxOffice)
limit 10

but for some reason i don't understand i keep getting Barack Obama and Douglas Adams as the only results. even when starting from completely different actors.

Can anyone shed some light on this, what am i doing wrong?

How `?relatedActor` is related to `?actor`? Subqueries are evaluated (logically) first. Add `?movie` to the projection of the second subquery and remove `limit 5`. Or even https://w.wiki/bud. — Stanislav Kralin, Sep 12 '20 at 10:36
Related actor is not linked to actor directly, rather than to the movie the initial actor plays in. If i do it like you suggest i get all cast members of each movie the initial actor plays in, but i'm only looking to get a couple of them (hence the `limit 5`) — Enermis, Sep 12 '20 at 10:43
that's not gonna work in SPARQL 1.1 - you can't get just 5 actors per movie because there is no windowing nor correlated subqueries - this is a feature request for SPARQL 1.2 — UninformedUser, Sep 12 '20 at 11:06
Ah perfect. so my best bet is to do it in a series of single queries and filter them externally? I'll do it like that then. Thanks — Enermis, Sep 12 '20 at 11:21

score 0 · Accepted Answer · answered Sep 17 '20 at 00:50

Here is a query does what you want, more or less:

SELECT ?actorLabel (SAMPLE(?movieLabel) AS ?sample_movie) 
  (GROUP_CONCAT(?movieLabel) AS ?movies) (COUNT(?movie) AS ?number_movies) 
  ?relatedActor ?relatedActorLabel WHERE {
    VALUES ?actor { wd:Q37175 }
    ?movie wdt:P161 ?actor, ?relatedActor.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?relatedActor ?actorLabel ?relatedActorLabel
ORDER BY DESC (COUNT(?relatedActor))

(Link)

It doesn't do the limit. But, on the plus side, it still works and gets about 1000 results. I believe the aggregation of movie names should work, but suffers from a bug–there is a section on that in the documentation somewhere, but I can't find it right now.

I sorted by number of joint appearances, but box office should work similarly. I am not sure, however, how complete the data on box office returns is.

The Johnny Depp item had also been vandalised recently, which may have been the reason why your query did not work. At least I did not get any results at first.

score 0 · Answer 2 · edited Sep 02 '21 at 13:08

0

SELECT distinct ?actorLabel ?movieLabel ?relatedActor ?relatedActorLabel
WHERE {
  ?actor rdfs:label "Johnny Depp"@en.
  ?movie wdt:P161 ?actor.
  ?movie wdt:P161 ?relatedActor.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

edited Sep 02 '21 at 13:08

Adrian Mole

49,934
160
51
83

answered Aug 31 '21 at 22:14

user12115445

19
4

Wikidata sparql get related actors

2 Answers2