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!

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...