hasMany relations broken with SQL datasources

[gerbus]

Description / Steps to reproduce / Feature proposal

I built a simple API modelled very closely after the ToDo and ToDoList example models. However I used a mysql datasource, and I suspect this caused my relations between repositories/models not to work. I had in my ToDoList model a proper @hasMany(() => ToDo decorator. Wired it up in the repository as per documentation instructions. Then I used the API Explorer to do a GET /todolist in the ToDoListController.

Current Behavior

Error: ER_BAD_FIELD_ERROR with 'ToDo' field

I think something is looking to the ToDoList db table looking for the ToDo field. Obviously this doesn't make sense, right? I want the model to represent that, but I have a different table in my database for ToDo.

Expected Behavior

Relation on model works

Related question: Is relational-database discovery available in LB4? Didn't really see a concrete answer in the docs.

I don't have any experience with previous releases of LoopBack.

[ZeroCoolJr]

This can be easily reproduced by running the lb4 example todo-list and changed it to use a different data source, in my case "postgres", I'm getting the "500 error: column "todos" does not exist". In-memory works fine.

[dougal83]

This can be easily reproduced by running the lb4 example todo-list and changed it to use a different data source, in my case "postgres", I'm getting the "500 error: column "todos" does not exist". In-memory works fine.

I managed to get postgres to work. Used cli to create a postgres datasource which installed dependencies. Have you created the db table with automigrate/autoupdate script? Unlike tools like typeORM, I think you have to manually initiate db changes.

The following worked for me: (WARNING: example uses automigrate that will destroy existing table)
created ./src/bin/todo.automigrate.ts

import { TodoRepository } from '../repositories/todo.repository';
import { DataSource } from 'loopback-datasource-juggler';
import * as config from '../datasources/db.datasource.json';

const ds = new DataSource(config);
const repo = new TodoRepository(ds);

ds.attach(repo.modelClass);

ds.automigrate('Todo', err => {
  if (err) throw err;
  ds.disconnect();
});

executed using ts-node, it works for now. I don't understand how to setup a table with relations currently. EDIT: see, below. Also this script is a bodge as implementation is incomplete.

EDIT: automigrate for todo-list example

[ZeroCoolJr]

This can be easily reproduced by running the lb4 example todo-list and changed it to use a different data source, in my case "postgres", I'm getting the "500 error: column "todos" does not exist". In-memory works fine.

I managed to get postgres to work. Used cli to create a postgres datasource which installed dependencies. Have you created the db table with automigrate/autoupdate script? Unlike tools like typeORM, I think you have to manually initiate db changes.

I manually created the tables.

The following worked for me: (WARNING: example uses automigrate that will destroy existing table)
created ./src/bin/todo.automigrate.ts

import { TodoRepository } from '../repositories/todo.repository';
import { DataSource } from 'loopback-datasource-juggler';
import * as config from '../datasources/db.datasource.json';

const ds = new DataSource(config);
const repo = new TodoRepository(ds);

ds.attach(repo.modelClass);

ds.automigrate('Todo', err => {
if (err) throw err;
ds.disconnect();
});

executed using ts-node, it works for now. I don't understand how to setup a table with relations currently. Also this script is a bodge as implementation is incomplete.

Thanks, you've pointed me in some direction. I tried including the TodoListRepository in the automigrate but todos became a column instead, it did not create a relation! Querying /todo-lists with a parameter filter[include]=todos now complains 500 Error: Relation "todos" is not defined for TodoList model. I tried adding the relation manually and named it "todos" but that did not work.

[dougal83]

Perhaps the implementation is incomplete atm. I suppose we'll both eagerly await the progress to be made.

[ZeroCoolJr]

Related issue #1889. Tried http://localhost:3000/todo-lists?"filter"={"include": [{"relation": "todos"}]} and does not spit the 500 Error: Relation "todos" is not defined for TodoList model error but todos is null. Incomplete functionality as stated.

[cpschofield]

I am new to Loopback and tried the exact same test as described Gerbus.

Since updating my dependencies this has gotten better the only issue I am still having is:

I tried including the TodoListRepository in the automigrate but todos became a column instead, it did not create a relation!

If I call /todo-lists/1/todos I now see the correct data but this is at the expense of a 'junk' column existing in the database

[Mohit-Purbia]

i also face same problam ER_BAD_FIELD_ERROR: Unknown column 'ingridient' in 'field list' with mysql
db

@hasmany(() => Ingridient)
ingridient?: Ingridient[];