I have 3 tables:
person (person_id, person_name),
game (game_id, game_name)
and the linked table
play(person_id, game_id, score).
With ActiveJdbc I use many2many annotation and it works fine to get all games for 1 person
List<Game> games = person.get(Game.class, "person_id = ?", personId);
My question is how to get the value "score"?
I tried game.getScore() but it obviously didn't work
edit: I want the complete list of game from 1 person. Here is the result I want
game1 | 21
game2 | 33
game3 | 44
In SQL in should be something like:
select game.name, play.score
from game join play on (game.game_id = play.game_id)
join person on (game.person_id = person.person_id)
where person_id = 1;
There is probably more than one way of doing this, but here is a way. You can think of Many-to-Many relationship as two One-to-Many relationships, where Play is a child of both, Person and Game. Then you approach from a different angle:
List<Play> plays = Play.where("person_id = ?", 1).include(Person.class, Game.class);
By using include() you are also optimizing and running fewer queries: http://javalite.io/lazy_and_eager.
Depending on your query conditions, you might have just one play:
Game game = plays.get(0).parent(Game.class);
Person = plays.get(0).parent(Person.class);
If course, you'd be able to get to your score:
int score = plays.get(0).getScore();
hope it helps!