I can't see any ways of calling a function when I select
What do you mean ?
Selecting in Norm is like this :
var db = open(dbName, "", "", "")
defer: close(db)
var modelInstance : ModelType = newModelType() # See readme for Model type definition & initialization
db.select(modelInstance, "id=$1", 0) # Select id 0
echo modelInstance # modelInstance is now populated
I think he tries to produce a query like this:
SELECT
avg(col)
FROM
my_table;
Using select will select a Model instance.
But you can execute sql queries with exec :
db.exec(sql"""SELECT col FROM my_table;"""
Could you please specify what problem you're solving? Do you want to get an average value of a certain column across multiple selected rows?
Norm doesn't have an interface for AVG yet, feel free to create an issue. PRs are very welcome too!
However, there are ways to work around that, with and without Norm:
2. Select the objects with Norm's select and calculate the average using Nim instead of SQlite:
import std/[stats, sugar]
withDb:
let
myObjs = @[MyModel()].dup:
db.select("Some Condition")
myAttrs = collect(newSeq):
for obj in myObjs:
obj.attr
myAvg = mean(myAttrs)
Yes, this is precisely what I want to do (the AVG example that is). I had thought I could just run the query myself, but wondered if the ORM would be able to do it for me. I definitely didn't want to have to calculate it all myself as that would be slow and wasteful.
OK, my next question is: is there a way to attach an "on delete cascade" condition to a column definintion? I'm guessing not, but it was in my original design before I started trying out various ORMs. I had to remove it when trying out ormin as it couldn't parse it, although it happily created the tables using it, but of course with norm it would probably require some sort of pragma being attached to a foreign key field.
is there a way to attach an "on delete cascade" condition to a column definintion
You're right, there is no way to do that now. It shouldn't be hard to implement though. I used to have in Norm 1.0, I think.
I've submitted a PR that adds ON DELETE support: https://github.com/moigagoo/norm/pull/109
With this change, you can add onDelete: "CASCADE" to your foreign key attrs to enable cascade deletion: https://github.com/moigagoo/norm/pull/109/files#diff-dd632f99e2ca269c5f2323bd5bf1320c1e95123064a80e417c336d877a2adac1R16
Note that foreign keys are disabled in SQLite by default, so you must call PRAGMA foreign_keys = ON manually at some point before running other queries (see https://github.com/moigagoo/norm/pull/109/files#diff-909c643b3e390ff415ab3c253f00628bffa79afde551708e78848d10a2984ac7R229).
Sounds good?