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:
CREATE TABLE IF NOT EXISTS "groups_teams" (
"id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
"group_id" integer NOT NULL,
"team_id" integer NOT NULL,
"created_at" datetime NOT NULL,
"updated_at" datetime NOT NULL
);
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.
OFObject subclass: #OFGroup
instanceVariableNames: 'eventId title pos teams'
classVariableNames: ''
package: 'OpenFootball'
Next, modify the mapping for OFGroup in OFDescriptorSystem:
classModelForOFGroup: aClassModel
self virtualClassModelForOFObject: aClassModel.
aClassModel newAttributeNamed: #eventId type: Integer.
aClassModel newAttributeNamed: #title type: String.
aClassModel newAttributeNamed: #pos type: Integer.
"Next item is for linking OFGroup with OFTeam."
aClassModel newAttributeNamed: #teams collectionOf: OFTeam.
descriptorForOFGroup: aDescriptor
| t |
t := self tableNamed: 'GROUPS'.
aDescriptor table: t.
self virtualDescriptorForOFObject: aDescriptor with: t.
(aDescriptor newMapping: DirectMapping)
from: #eventId
type: Integer
to: (t fieldNamed: 'event_id').
(aDescriptor newMapping: DirectMapping)
from: #title
type: String
to: (t fieldNamed: 'title').
(aDescriptor newMapping: DirectMapping)
from: #pos
type: Integer
to: (t fieldNamed: 'pos'.
"Next item is for linking OFGroup with OFTeam."
(aDescriptor newMapping: ManyToManyMapping)
attributeName: #teams.
"No change to #tableForGROUPS:."
It is now necessary to add the table GROUPS_TEAMS to OFDescriptorSystem:
tableForGROUPS_TEAMS: aTable
| gid tid |
self virtualTableForOFObject: aTable.
gid := aTable createFieldNamed: 'group_id' type: platform integer.
aTable addForeignKeyFrom: gid to: ((self tableNamed: 'GROUPS') fieldNamed: 'id').
tid := aTable createFieldNamed: 'team_id' type: platform integer.
aTable addForeignKeyFrom: tid to: ((self tableNamed: 'TEAMS') fieldNamed: 'id').
Now let's fetch the OFGroup instances with their linked OFTeam instances.
| vh |
Transcript clear.
OFDatabase dbFileName: 'wc2018.db'
evaluate: [ :db |
db session accessor logging: true. "This shows the generated SQL."
vh := String streamContents: [ :str |
(db session read: OFGroup) do: [ :ea |
str nextPutAll: ea title; nextPut: Character cr.
ea teams do: [ :team |
str nextPutAll: '- ', team title; nextPut: Character cr ]]]].
vh
The above snippet produces the following output:
Group A
- Egypt
- Russia
- Saudi Arabia
- Uruguay
<some output omitted>
Group H
- Senegal
- Japan
- Poland
- Colombia
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.
SELECT t1.id, t1.created_at, t1.updated_at, t1.event_id, t1.title, t1.pos
FROM GROUPS t1 an OrderedCollection()
SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
FROM TEAMS t1, GROUPS_TEAMS t2
WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?)) an OrderedCollection(1)
SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
FROM TEAMS t1, GROUPS_TEAMS t2
WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?)) an OrderedCollection(2)
SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
FROM TEAMS t1, GROUPS_TEAMS t2
WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?)) an OrderedCollection(3)
SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
FROM TEAMS t1, GROUPS_TEAMS t2
WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?)) an OrderedCollection(4)
SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
FROM TEAMS t1, GROUPS_TEAMS t2
WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?)) an OrderedCollection(5)
SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
FROM TEAMS t1, GROUPS_TEAMS t2
WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?)) an OrderedCollection(6)
SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
FROM TEAMS t1, GROUPS_TEAMS t2
WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?)) an OrderedCollection(7)
SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
FROM TEAMS t1, GROUPS_TEAMS t2
WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?)) an OrderedCollection(8)
Fortunately Glorp is cleverer than this, and provides a way to avoid the N+1 problem, by using the message #alsoFetch:.
| vh |
Transcript clear.
OFDatabase dbFileName: 'wc2018.db'
evaluate: [ :db |
| query |
db session accessor logging: true.
query := Query read: OFGroup.
query alsoFetch: [ :ea | ea teams ]. " <== See me. "
vh := String streamContents: [ :str |
(db session execute: query) do: [ :ea |
str nextPutAll: ea title; nextPut: Character cr.
ea teams do: [ :team |
str nextPutAll: '- ', team title; nextPut: Character cr ]]]].
vh
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 language.
SELECT t1.id, t1.created_at, t1.updated_at, t1.event_id, t1.title, t1.pos,
t2.id, t2.created_at, t2.updated_at, t2.key, t2.title
FROM GROUPS t1
INNER JOIN GROUPS_TEAMS t3 ON (t1.id = t3.group_id)
INNER JOIN TEAMS t2 ON (t3.team_id = t2.id)
ORDER BY t1.id an OrderedCollection()
Tags: Glorp, SQLite