This is the second post in a short series on the topic.
The last post
looked at the tables GROUPS and TEAMS in the OpenFootball relational
database schema. There is also the table GROUPS_TEAMS, usually known as a
link table, which, ahem, "relates" the GROUPS and TEAMS table. GROUPS_TEAMS
has the following schema:
A row in GROUPS_TEAMS with group_id of XXX and team_id of YYY means that
the team represented by team_id YYY is in the group with group_id XXX.
Let's modify the Smalltalk class OFGroup to handle the linkage, by adding
the inst-var 'teams' and creating accessors for it.
Next, modify the mapping for OFGroup in OFDescriptorSystem:
It is now necessary to add the table GROUPS_TEAMS to OFDescriptorSystem:
Now let's fetch the OFGroup instances with their linked OFTeam instances.
The above snippet produces the following output:
In the snippet, logging is enabled, and the SQL generated by Glorp is
displayed in the Transcript (with whitespace inserted for readability).
What we see is the infamous "N+1 selects problem" in action - the first
SELECT fetches the GROUPS rows, then, for each group_id, there is a
corresponding SELECT to fetch the TEAMS rows.
Fortunately Glorp is cleverer than this, and provides a way to avoid the
N+1 problem, by using the message #alsoFetch:.
Same output as before, but this time the SQL (pretty-printed by hand for
readability) is much shorter and properly takes advantage of the SQL