Handling relationships with the Data Mapper pattern

Question

I am using the Data Mapper Pattern and I am wondering what is the best way to handle relationships with that pattern. I spent a lot of time searching for solutions in Google and Stack overflow, I found some but I am still not completely happy about them, especially in one special case that I will try to explain.

I am working with PHP so the examples of code that I will put are in PHP.

Let's say I have a table "team" (id, name) and a table "player" (id, name, team_id). This is a 1-N relationship. By implementing the Data Mapper pattern, we will have the following classes: Team, TeamMapper, Player and PlayerMapper.

So far, everything is simple. What if we want to get all players from a team?

The first solution I found is to create a method getAllPlayers() in the Team class which will handle that with lazy loading and proxies. Then, we can retrieve the players of a team like that:

$players = $team->getAllPlayers();

The second solution I found is to directly use the PlayerMapper and pass the team ID as parameter. Something like:

$playerMapper->findAll(array('team_id' => $team->getId()));

But now, let's say that I want to display a HTML table with all the teams and with a column 'Players' with all of the players of each team. If we use the first solution I described, we will have to do one SQL query to get the list of teams and one query for each team to get the players, whcih means N+1 SQL queries where N is the number of teams.

If we use the second solutions I described, we can first retrieve all team IDs, put them in an array, and then pass it to the findAll method of the player mapper, something like that:

$playerMapper->findAll(array('team_id' => $teamIds));

In that case, we need to run only 2 queries. Much better. But I am still not very happy with that solution because the relationships are not described into the models and it is the developer who must know about them.

So my question is: are there others alternatives with the Data Mapper pattern? With the example I gave, is there a good way to select all teams with all players in just 2 queries with the description of the relationships into the model?

Thank you in advance!

Condensed

Answer 1

If you look at Martin Fowler's text that describes how the DataMapper works, you'll see that you can use one query to get all the data that you need and then pass that data to each mapper, allowing the mapper to pick out only the data that it needs.

For you, this would be a query that joins from Team to Player, returning a resultset with duplicated Team data for each unique Player.

You then have to cater for the duplication in your mapping code by only creating new objects when the data changes.

I've done something similar where the equivalent would be the Team mapper iterating over the result set and, for each unique team pass the result set to the Player mapper so that it can create a player and then add the player to the team's collection.

While this will work, there are problems with this approach further downstream...

Thriller

Which Martin Fowler's text do you refer to? The only one I found is that one (martinfowler.com/eaaCatalog/dataMapper.html) and I don't see anything about what you say. Anyway, this approach doesn't convince me. It means that you have to do a query returning NxM rows (with N the number of teams and M the number of players) with a lot of duplicated data. And it also means mapping the players through the Team mapper which doesn't sound so good. And as you said, I am sure there are problems with this approach further downstream... – Condensed

It's in the PoEAA book. The website only provides an overview. Your query doesn't return NxM rows, only M rows. It does bring back a lot of data, but this is the point. Grab as much in one round trip as possible. Furthermore, your Team mapper doesn't map Players, it asks the Player mapper to map Players for it. – Thriller

Yes, sorry, M rows. Well, that's a solution but I am still not completely convinced. Let's see if my question will bring more answers... – Condensed

@Vincent: You are not completely convinced, yet don't say why - having not accepted the answer, this comment is not useful to those who come seeking in the future. – Goliath

@DavidOsborne: what are the problems further downstream? – Goliath

I introduced a flaw by mapping child objects as part of the parent mapping process. This is fine until you have bidirectional mappings and you create an infinite loop! I think ORMs like NH get around this by doing the mapping and then linking the related objects. – Thriller

I am not so convinced mainly because using one big query to get relations doesn't sound like a clean and optimized solution to me (we get duplicated data). It is maybe OK if the table "team" has only the relation to the table "player" but if the table "team" has many more relations to other tables, then we get more and more duplicated data and a heavy SQL query. Also, I think the proposed solution may sound easy when explained in a few sentences but the actual implementation is very complex and, as said, brings problem further downstream. – Condensed

This is why no one writes their own ORM! blog.codinghorror.com/… – Thriller

Answer 2

I have a possible solution to this problem that I have implemented successfully in one of my projects. It is not so complex and would use only 2 queries in the example described above.

The solution is to add another layer of code responsible for handling relationships.

For instance, we can put that in a service class (which can be used for other stuff as well, not only handling relationships). So let's say that we have a class TeamService on top of Team and TeamMapper. TeamService would have a method getTeamsWithRelationships() which would return an array of Team objects. getTeamsWithRelationships() would use TeamMapper to get the list of teams. Then, with the PlayerMapper, it would get in only one query the list of players for these teams and set the players to the teams by using a setPlayers() method from the Team class.

This solution is quite simple and easy to implement, and it works well for all types of database relationships. I guess that some people may have something against it. If so, I would be interested to know what are the issues?

Condensed