One-to-Many

[Erhannis]

It seems to me more common for a data model to have one object with lists of children, than to have many objects referring to a single common object. I suspect most people more readily think of "w contains x, y, and z" than "x, y, and z each are contained by w". (Though my phrasing is a little leading.) However, SQL et. al. don't easily support the w-contains-list phrasing (because of having a static number of columns, basically), and the other way around is easier - x, y, z have a reference to w. But, they're more-or-less equivalent.

Ormar supports both sides to a certain extent - given a ForeignKey field, it automatically generates a corresponding List on the linked object. But:

1. I've had no luck with e.g. other_things: List[OtherThing] = ormar.ForeignKey(OtherThing) - there's no errors, but it only seems to store one thing. (Maybe ForeignKey(List[OtherThing])?)
2. In the docs, under Relations, it says "note that you DO NOT define it yourself", which somewhat suggests you CAN'T define the relationship from the other direction.
3. I've had some problems with doing e.g. thing.other_things = [ot1, ot2, ot3]. I suspect there's a bug involved, here - the objects on both side of the relationship act like the assignment was a success, but despite saving all parties, the ...oh, wait. I saved the children, set the list, saved the parent. Since the relationships are stored on the children, you have to update() the children after the assignment. I think I agree with this, for the sake of avoiding the usual ORM problem of "when the heck do things actually update", but it's a bit confusing if you're not rigorously thinking about it the right way around. Perhaps there could be a note somewhere in the documentation, as a reminder? Of course, I dunno if I'd have seen it. There could ALREADY be such a note - I get a little lost in the documentation, sometimes (though I appreciate the amount of information therein).

The data model I'm building ATM feels like it's halfway inside out - parts of it have relationships defined from the opposite way than makes natural sense to me. Questions:

1. Is it possible to define a one-to-many relationship in terms of a List field?
2. Perhaps what I want is a Many-to-Many? (Not that I truly want a many-to-many, but it may allow me to define the relationship in the direction I want.)
3. At what point is a many-to-many table updated? When add() etc. is called, or when the object virtually containing the field is update()d? (I'm assuming that many-to-many is accomplished by having a bunch of 1-1 entries in the separate table, and the actual entities visibly involved in the relationship don't actually have any relevant columns.)
4. Is there a way to omit fields when serializing to JSON? (E.g., as a response for a FastAPI endpoint. Maybe this is a FastAPI question.)
5. Mostly-related - is there a way to load all the fields at once? For instance, if I call thing.load(), thing.other_things remains empty, but if I call thing.other_things.all(), it populates. I'd like to not have to call all() on every field. (Preferably there'd be an option for whether you wanted it to recursively load.)

[Erhannis]

Also - is there a built-in way of doing ordered lists? Or do we have to manually do that, with e.g. an "order" field?

Also, question 4 - yeah, this is a FastAPI question. The answer is response_model_exclude and response_model_include.

[Erhannis]

Re: question 5, it might be good if there were a way to propagate a recursive-load across both all and load, since they both fill the same role, here.

[collerek]

Ok a lot of questions :)

Ad.1. No you cannot explicitly define a relation from reverse side, it's done for you when you declare a relation (foreignkey field) from child model like in sql database -> the model that has the actual foreign key side (so many-to-one). But you have this relation (one-to-many) constructed for you by ormar.

Btw. I think I never saw a orm when you declare a ForeignKey from reverse side. (i.e. in sqlalchemy.orm you can declare relation from any side (or both) but the foreign key is always on child side, and ormar don't use explicit relations declaration)

When you want a list from thing to other things:

Thing(ormar.Model):
# skip Meta for brevity
id: int = ormar.Integer(primary_key=True)
# you don't declare a "list" fk here

OtherThing(ormar.Model):
# skip Meta for brevity
id: int = ormar.Integer(primary_key=True)
# you declare a fk here and can customize the name present in Thing with related_name
thing: Optional[Thing] = ormar.ForeignKey(Thing, related_name="other_things")

#that way Thing have a field "other_things" which is a list of OtherThings (well actually it's a QuerysetProxy).
# so you can
thing = await Thing(id=1).save()
await thing.other_things.all() # <= will populate other_things etc. All methods of QuerysetProxy are available (check the docs) 

So you have both directions available and present, just the declaration is from one side. You do not declare relationships etc. like in example in sqlalchemy. ForeignKey is always declared from child side and ormar, the many-to-one is always virtual anyway, as the physical fk value is present in child model.

Ad.2. Many to many is, well, a many to many -> so a list (QuerysetProxy) is available from both sides, I guess that's not what you want in this case.

Ad.3. For many to many the through model (so the one linking related models) is saved when you call add() or when you call create() through QuerysetProxy. With add() both models (the one being added and one that you add to) needs to be already saved, with create() you can have only one of them and the second one will be created.

Rule of thumb is that operations that require await are doing something with the actual database.

Ad.4. Yes it's fastapi realeted but the option you mention are not the only ones. You should read #101 as there it's explained. This discussion will in time be used to expand fastapi part of the ormar docs.

Ad.5. QuerysetProxy methods (like all()) and load() should be used only when you want a partial load (so own columns or one of the relations), as usually when you want to refresh the whole object with related models that's not the way you really want to do this. That is because even if I implement recursive load with all and load (now it's impossible) you will get a query for own columns with load and then separate query for each of the relations. And what if you have 10 relations but you know that 5 of them are empty, you will get a query run for them anyway (so would probably have to have an option to include/exclude fields). And even than for one object, that usually do not have a vast amount of related objects, running a separate query for each relation is not beneficial (usually it's the slowest part of the process).

If you want to refresh all data including related models you should just use QuerySet methods:

# when you have a existing Thing thing1
thing1 # already exists
# refresh all with queryset
thing1 = await Thing.objects.select_related("other_things").get(pk=thing1.pk)  
# now thing1 is refreshed with all related other_things present and only one query was run (join was constructed for you)

That way you select which related models to fetch (add them to select_related or not) and moreover you can include/exclude fields with fields() and exclude_fields() methods for both main model and related models.

Note that if you want to issue a separate queries for each of related models you can do it with prefetch_related instead of select_related. For comparison please read joins section in queries docs.

As for the remark 3 from first 3 points, when you assign a list to other side of fk the relations are created but nothing is saved yet. Remember the rule of thumb that operations that require await are doing something with the actual database. Since the physical fk is saved on child side normally you would have to update each of the child models separately like you noticed.

But, if you want to save children models from parent side you actually can -> that's the save_related() method of the ormar.Model, please read the corresponding docs.

BTW. Would appreciate if you split your questions/remarks in more manageable pieces (maybe several discussions even), as i.e. excluding fields in response is not something directly related to one-to-many as the title suggest (and you have points 1-3 two times :D). It will be easier for me to answer and will be easier to find by other users in the future if they have similar questions.

[Erhannis]

Re: 1. Yeah, I know the relationship shows up as a field on both sides of the relationship - but the way I have to write the model is still inside-out from what the model is supposed to mean, which makes it hard to think about. (An example of an ORM behaving in the desired manner - in Java, we have a Set field, annotated with javax.persistence.OneToMany, and EclipseLink creates a mapping table between the classes. The other objects don't even know their owner exists.) It's not a huge problem - I can still get my goal accomplished. It's just harder, is all, and it would be nice if I could write the model right-side-out.

Re: 2. I may still try many-to-many, in the same way EclipseLink above technically uses a many-to-many to accomplish a one-to-many. I'll let you know how it goes, if I remember.

Re: 3. Good to know; thanks.

Re: 4. Thanks; I'll use those for reference.

Re: 5. It's good to know about the one call vs. many call issue. However, select_related doesn't actually solve my problem - as far as I can tell from the docs, I'd still need to explicitly name each field I wanted to load. If I were to add a field to the class, I'd have to hunt down all the places I wanted the whole model loaded, and add the field name to the list. (Or jerryrig something equivalent, like a static FIELDNAMES field on the class. Better, but still requires keeping two things in sync.) Does select_related allow you to load subfields, as well? Like thing1.otherthings[3].morethings[2]. It still seems to me like a reasonable request to "load the whole tree for this object", without having to manually do the process yourself - even if it does take multiple db calls.

Good to know about save_related(), thanks.

Re: BTW - sorry; I'll try to split future questions into separate threads, as applicable. I can go back and split THIS post into multiple threads, if you'd like, or just leave it since much of it is done or nearly done. Whatever you think.

[collerek]

Re.1 Ok I get your way of thinking, but you would like to just define the relation from other side or some kind of table linking like you described in Java?

Re.2 You can, but remember that even if one side of the relation will have one model, so the link is many to one, both sides will return a list on access. So for one model it will be always one element list.

Re.5. Yes you can go deep down with select_related, you use double underscore in list of joins so when you select from thing in your example you can select_related("other_things__more_things") with relation names.

As for the list of relations there actually is a method extract_related_names that returns a set of all field names with relation (fk and m2m). It's not documented as it's designed for internal purpose, you can also write your own that will iterate through Model.Meta.model_fields dictionary to extract the names that you will pass to select related, so this one can be solved without keeping the manual list of fields.

You can open an enhancement issue for load_all() that will load the whole tree as it's not a huge problem but it will face the same limitation assave_related so it won't visit models that were already visited when they are deeply nested to avoid circular infinite loop. But I still think it's not really efficient, and should be avoided if possible.

Or wait, maybe load all can be actually performed with use of queryset and generated list of relations, hmm, open the issues please and I will think about this.

For now let's just leave it as it is in one discussion.

[Erhannis]

Re.1. As the library-user - basically, it doesn't really matter to me how it's done under the hood as long as I never notice anything weird. I don't intend to mess with the database directly, so it doesn't matter whether there's a table or a foreign key. I tested the many-to-many thing, btw, and it seems to work as I'd like. I wrote a helper function to reduce the amount of boilerplate necessary, too:

def link(tblname: str):
    class Link(ormar.Model):
        class Meta(BaseMeta):
            tablename = tblname
    return Link
 
class CC(ormar.Model):
    class Meta(BaseMeta):
        tablename = "ccs"
    id: UUID = ormar.UUID(primary_key=True, default=uuid4)
    name: str = ormar.Text(default="")
    contents: str = ormar.Text(default="")
 
class CB(ormar.Model):
    class Meta(BaseMeta):
        tablename = "cbs"
    id: UUID = ormar.UUID(primary_key=True, default=uuid4)
    name: str = ormar.Text(default="")
    ccs: List[CC] = ormar.ManyToMany(CC, through=link("link_bc"))
 
class CA(ormar.Model):
    class Meta(BaseMeta):
        tablename = "cas"
    id: UUID = ormar.UUID(primary_key=True, default=uuid4)
    name: str = ormar.Text(default="")
    cbs: List[CB] = ormar.ManyToMany(CB, through=link("link_ab"))
 
@app.post("/test/nested")
async def post_test_nested():
    ca = await CA(name="ca0").save()
    cb = await CB(name="cb0").save()
    cc = await CC(name="cc0", contents="blah blah blah").save()
    await ca.cbs.add(cb)
    await cb.ccs.add(cc)
    return (ca, cb, cc)

Using a table means you don't run into the weirdness of "the field is conceptually stored on A, but the relationship is stored on B", and attendant confusion about when to save what. This leads into another thought I had, but I'll make that a separate discussion.

Re.2. I don't actually want the reverse link (which is the separate discussion I mention above), so it doesn't matter to me whether it's an object or a list.

Re.5. I'll look into extract_related_names, thanks - like, unless it becomes unnecessary in the course of discussion. Speaking of, I'll go open a feature request. Thanks

[collerek]

Re.1 I'm working on possibility to add more firlds on through table (link in your example) and probably this through parameter will become optional if you don't want to add more fields.