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[];

[b-admike]

Thank you all for the feedback. Can you please look at https://loopback.io/doc/en/lb4/Database-migrations.html and run automigrate first on the models and try again? I'll try this out as well with mysql datasource on the todo-list example.

[yvesh]

It's creating an todos column in the table and the data gets written into this field (JSON encoded) and it's not using the other table.

[bajtos]

Cross-posting from a StackOverflow question https://stackoverflow.com/q/52931333/69868

When I do for example a get on orders, I have the errors: 500 error: column "products" does not exist and in digging a bit more, I can see that the SQL is trying to retrieve the fields products where it is just a relation. Anybody have an idea if I am doing something wrong? I am using pg as DB.

Cross-posting my StackOverflow answer https://stackoverflow.com/a/53727603/69868

I believe this is a bug in LoopBack 4. When you decorate a class property with @hasMany, the decorator defines a model property under the hood. See here:

export function hasMany<T extends Entity>(
 targetResolver: EntityResolver<T>,
 definition?: Partial<HasManyDefinition>,
) {
  return function(decoratedTarget: Object, key: string) {
    property.array(targetResolver)(decoratedTarget, key);

    // ...
  };
}

When the connector querying the database, it's trying to include the column products in the query, because it thinks products is a property.

[eskappiah]

I'm still experiencing this problem. Any solution or workaround to the above? Mysql is actually trying to retrieve the hasMany property on the model as if it were a field on the database table.

[b-admike]

@eskappiah This should be fixed by #2167.

[anthozep]

Is there a specific version this is fixed in? Still having this issue with postgresql. Do we need to migrate first?

[b-admike]

@anthozep @gerbus This should be fixed by #2167 in the latest release (https://github.com/strongloop/loopback-next/releases/tag/%40loopback%2Frepository%401.1.3). Feel free to re-open if you still see this issue.