I have an User Model which uses a standard MySQL database and users table and a Movie Model which is a datasource from Rotten Tomatoes. They have a hasAndBelongsToMany relationship and I'm successfully able to write to the join table users_movies which holds the user_id and movie_id (the movie_id is the Rotten Tomatoes id). Works great.
The trouble is retrieving an User's movies. The standard find:
$movies = $this->User->find('all', array('conditions' => array('id' => $user_id)));
only returns the User not the associated Movie(s). I put a die statement in my read method in the DataSource and it's not even reaching the read method. How can I go about retrieving an User's movies?
So Rotten Tomatoes is holding your movies? In that case, of course Rotten Tomatoes wouldn't allow direct SQL access to their database - you'd be accessing it via an API. So Cake definitely won't just be able to join Users to Movies the way it normally would with two tables in the same database.
What you'll have to do is 1) get a list of the user's movie_id's from Cake, and then 2) Call the Rotten Tomatoes API to get a list of movies where their ID is in your list of movie_id's. (That's assuming rotten tomatoes allows such an API call.)
Having a quick look at the API, it looks like their 'movies search' (http://developer.rottentomatoes.com/docs/json/v10/Movies_Search) only allows you to specify plain-text as the search criteria (ie, you can't search based on movie id's). And their 'movie info' method (http://developer.rottentomatoes.com/docs/json/v10/Movie_Info), which does allow you to retrieve a movie by id, only allows you to retrieve one movie at a time.
You could of course loop through your list of movie id's for a given user, and make a separate API call to rotten tomatoes for each one - though I'd imagine that would get VERY slow.
Someone has put in a feature request for retrieving based on a list of multiple id's (http://developer.rottentomatoes.com/forum/read/123940) but until that request gets implemented, you will probably be having a tough time getting anything decent working.