Description
What version of SQLBoiler are you using (sqlboiler --version
)?
v4.2.0
What is your database and version (eg. Postgresql 10)
MySQL 5.7
Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)
(It's not a very realistic example but pls bear with it 😅 )
We have 3 entities here: person, bike and wheel. Person-bike and bike-wheel are M-M relationships. For example, Person A and Person B share the same bike, and the bike have 2 wheels. The relationships look like this:
person A --\ / Wheel A
bike A
person B --/ \ Wheel B
The DB schema:
# Dump of table persons
# ------------------------------------------------------------
CREATE TABLE `persons` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(256) DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
LOCK TABLES `persons` WRITE;
INSERT INTO `persons` (`id`, `name`, `deleted_at`)
VALUES
(1,'persons_a',NULL),
(2,'persons_b',NULL);
UNLOCK TABLES;
# Dump of table wheels
# ------------------------------------------------------------
CREATE TABLE `wheels` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(256) DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
LOCK TABLES `wheels` WRITE;
INSERT INTO `wheels` (`id`, `name`, `deleted_at`)
VALUES
(1,'wheels_a',NULL),
(2,'wheels_b',NULL);
UNLOCK TABLES;
# Dump of table bikes
# ------------------------------------------------------------
CREATE TABLE `bikes` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(256) DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
LOCK TABLES `bikes` WRITE;
INSERT INTO `bikes` (`id`, `name`, `deleted_at`)
VALUES
(1,'bikes_a',NULL);
UNLOCK TABLES;
# Dump of table persons_bikes
# ------------------------------------------------------------
CREATE TABLE `persons_bikes` (
`person_id` int(11) unsigned NOT NULL,
`bike_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`person_id`,`bike_id`),
KEY `persons_bikes_fk_bikes` (`bike_id`),
CONSTRAINT `persons` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`),
CONSTRAINT `persons_bikes_fk_bikes` FOREIGN KEY (`bike_id`) REFERENCES `bikes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
LOCK TABLES `persons_bikes` WRITE;
INSERT INTO `persons_bikes` (`person_id`, `bike_id`)
VALUES
(1,1),
(2,1);
UNLOCK TABLES;
# Dump of table bikes_wheels
# ------------------------------------------------------------
CREATE TABLE `bikes_wheels` (
`bike_id` int(11) unsigned NOT NULL,
`wheels_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`bike_id`,`wheels_id`),
KEY `bikes_wheels_fk_wheels` (`wheels_id`),
CONSTRAINT `bikes_wheels_fk_wheels` FOREIGN KEY (`wheels_id`) REFERENCES `wheels` (`id`),
CONSTRAINT `bikes_wheels_fk_bikes` FOREIGN KEY (`bike_id`) REFERENCES `bikes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
LOCK TABLES `bikes_wheels` WRITE;
INSERT INTO `bikes_wheels` (`bike_id`, `wheels_id`)
VALUES
(1,1),
(1,2);
UNLOCK TABLES;
Further information. What did you do, what did you expect?
When we load the person's bikes and wheels like the code shows below:
persons, err := models.Persons(
qm.WhereIn("id in ?", 1, 2),
qm.Load("Bikes.Wheels"),
).All(boil.GetDB())
if err != nil {
commons.Logger.Err(err).Msg("Cannot get RequestOrders from database")
return
}
for _, p := range persons {
fmt.Printf("Person %s has %d bike(s)\n", p.Name.String, len(p.R.Bikes))
for _, b := range p.R.Bikes {
fmt.Printf("Bike %s has %d wheels\n", b.Name.String, len(b.R.Wheels))
}
}
We would expect that the bike should have 2 wheels since it's the same bike shared by 2 persons. However, you will get output like this:
Person persons_a has 1 bike(s)
Bike bikes_a has 2 wheels
Person persons_b has 1 bike(s)
Bike bikes_a has 0 wheels
As you see here, the second bike entity referred by person B doesn't have wheels, even though it's the same bike as person A owns.
The problem is in the M-M eager loading template.
sqlboiler/templates/09_relationship_to_many_eager.go.tpl
Lines 152 to 154 in 6ccc92c
Firstly, LoadBikes
actually loads 2 identical records from the DB, but they are different objects in our code. Then the LoadWheels
only load 2 wheels shared among the bikes. However, the outer loop exhausted the foreign results and assigned them to the first local object, therefore nothing left for the second one.
It can be fixed by simply swapping the outer and inner loops. I've fixed it in our customized templates and it works fine. I can create a PR if you agree that it's something worth fixing. Thanks!