# Ordering many-to-many relations

**URL:** <https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470>\
**Category:** Issues\
**Created:** [June 1, 2021, 3:59pm UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470 "2021-06-01T15:59:33Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Morozzzko](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/morozzzko/32/239_2.png) [@Morozzzko](https://discourse.rom-rb.org/u/Morozzzko)\
**Post date:** [June 1, 2021, 3:59pm UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/1 "2021-06-01T15:59:34Z")

</div>

I have a many-to-many relation between two entities. The join table has a field called _position_, which is supposed to be used for ordering.

Example:

```auto
class Film < Relation[:sql]
  associations do
    has_many :actors, through: :film_actors
  end
end
class Actor < Relation[:sql]
...
end
class FilmActor < Relation[:sql]
  schema do
  ...
    attribute :position, Types::Integer
  end

  dataset do
    order(:position)
  end
end

```

Whenever I use `films.combine(:actors)` I expect `actors` to have an order specified by `FilmActor` relation. However, it doesn’t work this way. Instead, it orders by `actors.id`, even though it jons `film_actors` anyway.

I’ve tried modifying relation to `has_many :actors, through: :film_actors, order: :position`, but it doesn’t work either.

However, Sequel::Model supports it just fine:

`many_to_many :actors, join_table: :film_actors, order: :position`

So here’s the question: how do I achieve the same using rom-rb with rom-sql?

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [June 2, 2021, 10:16am UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/2 "2021-06-02T10:16:25Z")

</div>

hey @Morozzzko! So, I believe this is what you need [ROM - Associations](https://rom-rb.org/learn/sql/3.3/associations/#extending-associations-with-custom-views)

We could probably just add support for `:order` option though that would automatically configure relation behind the scenes.

---

<div class="post-metadata">

**Author:** ![Morozzzko](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/morozzzko/32/239_2.png) [@Morozzzko](https://discourse.rom-rb.org/u/Morozzzko)\
**Post date:** [June 2, 2021, 11:15am UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/3 "2021-06-02T11:15:03Z")

</div>

Oh thank you! That is indeed exactly what I need right now. I didn’t expect to find it there. Actually skipped the part of the docs.

Yes, it would be nice to add support for `:order` in DSL. However, it’s still counter-intuitive that the association totally ignores what I’ve said about the default order of the relation. So I guess it could end up in two changes

---

<div class="post-metadata">

**Author:** ![Morozzzko](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/morozzzko/32/239_2.png) [@Morozzzko](https://discourse.rom-rb.org/u/Morozzzko)\
**Post date:** [June 2, 2021, 12:59pm UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/4 "2021-06-02T12:59:46Z")

</div>

Also I’ve noticed something: it relies on me defining an order on `Actor` instead of `FilmActor`. Which is exactly what I’d like to avoid.

I want to have a one-way binding: you can get from a `Film` to an `Actor`, but can’t do it the other way around. So `Actor` has no idea that `FilmActor` even exists.

And `:view` forces me to leak the abstraction.

Well, we’ve got what we’ve got. Hope it’ll get better in later versions. Thank you!

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [June 4, 2021, 8:36am UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/5 "2021-06-04T08:36:30Z")

</div>

@Morozzzko oh relations are aware of all other relations and that’s by design. Treat the entire relation “layer” as one abstraction. That’s exactly why I recommend using repositories to hide details and establish strong boundaries 🙂

---

<div class="post-metadata">

**Author:** ![Morozzzko](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/morozzzko/32/239_2.png) [@Morozzzko](https://discourse.rom-rb.org/u/Morozzzko)\
**Post date:** [June 4, 2021, 8:58am UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/6 "2021-06-04T08:58:29Z")

</div>

Well, breaks my idea of growing functionality using modular design or whatever.

Here’s the context:

I have some “core” relations in my domain, the whole app revolves around them.

However, there are still smaller modules which may be easily disabled or _not loaded at all_. So I don’t want code from the features to leak into the “core”. It would be nice to remove _all_ traces of feature by simply removing a directory and a boot file.

This thread actually demonstrates that it would have been possible if we’d had a better relation management.

I am also a little bummed because rom-sql is still powered by Sequel, which _does_ enable me to do all of this

---

<div class="post-metadata">

**Author:** ![Morozzzko](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/morozzzko/32/239_2.png) [@Morozzzko](https://discourse.rom-rb.org/u/Morozzzko)\
**Post date:** [June 4, 2021, 9:02am UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/7 "2021-06-04T09:02:05Z")

</div>

To add to my previous point:

I’m still fine with listing _relations_, as we can’t drop database constraints by simply dropping directories. So that would have to be explicit, which is good.

What I don’t like is that I have to define context-specific _sorting_ logic inside one of the core relations

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [June 8, 2021, 8:13am UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/8 "2021-06-08T08:13:49Z")

</div>

@Morozzzko yeah I see your PoV. It’s just a missing feature, the details how sorting is done can be hidden. Query logic is exposed more in rom-sql a bit on purpose though. I specifically wanted to avoid heavy abstractions on top of queries so that it’s easier to tweak, optimize and have more control in general over query generation. It’s not to say that having common use cases covered through DSL is a bad idea, as long as you have access to the underlying relations, things will be OK.

> However, there are still smaller modules which may be easily disabled or _not loaded at all_ . So I don’t want code from the features to leak into the “core”. It would be nice to remove _all_ traces of feature by simply removing a directory and a boot file.

I’m not sure if I get it. Do you have associations defined between “core” relations and some other relations?

---

<div class="post-metadata">

**Author:** ![Morozzzko](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/morozzzko/32/239_2.png) [@Morozzzko](https://discourse.rom-rb.org/u/Morozzzko)\
**Post date:** [June 8, 2021, 8:59am UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/9 "2021-06-08T08:59:49Z")

</div>

> I’m not sure if I get it. Do you have associations defined between “core” relations and some other relations?

Other relations reference “core” relations, yes.

Let me give another relevant example: if we’re building a cleaning service, we’ve got a “marketplace”, or an uber-like model. Basically, everything revolves around customers, orders for cleaning, and cleaners.

However, we’ve got mass-hiring, and each cleaner signs a contract. We have to store the contract and link it to the cleaner.

Other option is the ability to sort the scheduled orders or tag them in app. It’ll require us to add new relations, which are only usable in a single narrow context.

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [June 9, 2021, 7:56am UTC](https://discourse.rom-rb.org/t/ordering-many-to-many-relations/470/10 "2021-06-09T07:56:22Z")

</div>

@Morozzzko the challenge here is that you want to decouple things that are coupled by nature, because they are associated. A base relation corresponds to a table, canonical associations correspond to tables too. If you have customers as a core base relation, and a bunch of other non-core relations associated, then you need to have customers with those non-core associations defined too. I’m pretty sure things would break if associations were defined only on one side.

It’s something to explore for sure though. I am very much aware of the fact that loading all-of-the-things can become a problem and it should be addressed eventually. This is something we could tackle after 6.0.0 is released.