Ordered join tables

[maclav3]

Hi guys,

Could you please advise on the best way to make an ordered relation using a transparent join table? I am using the mysql driver with SQLBoiler v2.6.0.

I want to have a many-to-many relation between Foo and Bar, and to preserve the order of Bars within the Foo model.

My first try was to add an AUTO_INCREMENTing column to the join table:

CREATE TABLE `foo_bar` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `foo_id` binary(16) NOT NULL,
  `bar_id` binary(16) NOT NULL,
  PRIMARY KEY (`foo_id`, `bar_id`),
  UNIQUE KEY `ak_id` (`id`),
  KEY `fk_bar_id` (`bar_id`),
  CONSTRAINT `fk_foo_id` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bar_id` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB

I could then add ORDER BY to the INNER JOINs when selecting and expect a consistent order between the inserts and the selects. To change order, I could remove the relevant relations and insert them in the desired order.

However, this generates a separate model for FooBar, just like described in #81. This means no more eager loading and I'm forced to make additional SQL calls to fill the Foo model with ordered Bars.

One ugly solution we have come up with involves storing within Foo the order of its Bars, for example in a JSON column. Then, the order could be enforced when rendering the model, and we would still have the benefits of eager loading. However, this looks artificial, ugly and redundant, and needs extra post-processing of the database result, which needs to be coded every time an ordered relation is needed.

[aarondl]

Unfortunately there's definitely no way and no planned way to make that happen. Transparent join tables are of the definition: "when the only information you want is that these two things are related, we can safely take away the table's model." Ordering information is additional information and that invalidates the condition there so the join table is here to stay (absent workarounds like you've done).

Eager loading however is really powerful in sqlboiler and you can eager load right through your opaque join table.

In the example below you can see we load everything we wanted to. But in our code we have to additionally work through the join table. It's a small pain in the butt but the price we pay for that additional information.

videos, _ := models.Videos(qm.Load("VideoTags", qm.OrderBy("id"), qm.Load("VideoTags.Tags"))

for _, v := range videos {
  for _, vt := range v.R.VideoTags {
    fmt.Println(vt.R.Tag.Name)
  }
}

Note that the ability to add query mods to eager loads is unique to v3 which is being released this week (just pushed v3.0.0-rc3 tag).

I'm willing to discuss any solutions you might have around this, but this is my recommendation so I'll close this for now, feel free to continue to discuss though. Also of course what you're doing now is also fine and a design decision with tradeoffs you'll have to decide on for yourself. Hope that helps. Sorry I didn't have any magic up my sleeve for you :)

[maclav3]

Thanks a lot for the explanation. Query mods in eager loads seem just like the thing that was lacking for an elegant solution. I'm going to try using v3 and see how that goes.