Retrieving/storing All related Actors in Freebase

Question

I'm trying to do something that (should be) very simple. Maybe I'm missing something.

I want to get a full list of people a person has worked with. My end goal is to find who he works with most often (in whatever relationship that may be)

Ex: Robert De Niro has acted together with Joe Pesci 7 times, but directed him twice. I want a table with 9 entries as a result.

I can use the topic API, but that only returns a list of movies. I then have to perform 10+ API queries to get the cast of every movie. Takes forever and the code is a nightmare.

If I use MQL search, I can only search for movies that Robert De Niro has starred in, but not every movie he has directed, or written, or produced, or starred in. Basically I can only search by 1 role at a time.

Is there a better way? I need to end up with a list of:

Movies

Actors/Crew People

Roles linking Movies and People

What I do currently:

Search for Robert De Niro and obtain the Machine ID
Do a topic search for that MID, returning a list of movie MIDs he has worked on
Do a topic search for each movie MID, and record fields like directed_by, starring, produced_by, etc

As you can see it's a very expensive operation. It's also extremely difficult to avoid duplicates in this way (though I'm working on it)

edit: Here's my current MQL query (for some reason it only works if I specify two actor names, as well, but that's another issue.

$query = array(array(
                        'a:starring'=>array('actor'=>'Joe Pesci'),
                        'b:starring'=>array('actor'=>'Robert De Niro'),
                        'directed_by'=>null,
                        'produced_by'=>array(),
                        'written_by'=>array(),
                        'executive_produced_by'=>array(),
                            'name'=>null,
                            'mid'=>null,
                        'starring'=>array(array('actor'=>array('mid'=>null,
                                   'name'=>null))),
                        'type'=>'/film/film'
                        ));

The MQL:

    [{
    "a:starring":
        {"actor":"Joe Pesci"},
    "b:starring":
        {"actor":"Robert De Niro"},
    "directed_by":null,
    "produced_by":[],
    "written_by":[],
    "executive_produced_by":[],
    "name":null,
    "mid":null,
    "starring":
        [{"actor":
            {"mid":null,"name":null}}],
    "type":"\/film\/film"}]

Tom Morris · Accepted Answer · 2013-11-27T02:29:57.517

2

You can do this all in a single MQL query with different subqueries for the directed/wrote/acted in properties. Just make sure you make each subquery optional.

For example:

[{
  "a:starring": {
    "actor": "Joe Pesci"
  },
  "b:starring": {
    "actor": "Robert De Niro"
  },
  "directed_by": [{
    "id": null,
    "name": null,
    "optional": true
  }],
  "produced_by": [{
    "id": null,
    "name": null,
    "optional": true
  }],
  "written_by": [{
    "id": null,
    "name": null,
    "optional": true
  }],
  "executive_produced_by": [{
    "id": null,
    "name": null,
    "optional": true
  }],
  "name": null,
  "mid": null,
  "starring": [{
    "actor": {
      "mid": null,
      "name": null
    }
  }],
  "type": "/film/film"
}]

edited Nov 27 '13 at 02:29

answered Nov 22 '13 at 03:04

Tom Morris

10,490
32
53

Would you be so kind as to provide an example query? Updating post with my current query I'm using. This is my first time with MQL and the documentation is very confusing. I did see the optional:optional flag but that didn't return any results. Alternatively, if you know of any way to get error messages back from the API, that would also help (currently just returns NULL if the query is incorrect in some way) – sricks Nov 23 '13 at 00:09
If you add the MQL (instead of PHP or whatever that currently is), I'll look at modifying it for you. The API does return errors for malformed queries. For queries which match no topics, it returns null or an empty set. By not using "optional", you are overconstraining the query so it returns no results. – Tom Morris Nov 23 '13 at 14:27
@TomMorris +1! pleased to meet you, Freebase user. – Jason Heo Nov 27 '13 at 02:48

score -1 · Answer 2 · answered Nov 22 '13 at 03:09

I don't know MQL but standard SQL would be like this:

Select
p.Name + ' has worked with '+p2.name+' on the movie '+m.Name
from Person p
join Roles r on r.PersonId=p.Id
join Movie m on m.Id=r.MovieId
join Roles r2 on r.MovieId=m.Id and r2.Id <> r.Id
join Person p2 on p2.Id=r2.PersonId

Retrieving/storing All related Actors in Freebase

2 Answers2

Linked