How do relations really work in Craft 5?

[mmikkel]

One thing I still haven't been able to fully get a handle on for Craft 5, is exactly how relations work in comparison to earlier versions.

Before Craft 5, relations were stored in the relations database table, period. Simple enough!

In Craft 5, relations are stored in the content JSON in the elements_sites table. Initially, I figured that the relations table was only kept around for backwards compatibility, and that going forward, new relations would only be stored in the elements_sites table. However, as of Craft 5.8 it seems that even new relations are now stored in two places; both in the JSON structures in elements_sites (where they are simple arrays of element IDs, keyed by the custom field layout element UID) and in the old relations table, just like in prior Craft versions.

AFAICT, the relations table has not been deprecated or changed in any way since Craft 4. So I'm a bit confused as to why relations are stored in two places, and how things really work 🫠

Specifically, I'm currently writing a plugin that is going to interface heavily with relations, and so I'd love to get some clarity on the following questions:

1. Is the relations table, and any/all functionality dependant on this table, deprecated and/or on its way out?
2. If not, what is the purpose of also saving related element IDs in the elements_sites JSON structures? I assume this has something to do with multi-instance relational fields, but in what way(s) do the relations in the two tables interface with each other?
3. My plugin integrates with a third party DAM. Authors can import files from this DAM into Craft, where each imported file is programmatically created as an asset in Craft. I keep track of "imported assets" in a custom database table with an FK on the assets.id column. The idea is to then be able to output usages for each imported asset, by querying the relations table. I don't really care about multi-instance fields (the "usages" will not need to specify which field an imported asset is related via) – all I need is to query for all elements related to a specific asset. For my use case, is it safe (both short- and long-term) to completely ignore the "relations" in the elements_sites table and only rely on the relations table to figure out which elements a specific asset is related to?

[brandonkelly]

We started storing the target element IDs in the content JSON in Craft 5.3, to get multi-instance support working for relation fields. They still store values in the relations table as well, but that table isn’t multi-instance aware, so now each individual field instance also stores its list of target element IDs in the content JSON. If the content JSON value is set, then the field will use that array when building its field value query. Otherwise it will fall back to joining in the relations table, like pre-5.3.

That said, the relations table is not (and won’t be) deprecated, even if it were no longer needed for fallback data, because it’s still referenced when fetching reverse relations.

For your use case, are you defining a new element type? Are the relations defined with a custom field that extends BaseRelationField/RelationalFieldInterface? If not, I wouldn’t recommend using the relations table, etc.

[mmikkel]

Aha! That makes things a lot clearer – thanks Brandon 🍻

For your use case, are you defining a new element type?

Nope. The assets are regular assets, all I'm doing is creating a reference to them in a custom table with an FK on assets.id, purpose being

1. to keep track of the assets created programmatically by "importing" a file from the DAM (as opposed to being manually uploaded by authors), and
2. to expose "usage" information for each imported asset in a custom endpoint that is consumed by the DAM. I.e. the DAM can query Craft for (essentially) a list of URLs where a specific image is "used". This is the part I'm currently relying on the relations table for (and only the relations table).

Are the relations defined with a custom field that extends BaseRelationField/RelationalFieldInterface

Yes, the relations are created via a custom field type, which extends the Assets field type. Essentially all the custom field type does is to provide a custom input template, which adds some JS triggering a custom modal for selecting a file from the third party DAM (instead of opening up the core element selector modal). When a file is selected in the custom modal, the JS triggers a controller action that downloads the file from the DAM, creates an asset, saves the asset ID to the custom "imported_assets" table, and finally the JS "selects" the created asset in the field (meaning that the actual relations are created by Craft core, just like w/ a native assets field).

[mmikkel]

...theoretically, though — if I needed to output a list of all field-instance-specific usages for a specific asset, as efficiently as possible — how would I approach that?

[brandonkelly]

There’s not currently any efficient way to output reverse relations for an element, for a specific instance. You’d need to just fetch all reverse relations, and filter out any where the instance you care about is not related to the target element. Or just make a special relation field that you remember should never be multi-instance.