hasMany relation with MySQL data source

[bbenejc]

Description / Steps to reproduce

I am trying out Loopback 4 for a project, and am having an issue with a simple hasMany relation.
The easiest way to reproduce is to use the todo-list example and change datasource type from "memory" to "mysql".

db.datasource.json example:

{
  "name": "db",
  "connector": "mysql",
  "url": "",
  "host": "localhost",
  "port": 3306,
  "user": "root",
  "password": "root",
  "database": "test"
}

Here is code to create tables:

-- Dumping structure for table test.todo
CREATE TABLE IF NOT EXISTS `todo` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `desc` varchar(255) DEFAULT NULL,
  `isComplete` tinyint(4) DEFAULT NULL,
  `todoListId` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `todo` (`id`, `title`, `desc`, `isComplete`, `todoListId`) VALUES
	(1, 'Take over the galaxy', 'MWAHAHAHAHAHAHAHAHAHAHAHAHAMWAHAHAHAHAHAHAHAHAHAHAHAHA', 0, 1),
	(2, 'destroy alderaan', 'Make sure there are no survivors left!', 0, 1),
	(3, 'terrorize senate', 'Tell them they\'re getting a budget cut.', 0, 2),
	(4, 'crush rebel scum', 'Every.Last.One.', 0, 2);

-- Dumping structure for table test.todolist
CREATE TABLE IF NOT EXISTS `todolist` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `todolist` (`id`, `title`, `color`) VALUES
	(1, 'Sith lord\'s check list', NULL),
	(2, 'daily routine of POTUS', NULL);

Current Behavior

Now when you try to GET /todo-lists - you get this error:
Unhandled error in GET /todo-lists: 500 Error: ER_BAD_FIELD_ERROR: Unknown column 'todos' in 'field list'

Here is a same / similiar issue on StackOverflow:
https://stackoverflow.com/questions/52931333/loopback-4-hasmany-relation-included-in-fields

Expected Behavior

Return array of todo-lists.

[dougal83]

Why not create the table using the migration tools? Have you tried that?

https://loopback.io/doc/en/lb4/Database-migrations.html

[bbenejc]

Thanks for the suggestion.... I have just tried the automigrate function.
I see it creates a todos TEXT column in todolist table, so the queries then work.

Doing a POST on /todo-lists in that case stores todos as JSON in that column.
But doing a POST on /todos creates a new todo in todo table.

I guess in my case, I should only create a belongsTo relation on the child model, since I am never storing child entities in the parent table.

I had other questions, but I see they are already being worked on:

• Inclusion of related models [MVP] #1352
• Include related models in "GET /mymodels" results #1889

[DanielJaramillo94]

I don't know why, but everything is already working neat with your original scipt to create the tables in the database, with no need of automigration. Why?