Many-To-Many Associations w/o Join Table

[Bharat311]

Hi Guys,

I am currently in middle of migrating my app from Rails/Mongoid to Phoenix/MongoDB-Ecto.

I have some many-to-many associations in my project which do not have join tables between them.
Some of these are single-sided relations w/o inverse relation.

These kind of relations are easily supported in Mongoid and internally implemented as an array of BSON id’s saved on each document.

I was wondering if this package provides support for these associations ?

If not, any pointers to how to make it work would be really appreciated. I will also try to raise a PR for this, if it feels like a valid use-case.

[johnnyshields]

Just wanted to capture the full extent of requirements here; it would be nice if we supported the full extent of permutations which are possible with list-type fields. Plural field name indicates a list of IDs.

1) One-to-Many - One-sided case A
Dog - owner_id
Owner - id

2) One-to-Many - One-sided case B
Dog - id
Owner - dog_ids

3) One-to-Many - Two-sided (A + B)
Dog - id, owner_id
Owner - id, dog_ids

4) Many-to-Many via Join Table
Dog - id
DogOwners - dog_id, owner_id 
Owner - id

5) Many-to-Many One-sided case A
Dog - owner_ids
Owner - id

6) Many-to-Many One-sided case B (functionally equivalent to 5)
Dog - id
Owner - dog_ids

7) One-to-Many - Two-sided (A + B)
Dog - id, owner_ids
Owner - id, dog_ids

[ankhers]

@Bharat311

I cannot speak to the version of mongodb_ecto that works with Ecto 1.x, but this should work for the version of mongodb_ecto that works for Ecto 2.0 (which is not release but should be merged shortly ( #84 ))

It may not support it in the same way that Mongoid does. But you should be able to do something along the lines of this.

defmodule Foo do
  @primary_key {:id, :binary_id, autogenerate: true}
  schema "foos" do
    field :name, :string
    field :bar_ids, {:array, BSON.ObjectId}
  end
end

defmodule Bar do
  @primary_key {:id, :binary_id, autogenerate: true}
  schema "bars" do
    field :address, :string
  end
end

This would give Foo a list of all associated Bar IDs. With that being said, you cannot do something like foo.bars like you could in mongoid. You would need to construct the query yourself with something like

Repo.all(from b in Bar, where: b.id in ^foo.bar_ids)`

which would give you a list of all Bars that are associated with a single Foo.

I opted for this behaviour because Mongo does not support joins like SQL does. Whether the adapter does it or you do it, it would still need to be 2 queries. I just wanted to make it more visible to you that there are multiple queries going to the database. It may allow you to think that there could be a better way to represent your data.

If this does not answer your question, would you be able to show an example of what your data looks like and how you interact with it?

[johnnyshields]

@ankhers the goal of this ticket would be to add macro functions that not only add the relation selector as you have described, but also add the proper callbacks to maintain the correct values for inverse relations.

For example, suppose foo.bars and bar.foos are the two sides of a many-to-many relationship. I should be able to do something like foo.bars = foo.bars ++ [new_bar]. This would not only add new_bar.id to foo.bar_ids, it would also call a MongoDB $add_to_set to add foo.id to new_bar.foo_ids (i.e. update the inverse relationship.)

I have an app with probably 200+ such relations, if I attempted to do this as all custom code without macro functions it would be an absolute nightmare.

[scottmessinger]

@ankhers I really appreciate how much time you've been putting into monogdb_ecto -- I'm excited to start using your branch.

Regarding relationships, I'm not too familiar with using ecto with a RDMS, but the method that @johnnyshields laying out doesn't look like how it works in Ecto. In the Ecto documentation, the way you create associations looks nothing like how it looks in Ruby/Rails land: https://hexdocs.pm/ecto/Ecto.Schema.html#many_to_many/3-examples While I think adding better support for relationships in mongodb_ecto is great, I think the path looks more like implementing the preload options and build_assoc and assoc methods.

[ankhers]

I need to prefix this comment with a couple things.

Anything written here are my own thoughts. I could be wrong on some things I am about to say because I am not a big MongoDB person. Either I do not understand HOW to use it, which is completely possible, or it just doesn't line up with the kinds of applications that I make.

I will hopefully not come off as insulting... But, if you have over 200 relations in your application, maybe it would be better to use an RDMS for your relational data and MongoDB for your non-relational data. With that said, I'm sure someone within your organization went through the pros and cons of various database solutions and settled on MongoDB for your application(s), which is completely fine if it suits your expected use case.

With all of that out of the way, you don't need any sort of special macros to support what you are asking for. You can write a 2 line function to do exactly what you are asking for. Though, you may want to change it slightly in order to give a more meaningful return value.

defmodule MyApp.Repo do
  use Ecto.Repo, otp_app: :my_app

  def relate_documents(doc1, key1, doc2, key2) do
    Mongo.update_one(Repo.Pool, doc1.__struct__.__schema__(:source),
      %{"_id": BSON.ObjectId.decode!(doc1.id)},
      %{"$addToSet": %{key1 => BSON.ObjectId.decode!(doc2.id)}})

    Mongo.update_one(Repo.Pool, doc2.__struct__.__schema__(:source),
      %{"_id": BSON.ObjectId.decode!(doc2.id)},
      %{"$addToSet": %{key2 => BSON.ObjectId.decode!(doc2.id)}})
  end
end

This may look a little confusing, so I will step through each part.

First, you have the two documents and keys that you need to pass into this function. Given the following schemas

defmodule Foo do
  import Ecto.Schema

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "foos" do
    field :bar_ids, {:array, :binary_id}
  end
end

defmodule Bar do
  import Ecto.Schema

  @primary_key {:id, :binary_id}
  schema "bars" do
    field :foo_ids, {:array, :binary_id}
  end
end

and assuming I already have a single foo and a single bar, you would call it like Repo.relate_documents(foo, :bar_ids, bar, :foo_ids). Simple enough.

We need to call the Mongo driver explicitly instead of using Ecto here because the mongodb_ecto adapter does not currently support the use of $addToSet. So we call Mongo.update_one/5 in order to actually update the two documents.

doc1.__struct__.__schema__(:source) just retrieves the collection name that is associated with that schema.

We need to decode! the documents ourselves because the ecto representation comes out as a string.

With that said, I am truly unsure if what you are currently doing with MongoDB is a common thing to do. I have done a couple searches for many to many relations within MongoDB and I cannot find any officially supported way of doing these kinds of things within Mongo.

[johnnyshields]

@scottmessinger @ankhers thank you for your comments.

@ankhers firstly no offense taken, actually using Mongoid in Ruby makes relations so easy to work with that I don't have the slightest need to look at an RDMS.

Mongoid overrides the getters/setters so you can do something like:

foo.bars = [bar]
foo.save

When save is called, the inverse key bar.foo_ids will be auto-magically updated via callbacks.

Now, consider what happens if I want to do multiple relations and multiple updates to each:

foo.bars = [bar1]
foo.bazs = [baz1]
foo.quxs = [qux1, qux2]
foo.bars = foo.bars ++ [bar2]
foo.save

If I use your Repo.relate_documents method, the above code will produce at least 9 queries in total, including updates to inverse keys, because all relations will be modified atomically.

However, using Mongoid in Ruby, no queries are performed until foo.save is called--updates are lazy evaluated. All changes to foo will be processed on a single query so you can achieve the same result with 5 queries.

For this reason, IMHO it makes sense to introduce some functionality which allows users to work with relations as if they were fields, and lazy-process the necessary actions only when the model is saved. Again I'd recommend you to take a look at Mongoid in Ruby because they've really come up with an elegant abstraction for all this.

[michalmuskala]

Providing support for this, would first need work to be done on ecto, to make the association API extensible (it's not far from being that, but it's not exactly it yet). Once that is done, custom association types could be provided by mongodb_ecto.

Contributions to ecto on that are welcome. If somebody wants to tackle this, I'll provide guidance.

Since it's not directly actionable in this repository until necessary changes in ecto are done, I'm going to close it.

[johnnyshields]

@michalmuskala thanks for the response. My company TableSolution would be happy to have one of our best and brightest developers @Bharat311 work on this.

[michalmuskala]

Best place would be to discuss this further on Ecto mailing list or on elixir's IRC where the whole ecto team can be found.

\cc @josevalim

[josevalim]

If I understand this correctly, the main idea is to support associations where multiple entries are specified using a list of IDs. It looks like it would be a "belongs_to_many"? I.e. imagine I want to have the same comment on posts 1, 2, 3 and 4. I would have a post_ids column (instead of post_id) which all of those 4 ids.

[ankhers]

@josevalim From my understanding, they also want to have a "many_to_many" relationship without a join tables (both schemas store foreign keys in their respective collection) similar to the Foo and Bar modules I showed in my last comment.

The idea would be to specify the relationship IDs in an array, or something, and on save it would also update the inverse ids.

So, in my example above, we have the foo and bar. We would somehow set the bar.id into foo.bar_ids and on save, something would happen that also saved the foo.id into the bar.foo_ids field.

We would also be able to access all of the Bars using foo.bars and the inverse.

Hopefully that made enough sense.

[josevalim]

@ankhers I see, thank you for clarifying. It is slightly awkward, because the information is duplicated now in both entries, and they can get out of sync unless the database provide atomicity or transactions. How would such be implemented in Mongo?

[ankhers]

Long story short, Mongo does not support proper transactions. At least not in the sense that Postgres supports transactions. And for reference, this is where I am getting all of this information.

When you perform an update on a document, that operation is atomic on that document, no matter what part of it is being modified. So Mongo does make the guarantee that you are not querying a document that is mid edit.

From my understanding, we can get transaction-like semantics by using two-phase commits. However, that is left up to the developers of a given application to get working. And after reading, it adds a bunch more queries to what should only be 2 in Mongo. We would also need to clutter the document with information about the transaction.

So it is very hard to say how something like this could be implemented. We could go the route of two-phase commits, but then we are adding a lot more queries to something. Or we could just go the naïve way and not make any guarantees about the data being in sync on both documents.

Hopefully someone else with a little more MongoDB knowledge would be able to shed some light on this.

[scottmessinger]

@josevalim That's exactly it. In my app, that's the only way I do one-to-many and how I've seen it modeled in other apps. In some apps, the reference (post_ids) might be an array of stringified BSON ObjectIds. (e.g. ["55f203a27465611471cb0400", "55f203ac7465611471620900"] instead of [ #BSON.ObjectId<"55f203a27465611471cb0400">, #BSON.ObjectId<"55f203ac7465611471620900">]. Having an optional "type" as belongs_to relationships have would solve this mismatch.

Ecto 1.0.7 almost makes this possible today. If I do has_many :posts, App.Post, reference: :post_ids, foreign_key: :id, the query almost looks right but throws this error

** (Ecto.CastError) deps/ecto/lib/ecto/association.ex:336: value `[["55f203a27465611471cb0400", "55f203ac7465611471620900"]]`
in `where` cannot be cast to type {:array, :binary_id} in query:

from p in Spikey.Post,
  where: p.id in ^[["55f203a27465611471cb0400", "55f203ac7465611471620900"]],
  order_by: [asc: p.id]

As for the many-to-many relationships, you're completely right @josevalim -- the lack of transactions makes this possible to get out of sync. In my app, I have a couple instances of user.post_ids = [1,2,3] in one model and post.user_id = 1. Denormalization like this has performance benefits but obvious costs. The only reason I'm comfortable using it is because the relationship is immutable (I create the relationship when the document is created). I offer that use case only to suggest there are read only use cases where it's not entirely crazy. In parts of the app where the relationship is mutable, I store the relationship in only one document and not both.

I'm not sure what direction to suggest for Ecto, but my initial thought is denormalizing the relationship isn't a case that should be natural or normal when using Ecto. If denormalized relationships are supported, some option like provocatively named like dangerously_denormalize: true seems like it would go along way to helping app developers think twice about their schema. One of the mistake's Mongoid made was making an interface that led developers think Mongo offered the same data integrity guarantees that a RDMS makes. For instance, updating embedded documents should never be done by referencing the document by it's index in the array. However, Mongoid hid that reality and it bit people hard like the team at Artsy. If something isn't safe to do atomically in Mongo, it shouldn't feel safe in Ecto. It should still probably be possible, just not comfortable.

Do the youngness of the mongodb_ecto adapter, I use it rather extensively for reading data but rarely for writing. I do practically all writes using the raw Mongo elixir driver. I do this in large part because

• Mongo's atomic operators ($addToSet, $set, $unset, $push, $pull, $inc`, etc) are essential to using Mongo in a safe way
• I use a ton of embedded documents and the only way to safely update an embedded document is to query it. For example, if I want to update a comment in a blog post, I'd query %{_id: "post_1", comments.id: "comment_1"} and then update it like so %{"$set: %{"comments.$.comment" => "Loved it" } } where the $ points to the comment with the id comment_1 in the comments array. For me, ecto isn't very useful for updating data in Mongo until it supports the atomic operators and lets embedded documents be queried and updated. (That's not a dig on the impressive work done on mongo_ecto. I really love that it exists).

Returning back to the many-to-many question, supporting atomic operators help decrease the risks of having multiple writes that aren't wrapped in a transaction. For instance, if we need to add an id to arrays in two documents, two subsequent updates using $addToSet are less likely to conflict than $set the fields. Any increased support Ecto has for atomic operators will help with updating denormalized relationships as safely as Mongo currently allows.

Re: @ankhers's point about 2 phase commits. Having used mongo for 5 years, I generally think Ecto should leave 2 phase commits to the user or to a library. Where needed, I don't think it's a problem if Ecto makes two writes, but I think it's best if the dev had to opt into it. For instance, in Ecto 1.0.7, if you do Repo.get(Model, id, preload: [:comments]), comments won't be loaded. Thus, the dev has to do Repo.get(Model, id) |> Repo.preload(:comments) which looks like the two queries it actually is. I think that explicitness is entirely appropriate given Mongo's lack of support for relationships. I'd use that as a model when approaching other situations.