[FeatureRequest] Could there be a proc that automatically fetches all "Many" entries of a "One-to-many" relationship ?

[PhilippMDoerner]

However, Norm may be mature enough to provide a nicer interface for one to many relations. What if we add a proc that returns a seq if objects to types which are referenced as foreign keys in other types? Something like getMany that accepts two types, the "one" type and the "many" type.

This was written back in 2019 as an off-hand comment, but I think this is still a really good idea.... particularly because I'm running into a situation where it'd be really useful 😄

I'd only specify that I think that the "one"-side of the parameters should be an instance, not just the type itself, so that you automatically have the ID of the one-side to query the many side with.
This could be a proc:

proc getMany[T: Model](one: Model, many: typedesc[T]): seq[T] =

The proc could scan the provided type T for the FK field to "one" and build an SQL query on T's table accordingly.
Would this be something easy to implement?

[PhilippMDoerner]

I've made some pretty decent headway in this regard. You CAN have a function that fetches the "many" side on a one-to-many relationship, assuming you have the "one" instance. You can do it with these 3 generic procs:

(Be careful: newModel just has to be defined for the given model-type you pass in. You effectively give the proc a model-type and it gives you back an instance of that model. Here an example proc signature for the model CharacterEncounterRead proc newModel*(T: typedesc[CharacterEncounterRead]): CharacterEncounterRead = <Your implementation to return a CharacterEncounterRead-Model-instance>)

##[This function fetches the name of a field if it is an FK-Field and points to the Model `targetType`]##
proc getRelatedFieldName[M: Model, O:Model](targetType: typedesc[O], sourceType: typedesc[M]): Option[string] =
    let source = sourceType()
    for sourceFieldName, sourceFieldValue in source[].fieldPairs:
        #Handles case where field is an int64 with fk pragma
        when sourceFieldValue.hasCustomPragma(fk):
            when O.table() == sourceFieldValue.getCustomPragmaVal(fk).table():
                return some(sourceFieldName)
        
        #Handles case where field is a Model type
        when sourceFieldValue is Model:
            when O.table() == sourceFieldValue.type().table():
                return some(sourceFieldName)
        
        #Handles case where field is a Option[Model] type
        when sourceFieldValue is Option:
            when sourceFieldValue.get() is Model:
                when O.table() == genericParams(sourceFieldValue.type()).get(0).table():
                    return some(sourceFieldName) 

    return none(string)

##[Just a wrapper around `getRelatedFieldName` to deal with the option that you might not find a field ]##
proc getForeignKeyFieldNameOn*[M: Model, O:Model](oneModel: typedesc[O], manyModel: typedesc[M]): string =
    let fieldOption: Option[string] = getRelatedFieldName(oneModel, manyModel)
    if fieldOption.isNone():
        let errorMsg = "Tried getting foreign key field from model '" & name(manyModel) & "' to model '" & name(oneModel) & "' but there is no such field!"
        raise newException(FieldDefect, errorMsg)
    
    return fieldOption.get()


##[ This generic function takes the instances whose related Models of type M you want to fetch ]##
proc getManyFromOne*[O: Model, M: Model](oneEntry: O, relatedManyType: typedesc[M]): seq[M] =
    mixin newModel

    let db: DbConn = getDatabaseConnection()
    var targetEntries: seq[relatedManyType] = @[newModel(relatedManyType)]

    let oneTableName: string = oneEntry.type().table()
    var foreignKeyFieldName: string = oneEntry.type().getForeignKeyFieldNameOn(relatedManyType)

    let manyTableName: string = relatedManyType.table()
    let sqlCondition: string = manyTableName & "." & foreignKeyFieldName & " = ?"
    db.select(targetEntries, sqlCondition, oneEntry.id)

    result = targetEntries

Some minimal manual testing code for the above functions as I haven't yet bothered to write tests

type
    A = ref object of Model # <-- has implicit tableName "A"
        name: string
    AC {.tableName: "A".} = ref object of Model
        myothername: string
        name: string

    B = ref object of Model # <-- has implicit tableName "B"
        name: string
        myA: Option[A]
    D = ref object of Model
        myothernameid: string
        myDA: A
    E = ref object of Model
        myotherbool: bool
        myEA {.fk: A.}: int64


    
echo A.getRelatedFieldName(B) # some("myA")
echo AC.getRelatedFieldName(B) # some("myA")
echo A.getRelatedFieldName(D) # some("myDA")
echo AC.getRelatedFieldName(D) # some("myDA")
echo A.getRelatedFieldName(E) # some("myEA")
echo AC.getRelatedFieldName(E) # some("myEA")

[PhilippMDoerner]

In fact, with the help of getForeignKeyFieldNameOn I even managed to write something up to fetch an entire many-to-many relationship where you just need to define a model from which to start on and the Model of the join-table. Which will return you a seq[otherManyModel], not a seq[joinTableModel] .

From there it'll do the query to the database on the join Table and automatically also fetch the data for the otherManyModel given how norm itself does select statements with FK relationships, which gives you a seq[joinTableModel].

A macro I got essentially gifted from ElegantBeef then helps generically "unpack" that seq[joinTableModel] to seq[otherManyModel].

macro unpackFromJoinModel*[T: Model](mySeq: seq[T], field: static string): untyped =
    newCall(bindSym"mapIt", mySeq, nnkDotExpr.newTree(ident"it", ident field))

proc getManyToMany*[MS: Model, J: Model](queryStartEntry: MS, joinModel: typedesc[J], foreignKeyField: static string): seq[untyped] =
    mixin newModel

    let db = getDatabaseConnection()
    var joinModelEntries: seq[joinModel] = @[]
    joinModelEntries.add(newModel(joinModel))

    let fkColumnFromJoinToManyStart: string = queryStartEntry.type().getForeignKeyFieldNameOn(joinModel)
    let joinTableName = joinModel.table()

    let sqlCondition: string = joinTableName & '.' & fkColumnFromJoinToManyStart & " = ?"
    db.select(joinModelEntries, sqlCondition, queryStartEntry.id)

    let manyEntries = unpackFromJoinModel[J](joinModelEntries, foreignKeyField)
    result = manyEntries

For the most part to get a feel for the macro, here some example code (Take the types of A and D from the above comment):

macro mapModel[T: Model](mySeq: seq[T], field: static string): untyped =
    newCall(bindSym"mapIt", mySeq, nnkDotExpr.newTree(ident"it", ident field))
    

var myDSeq: seq[D] = @[]
let anA: A = A(name: "this is an A")
myDSeq.add(D(myothernameid: "la", myDA: anA))
myDSeq.add(D(myothernameid: "le", myDA: anA))


echo %*myDSeq

let myASeq: seq[A] = mapModel(myDSeq, "myDA")

echo %*myASeq

[moigagoo]

Looks cool! A few motes before I'll ask you to submit a PR:

1. Don't rely on newModel or similar “contructor” proc being defined. Instead, rely on object instances. There is no universal way to initialize a type in Nim, and even demanding a newModel proc to exist won't do: there are other ways to initialize an object. One is just call the type as proc, the other is how they do in in Status: proc init[T](_: typedesc[T]): T.
2. It seems like the mapModel macro can be replaces with a template or even a plain proc?
3. Please use two spaces to indentation.
4. let source = sourceType() won't work if sourceType has a DateTime in it. Again, use instances instead of types. I've already tried working with types in Norm 1, trust me, you don't want that :-)
5. You can use norm.model.isModel instead of cascading when to check is something is a Model.
6. I'm not really seeing how mixin is useful here. Even Nim's manual states that mixin makes sense in templates and macros only ¯_(ツ)_/¯

Anyway, I suggest you submit a PR and we'll discuss the code in the сщььуте.

[PhilippMDoerner]

Ah, that was more the code I had so far for my side-project (It's part of my "genericArticleRepository"), I hadn't yet taken the effort to rewrite it more to suit norm's philosophy since I wanted to first give a gist of how I tackled the problem for my side-project before I did a rewrite for norm, in case you had already plans for going in a different direction.

The key idea was, use getRelatedFieldName to scan at compile time for the table name of a given Model and, as a sample, how you can use that to construct generic ways to interact with the database (written in not yet the "norm" way of doing it). And how, through the use of the mapModel/ unpackFromJoinTable, you can use that to fetch a many-to-many relationship in a way that alleviates you of having to fetch the model you actually care about from the many-to-many model.

The mixin there is pretty much required due to the way I built my side-project, I can go on about that, but for the most part it's about which module has to import the modules that implement the various newModel procs. Using mixin allows you to not have to import them in the module-file within which getManyToMany etc are defined, but in other modules that call these procs. So if I have genericArticleRepository (that contains the above procs and doesn't import any module with a newModel proc) and characterRepository (which uses the above procs and imports modules with newModel procs), I can tell it (via mixin) to use the newModel procs imported in characterRepository instead of looking for them in genericArticleRepository.

It seems like the mapModel macro can be replaces with a template or even a plain proc?

I tried these approaches first. I only managed to make templates work if you call them directly and you have to give them the field, can't do that in string form or anything. As part of a generic function, it did not work, as that would mean the generic function would need to receive a field as some parameter somehow and pass that on to the template. I'm not sure that's possible.

Here how far I got with templates.

import std/[options, typetraits, json, sequtils]
import norm/[model, pragmas]
import macros

type
    A = ref object of Model 
        name: string
    D = ref object of Model
        myothernameid: string
        myDA: A

template mapModel[T: Model](mySeq: seq[T], field: untyped): seq[untyped] = mySeq.mapIt(it.field)

var myDSeq: seq[D] = @[]
let anA: A = A(name: "this is an A")
myDSeq.add(D(myothernameid: "la", myDA: anA))
myDSeq.add(D(myothernameid: "le", myDA: anA))
myDSeq.add(D(myothernameid: "li", myDA: anA))
myDSeq.add(D(myothernameid: "lo", myDA: anA))
myDSeq.add(D(myothernameid: "lu", myDA: anA))

echo %*myDSeq

let myASeq: seq[A] = mapModel(myDSeq, myDA)

echo %*myASeq

Notice how mapModel(myDSeq, myDA) works only because I pass it myDA ahead of time? I'm not sure how to fetch that field with a proc. Sadly, I only know how to fetch that field's name in a string with a proc, that one being getRelatedFieldName or whatever I tend to call it. And if all I have is a field's name as a string, then I could only think of solving this with a macro.

[PhilippMDoerner]

As a sidenote, if you have any inspiration for better names than "getManyToMany" or "getManyFromOne" I am very eager to hear about them. I'm none too happy with those names, they were just the best name that I could think of.

[PhilippMDoerner]

You can use norm.model.isModel instead of cascading when to check is something is a Model.

I played around a bit with this and couldn't find a way to make this work. The important thing is, that getRelatedFieldNameOn acts at compile time since that's where I wanted this all to run. However, isModel appears to not jive with that somehow. If I try this version of getRelatedFieldNameOn for example (I've already written up some tests for it that pass with the version using blank "is Model" checks):

proc getRelatedFieldNameOn*[T: Model, M: Model](targetModel: T, sourceModel: M): string =
  for sourceFieldName, sourceFieldValue in sourceModel[].fieldPairs:
    #Handles case where field is an int64 with fk pragma
    when sourceFieldValue.hasCustomPragma(fk):
      when targetModel.type().table() == sourceFieldValue.getCustomPragmaVal(fk).table():
        return sourceFieldName
    
    #Handles case where field is a Model type
    when sourceFieldValue.isModel():
      when targetModel.type().table() == sourceFieldValue.type().table():
        return sourceFieldName
    
    #Handles case where field is a Option[Model] type
    when sourceFieldValue is Option:
      when sourceFieldValue.get().isModel():
        when targetModel.type().table() == genericParams(sourceFieldValue.type()).get(0).table():
          return sourceFieldName

  raise newException(
    FieldDefect, 
    "Tried getting foreign key field from model '" & name(sourceModel.type()) & "' to model '" & name(targetModel.type()) & "' but there is no such field!"
  )

I receive compile errors like this:

cannot evaluate at compile time: sourceModel
tmodel.nim(8, 7): template/generic instantiation of `suite` from here
tmodel.nim(51, 8): template/generic instantiation of `test` from here
tmodel.nim(56, 11): template/generic instantiation of `check` from here

I can rewrite the above to use isModel(), but the only way I see to do that would mean that these calculations are going to run at runtime instead of compile-time, which I wanted to avoid.

[PhilippMDoerner]

Alright, I've got my core functions hammered down and got them tested under sqlite. There's one thing I'm not yet quite sure on, is how to deal regarding organizing said code. If you wish to look at the code, here's the branch of it in my repo: https://github.com/PhilippMDoerner/norm/tree/manytoxfetching

The entire PR that'll be made for this consists of getRelatedFieldNameOn, selectOneToMany(oneModelInstance, manyModelSeq) and selectManyToMany(manyModel1Instance, joinModelSeq, manyModel2Seq).
Strictly speaking, the procs that do one-to-X fetching are completely independent of the underlying database.
They need a select proc for me to work on top of and access to a defined DbConn type, that's it.

Here's the procs:

# Couldn't find a way to make getRelatedFieldNameOn work at compile time without solely using types instead of objects
#However, as this never accesses and field values, this proc does get away with it, even if there are DateTime fields
proc getRelatedFieldNameOn*[T: Model, M: Model](targetModel: typedesc[T], sourceModel: typedesc[M]): string  {.raises: [FieldDefect].} =
  for sourceFieldName, sourceFieldValue in sourceModel()[].fieldPairs:
    #Handles case where field is an int64 with fk pragma
    when sourceFieldValue.hasCustomPragma(fk):
      when T.table() == sourceFieldValue.getCustomPragmaVal(fk).table():
        return sourceFieldName
    
    #Handles case where field is a Model type
    when sourceFieldValue is Model:
      when T.table() == sourceFieldValue.type().table():
        return sourceFieldName
    
    #Handles case where field is a Option[Model] type
    when sourceFieldValue is Option:
      when sourceFieldValue.get() is Model:
        when T.table() == genericParams(sourceFieldValue.type()).get(0).table():
          return sourceFieldName

  raise newException(
    FieldDefect, 
    "Tried getting foreign key field from model '" & name(sourceModel.type()) & "' to model '" & name(targetModel.type()) & "' but there is no such field!"
  )

# One-to-Many Fetching

proc selectOneToMany*[O: Model, M: Model](dbConn; oneEntry: O, relatedEntries: var seq[M]) =
  const foreignKeyFieldName: string = O.getRelatedFieldNameOn(M)
  const manyTableName: string = M.table()
  const sqlCondition: string = "$#.$# = ?" % [manyTableName, foreignKeyFieldName]

  dbConn.select(relatedEntries, sqlCondition, oneEntry.id)

# Many-to-Many Fetching

macro unpackFromJoinModel*[T: Model](mySeq: seq[T], field: static string): untyped =
  newCall(bindSym"mapIt", mySeq, nnkDotExpr.newTree(ident"it", ident field))

proc selectManyToMany*[M1: Model, J: Model, M2: Model](dbConn; queryStartEntry: M1, joinModelEntries: var seq[J], queryEndEntries: var seq[M2]) =    
  const fkColumnFromJoinToManyStart: string = M1.getRelatedFieldNameOn(J)
  const joinTableName = J.table()
  const sqlCondition: string = "$#.$# = ?" % [joinTableName, fkColumnFromJoinToManyStart]
  dbConn.select(joinModelEntries, sqlCondition, queryStartEntry.id)

  const fkColumnFromJoinToManyEnd: string = M2.getRelatedFieldNameOn(J)
  let unpackedEntries: seq[M2] = unpackFromJoinModel(joinModelEntries, fkColumnFromJoinToManyEnd)

  queryEndEntries = unpackedEntries

@moigagoo A particular question I'd have at you:
The getRelatedFieldNameOn proc imo belongs to models, as it's a convenience function there and feels "right" there.
I'm unsure what to do with selectManyToMany and selectManyToOne, as imo they should go into a file separate from sqlite.nim and postgres.nim, because it'd just be the same code in both files otherwise and make extending to other Dbs (should that ever be done) more complex than it needs to be.
I'm just not sure how to tell the proc "Accept a DbConn-type object where DbConn is either the one defined in postgres.nim or the one defined in sqlite.nim". Do you have an idea on how to "generalize" that?

[PhilippMDoerner]

PR for this issue has been merged.