[FeatureRequest] Allow aggregating functions over models with joins

[PhilippMDoerner]

I fairly often have usecases such as this:

Model A has an FK to Model B. I want to count all model A's who are associated with a Model B with a specific id.

Currently, so far as I can see, I can't do this in norm, as norm fetches the table from the model A and then ignores that the model A might explicitly imply you should be doing an INNER JOIN to the table of model B (which it would normally perform on a select query).

I'm currently doing this in raw sql, and it's not a big deal, but it would be convenient to have the joins happens if I supply models with explicit FK fields.

I'm willing to take a stab at this myself, once my 2 other PR's are merged, as I don't want to start even more work until that one is over.

[moigagoo]

Can't you use count proc with a condition to do that? The condition can reference the fk column.

[PhilippMDoerner]

Turns out that's the case and I overlooked that part on the count API.

[PhilippMDoerner]

Wait, nope, I forgot the actual usecase. I'm talking about that you want to have a where condition that accesses fields on another table.

Here an example. I have these 2 models:

import norm/[model, sqlite, pragmas]

type Campaign* {.tableName: "wikientries_campaign".} = ref object of Model
    name* {.unique.}: string


type Creature* {.tableName: "wikientries_creature".} = ref object of Model
    name*: string
    campaign_id* {.fk: Campaign.}: int64

And I want to count all creatures of the campaign whose name is Aldrune. I only have the name, I do not have the id of that campaign. In SQL I would write:

SELECT COUNT(*)
FROM wikientries_creature creature
INNER JOIN wikientries_campaign campaign ON creature.campaign_id = campaign.id
WHERE campaign.name = ?

In norm I have no way to express this.

Using a model such as

type CreatureRead* {.tableName: "wikientries_creature".} = ref object of Model
    name*: string
    campaign_id*: Campaign

Won't help either, since that still won't generate you the join you need for the query you want. However, to be honest, I'm not sure how this one would be tackled.

My thought is that maybe this feature could be left out if a proc such as fromRow proc suggested in #133 existed, since I'm not sure how complex this is and whether it would be worth the effort to implement.

[moigagoo]

Now I understand what you mean, thanks.

I think, in cases like this, when you deliberately decide to do your foreign keys manually, you should just use vanilla SQL to do your counting. If you want to make use of the count proc, you should also trust Norm to do your foreign keys. So, basically use campaign: Campaign instead of campaignId: int64.

Supporting manually linked tables basically means supporting the entire SQL syntax at some point.

[PhilippMDoerner]

I can agree with this. Furthermore, with the merging of #164 there is a way that isn't all too complicated to do this kind of thing fairly easily using rawSelect.

Thus I'm closing this issue.