More than one many-to-many relation between the same tables

[Alberto Anguita]

Hi!

I am trying to model a situation where I have a table "movies" and a table "people". I want to store movie actors, as well as movie directors. My idea was therefore to create to assotiation tables between movies and people. So I created the schema like this:

        "CREATE TABLE movies (" +
                "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                "title TEXT, " +
                "year INTEGER " +
                ")"

        "CREATE TABLE people (" +
                "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                "name TEXT " +
                ")"

        "CREATE TABLE movies_actors (\n" +
                "movie_id INTEGER NOT NULL REFERENCES movies(id), " +
                "person_id  INTEGER NOT NULL REFERENCES people(id), " +
                "PRIMARY KEY (movie_id, person_id)\n" +
                ")"

        "CREATE TABLE movies_directors (\n" +
                "movie_id INTEGER NOT NULL REFERENCES movies(id), " +
                "person_id  INTEGER NOT NULL REFERENCES people(id), " +
                "PRIMARY KEY (movie_id, person_id)\n" +
                ")"

And created the 4 corresponding models:

public class Movie extends Model {}

public class Person extends Model {}

@Table("movies_actors")
public class MovieActors extends Model {}

@Table("movies_directors")
public class MovieDirectors extends Model {}

However, ActiveJDBC does not seem to recognize the models for the associations. Indeed, in the samples I cannot find how to specify which of the two associations I want to use when retrieving persons (actors) related to a movie. Thus, when I execute:

List<Person> actors = movie.getAll(Person.class);

I get "NotAssociatedException: No association from table 'movies' to table 'people'"

Is it possible to achieve what I am trying in ActiveJDBC? 

Sorry if I am making some basic mistake, I just started with ActiveJDBC a couple hours ago! (it seems awesome, by the way, thanks for it!)

Regards.

[Igor Polevoy]

Alberto, I think you hit  a strange bug, which will take some time to explore. However,  if you bring your tables under control, you will avoid it all together. 

I created a working  example for you: https://github.com/javalite/simple-example/tree/manytomany  - on this branch only. Clone this project and switch to manytomany branch to run the code and experiment with it. 

Basically, you do not need two join tables, only  create one: 

CREATE TABLE movies_people (
  id        INT(11) NOT NULL AUTO_INCREMENT,
  movie_id  INTEGER NOT NULL,
  person_id INTEGER NOT NULL,
  type VARCHAR(128),
  PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

As you can see, it has a column 'type' - type of a person (actor or director) and you can use a Person.TYPE https://github.com/javalite/simple-example/blob/manytomany/src/main/java/activejdbc/examples/simple/Person.java#L10 enum, to use it to tag people. 

So, adding a new person to a movie looks like this: 

https://github.com/javalite/simple-example/blob/manytomany/src/main/java/activejdbc/examples/simple/Movie.java#L16

and looking for people of specific type looks like this: 

List<Person> directors = movie.get(Person.class, "type = ? ", Person.TYPE.director.name()); 

My example is based on MySQL. 

I hope this helps !

Thanks

[Alberto Anguita]

I just tried your approach and it works like a charm! Thanks a lot!!

[Alberto Anguita]

One minor question, regarding memory usage in this example: if I get a Movie from my model, does this retrieval trigger other get operations for related persons, following the associative table, or these get operations are only performed once I execute the

List<Person> persons = movie.get(Person.class, "type = ? ", Person.TYPE.actor.name());?

Thanks.

[Igor Polevoy]

No, ActiveJDBC is lazy by default. Imagine you have DOCTORS - PATIENTS many to many relationship. A doctor treats many patients, and a patients visits many doctors. 

If ActiveJDBC were eagerly loading data, you would read a patient, and his/her doctors, than you would read these doctors' patients, than those patients' doctors, etc, until you have entire database in memory, lol!

ActiveJDBC only loads what you ask. Take a look at this page: http://javalite.io/lazy_and_eager

tx