Many-to-many relations in changesets and schemas

Questions / Help
,
1

In my application, I want a Transaction to be connected to possibly many Transaction. I have created a table related_transactions:

  schema "related_transactions" do
    belongs_to(:first_transaction, Transaction)
    belongs_to(:second_transaction, Transaction)

    timestamps()
  end

My intention is to get a list of readable ids (a combination of two fields in the transaction), that is then verified (check that it doesn’t try to relate to itself or a transaction that doesn’t exist), and then turned into a list of id’s which are used in the changeset and created when creating the transaction. Currently I do this with Ecto.Multi, since I didn’t find a good way to do it otherwise.

It doesn’t matter if a transaction is first_transaction or last_transaction, but I am not sure how to implement this logic in the schema and changeset with e.g. has_many -association. Is it possible, and how? Or would it be better for me to instead create two RelatedTransaction for each relation, where I switch who is first_transaction and who is second_transaction. It feels like it should be unnecessary, but it would be easier in regards to the changeset, I think.

Anyone who has encountered a similar problem, and found a solution for it? Or just have some thoughts?

2

You just need to create constraint for first_transaction <> second_transaction. And use many_to_many

1 Like
3

Did you have problems using many_to_many and has_many with the :through option?

4

If you go down this route keep in mind you may run into deadlocks. i.e. if transaction 1 and 2 are related to each other and are modifying this table concurrently:

  • transaction 1 is entering (1, 2)
  • transaction 2 is entering (2, 1)
  • transaction 1 tries to enter (2, 1) and waits for transaction 2 to let go of the lock
  • transaction 2 tries to enter (1, 2) and waits for transaction 1 to let go of the lock
  • deadlock

Make sure your inserts have the same ordering to avoid this. i.e. both transactions try to insert (1, 2) before (2, 1)

5

I’m not sure I understand exactly what kind of constraint you are thinking of here?

6

The main problem is that in the changeset, I want to get all related transactions in one field, regardless of if the current one is first_transaction or last_transaction. After what I understood, I will end up getting two separate fields/associations if I use has_many and many_to_many

7
8

As I understood, there is no difference between Transaction and RelatedTransaction schemas. So maybe you have no need in separate table, just add transaction_id field to Transaction schema (belongs_to self).

schema "transactions" do
 belongs_to :parent_transaction, Transaction, foreign_key: :transaction_id
 
 has_many :child_transactions, Transaction, foreign_key: :transaction_id
end

But need more clarification how you gonna use transactions

1 Like
9

And in changeset you just calling cast_assoc

changeset
...
|> cast_assoc(:child_transactions)
|> ...

And then add constraint as said @hst337 in migrations smth like

def up do
 alter table("transactions") do
  add constraint("transactions", :no_self_reference, check: "id != transaction_id")
 end
end
10

Some more explanation of the usecase:

I want to have a list of transactions, and when editing one transaction, t1, I want to be able to connect it to multiple other transactions, t2 and t3, that already exists. When I then open or edit either t1, t2, or t3, I want to see that it is connected to the other two transactions. The direction of the relationship has hence no importance, only that they are related. Since a transaction can be related to multiple transactions, I can’t use belongs_to field in a transaction.

Even if I have a separate table related_transactions with first_transaction and second_transaction, I end up having to check for two has_many relations since I would need to join through both fields. But it shouldn’t matter which one is first_transaction or second_transaction, just that they are related.

11

Could it maybe be a better solution to alter related_transactions to only having the transaction_id and a field for to identify the relation? That way, I can join on a shared relation-id, and t2 and t3 can more easily be connected if I create a relation from t1 → t2 and t1 → t3, since I just create three entries in related_transactions with the same relation-id.

12

I think the basic problem here is that your concept of “related” cannot be modeled with a single join record. From the db’s POV it matters which fk is used, even if it doesn’t in your use case. So you will either need to create a record for both directions (so you can query from either side), or you will need to write a custom query to get the full list by ORing the conditions for each side (there are other ways to approach but off the top of my head these 2 seem to me to be the most straightforward).

I’m a bit curious about the business case here, because “Transaction” sounds kind of financial but there you would usually have an “order” or similar concept to group the transactions rather than joining them directly to each other. This makes more sense to me when there is no “direction” to the relation.

On the other hand, your model sounds kind of close to a “friend” relation in a social media app where often one side will add the other (so it has a direction) but then there’s frequently a need to query on “friends” by ORing the direction. In that case I would usually model 2 separate joins.

So I guess it would be helpful to know how a relation between transactions is “created” in your business case.

13

In case you missed it, the Ecto docs have a guide on Self-referencing many to many — Ecto v3.11.1 that demonstrates how to model relationships between people. The interesting bits are use of an intermediate schema and the use of :join_keys option to define two separate many_to_many associations that captures the bidrectional character of symmetric self-referencing relations.

Could you elaborate on why you want all related transactions in one field on the changeset?

With the approach described in the guide above, you will end up with two separate fields. You could potentially combine them in an Ecto.Schema virtual field if you’re only trying to display them. Otherwise, it might be cleaner to just style the two separate association fields to appear combined in the UI.

1 Like
14

This is the correct approach.