There are a few unrelated topics here, I'll cover them one by one.
**1\. One-to-many in Norm**
> . But it seems a little counter intuitive to me. If a purchase is composed of
> subpurchases, my natural approach would be to define a purchase object with a
> seq of subpurchases. But if I understand the model definitions correctly, I
> need to do the opposite: define purchase as a field of subpurchase.
This is how one-to-many relations are done with SQL. Norm doesn't add too thick
of an abstraction layer above SQL, so those relations are preserved as they
would be in SQL. Norm handles the foreign keys for you, so you define model
relations instead of field relations.
To better understand what Norm does, enable logging and see the generated SQL
queries:
import logging
addHandler newConsoleLogger()
import norm/sqlite, norm/model
type Foo* = ref object of Model
...
Run
When you run your sample with those additional two lines, you'll see that in
the console:
DEBUG CREATE TABLE IF NOT EXISTS "Foo"(str TEXT NOT NULL, val INTEGER NOT
NULL, id INTEGER NOT NULL PRIMARY KEY)
DEBUG CREATE TABLE IF NOT EXISTS "Foo"(str TEXT NOT NULL, val INTEGER NOT
NULL, id INTEGER NOT NULL PRIMARY KEY)
DEBUG CREATE TABLE IF NOT EXISTS "Bar"(foo1 INTEGER NOT NULL, foo2 INTEGER
NOT NULL, id INTEGER NOT NULL PRIMARY KEY, FOREIGN KEY(foo1) REFERENCES
"Foo"(id), FOREIGN KEY(foo2) REFERENCES "Foo"(id))
...
Run
As you can see, each model relation is turned into a FOREIGN KEY statement.
**2\. Better interface for one-to-many selections**
As you've already mentioned, the way SQL defines one-to-many relations is
counter intuitive: instead of saying “one purchase relates to many
subpurchases,” you have to say “any number of subpurchases can relate to a
single purchase.” The meaning is the same but the interface if kind of inverted
relative to what you expect.
In the purchases and subpurchases example, you probably want to be able to call
`somePurchase.subpurchases` to get all subpurchases of a particular purchase.
Luckily, Nim is awesome and lets us easily solve this problem. I haven't tested
this code but with something along those lines, you could get the desired
behavior:
# Add to app/models/subpurchase.nim
template subpurchases*(purchase: Purchase): seq[Subpurchase] =
@[newSubpurchase()].dup:
db.select(""""Subpurchase".purchase = $1""", purchase.id)
# Call when needed
withDb:
echo myPurchase.subpurchases
Run
In fact, I thought about adding automatic generation of such helper procs to
Norm but there's just too much work on the basics.
**3\. Issue with foo1 and foo2**
I think this is just a bug with `select`. The generated query selects the same
Foo row twice, hence the incorrect result:
DEBUG SELECT "Foo".str, "Foo".val, "Foo".id, "Foo".str, "Foo".val,
"Foo".id, "Bar".id FROM "Bar" JOIN "Foo" ON "Bar".foo1 = "Foo".id WHERE Bar.id
= ? <- [1]
Run
I'll take a look into that. If you don't mind, please report an issue on GitHub.
