Bidirectional relationships and more control over relationships in relational DBs

[jmikrut]

Right now, in Postgres, all relationships for a given document are stored in a single join table, which is for performance and simplicity reasons. This way, any number of relationship fields across a document, at any level or within any sub-field structure, are stored and retrieved in a single join.

But for relations that are hasMany: false and not polymorphic (i.e. have a single relationTo), you might want to have a more expected database structure where rather than storing a relation in the existing join table, you instead just store the related ID directly on the row in the relevant table.

We should expose a set of options to allow for more fine-grained control here.

There might be some other cases where we can add flexibility. Let's discuss it here.

EDIT:

Bidirectional Relationships are also planned.

Related:
#4163

EDIT:
RELEASED IN 3.0

[paulpopus]

Note on this, some people have asked about supporting junction tables as well for many-to-many relationships

[abass]

Yes! This please 🙏

[jesse996]

any progress?

[abass]

Yes, this is the main thing preventing us currently as well, any update on ETA/Status update would be super appreciated 🙏

[nachomglz]

any updates on this? it's been really a pain to work around this having to create custom sql queries.

[andershermansen]

Join fields are available in latest 3.0 beta. Check the documentation here: https://payloadcms.com/docs/beta/fields/join