PostgreSQL jsonb field in ActiveRecord belongs_to association

Question

Can I use a jsonb field as a foreign_key in a belongs_to association? Something along the lines of:

belongs_to :product, class_name: "Product",  foreign_key: "data ->'product_id'"

Malaguena

Answer 1

References can be defined only between columns (or groups of columns), as it is stated in the documentation:

FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]

Heterochromous

I honestly don't see how foreign key constraints limitations are relevant to the question... Yes, this would mean that you can't guarantee referential integrity, but I don't see how it means that rails couldn't still build a relationship if that's not a concern. SELECT * FROM products INNER JOIN instructions ON products.id = instructions.data->'product_id' would still be valid SQL without the FOREIGN KEY constraint, no? – Hipparch

@SampsonCrowley - The declaration refers to a foreign key. Per the documentation: (...) by declaring that one model belongs_to another, you instruct Rails to maintain Primary Key-Foreign Key information between instances of the two models. – Heterochromous

that still has nothing to do with the database itself; the keyword there being instances of the two models. If I create a table with a column product_id, without any form of FOREIGN KEY, rails has no problem using that column to create associations and the correct queries. I guess my point being your link to the PostgreSQL docs have nothing to to with the ability of rails belongs_to/has_many to configure and create associations, otherwise polymorphic associations would be impossible – Hipparch

The quoted sentence clearly shows that belongs_to is implemented by a reference (primary key - foreign key) in a database. Other associations may be implemented in a different way. – Heterochromous

Nope, it is in no way required to maintain referencial integrity at the DB level. I can 100% guarantee you ActiveRecord does zero checking to see if a foreign key constraint exists. the ONLY reason that the asker's code doesn't work, is ActiveRecord expects the key to be an attribute on the model. Straight from the same link you provided in the comments: If you wish to enforce referential integrity at the database level, add the foreign_key: true option to the ‘reference’ column declarations above. It's completely optional whether to make the column an actual foreign key – Hipparch

Ok, but the implementation has to be ready to handle the database foreign key (in the case of foreign_key: true option). The ActiveRecord requirement reflects database restrictions – Heterochromous

the foreign_key: true option has nothing to do with the association directly. that is only applicable to the migration, i.e. #references method. They are two completely separate modules that do not require method interop in any way. Your link to the PostgreSQL docs still has nothing to do with the question. a more relevant link would be the link you placed in the comments; or this issue, that mentions raw sql is not supported, which is the real reason: github.com/rails/rails/issues/29607 – Hipparch

It is a matter of looking at ActiveRecord (or ORMS in general). In my opinion, they are a database overlays and their structure reflects the capabilities of the underlying database (note that the concept of foreign keys is common to different database systems). You look at ActiveRecord as a stand-alone product that can optionally use databases. – Heterochromous

Let us continue this discussion in chat. – Hipparch

Answer 2

you can use activerecord-json-associations to use PostgreSQL JSONB fields to store association information of your models

Wringer

Answer 3

As was discussed in comments to answer that @klin gave , it not possible to do in ActiveRecord out of the box.

However I came up with a maintable approach that works much better, in a similar situation in my project.

I'm utilizing a database feature (if you can control underlying DB) - Generated or Virtual columns are well suited for this task

ALTER TABLE orders ADD COLUMN product_id BIGINT GENERATED ALWAYS AS (CAST(data->>'product_id' AS BIGINT)) STORED;
CREATE INDEX IF NOT EXISTS "index_orders_on_product_id" ON "cq_shopify"."orders" ("product_id");

Now you are making DB do what it does best - store data and run essentially a stored procidure to maintain data integrity.

Since you now have a virtual or generate field called product_id you can use normal belongs_to :products in your Rails app

To keep this in you project - just add it as a migration to your code.

Miracidium