Problem in accessing included relation objects

[Jtmaurya]

const user = await this.userRepository.findOne({
where: { id: id },
include: [
{ relation: 'order' }
]
});

  console.log('User details>>>>>>>>>>>>>', user);

  if (user && user.id) {
    console.log('user details>>>>>>>>>>>>>', user.order.id);
   // const records = await this.productRepository.find({
    //  where: { productId: product.id },
    //  include: [
      //  { relation: 'product' }
    //  ]
    })

I am not able to access the related objects .
Relation is User hasMany orders
Order belongsTo user

[Jtmaurya]

user.order.id is giving error

[derdeka]

@Jtmaurya Can you please provide an example repository showing your issue? Please review the issue reporting guidelines: https://loopback.io/doc/en/contrib/Reporting-issues.html#how-to-report-an-issue

[agnes512]

Hi, could you make the issue more descriptive? Following the reporting guidelines would be helpful.

For relations,

1. please make sure you have inclusion resolver set up and queried the correct relation names.
   For example,

• User hasMany orders, orders is your relation name.
• Order belongsTo a user. user is the relation name.

To query a user with its related orders when id = user.id:

const user = await this.userRepository.find({
    where: { id: some_user.id },
    include: [
        { relation: 'orders' }  // should be orders
    ]
});

To query an order with its related user when id = order.id:

const user = await this.productRepository.find({
    where: { id: some_order.id }, // make sure the content in the where clause is correct
    include: [
        { relation: 'user' }
    ]
});

2. please make sure to specify your customized names if you're not using the default names. Check Relation metadata for examples and details.

[Jtmaurya]

It is the order repository

import {DefaultCrudRepository, repository, BelongsToAccessor} from '@loopback/repository';
import {Order, OrderRelations, User} from '../models';
import {MysqldbDataSource} from '../datasources';
import {inject, Getter} from '@loopback/core';
import {UserRepository} from './user.repository';

export class OrderRepository extends DefaultCrudRepository<
Order,
typeof Order.prototype.orderId,
OrderRelations

{

public readonly user: BelongsToAccessor<User, typeof Order.prototype.orderId>;

constructor(
@Inject('datasources.mysqldb') dataSource: MysqldbDataSource, @repository.getter('UserRepository') protected userRepositoryGetter: Getter,
) {
super(Order, dataSource);
this.user = this.createBelongsToAccessorFor('user', userRepositoryGetter,);
this.registerInclusionResolver('user', this.user.inclusionResolver);
}
}

[Jtmaurya]

This is the user repository

import {DefaultCrudRepository, repository, HasManyRepositoryFactory} from '@loopback/repository';
import {User, UserRelations, Order} from '../models';
import {MysqldbDataSource} from '../datasources';
import {inject, Getter} from '@loopback/core';
import {OrderRepository} from './order.repository';

export class UserRepository extends DefaultCrudRepository<
User,
typeof User.prototype.id,
UserRelations

{

public readonly orders: HasManyRepositoryFactory<Order, typeof User.prototype.id>;

constructor(
@Inject('datasources.mysqldb') dataSource: MysqldbDataSource, @repository.getter('OrderRepository') protected orderRepositoryGetter: Getter,
) {
super(User, dataSource);
this.orders = this.createHasManyRepositoryFactoryFor('orders', orderRepositoryGetter,);
this.registerInclusionResolver('orders', this.orders.inclusionResolver);
}
}

////////////////
Order belongsTo user
So i want to access user.order.orderId

[agnes512]

@Jtmaurya The repositories seem correct to me. Could you also post those 2 models? thanks.

[Jtmaurya]

import {Entity, model, property, hasMany} from '@loopback/repository';
import {Order} from './order.model';

@model({settings: {strict: false}})
export class User extends Entity {
@Property({
type: 'number',
id: true,
generated: true,
})
id?: number;

@Property({
type: 'string',
required: true,
})
email: string;

@Property({
type: 'string',
required: true,
})
firstname: string;

@Property({
type: 'string',
required: true,
})
lastname: string;

@Property({
type: 'string',
})
password?: string;

@hasmany(() => Order)
orders: Order[];
// Define well-known properties here

// Indexer property to allow additional data
// eslint-disable-next-line @typescript-eslint/no-explicit-any
[prop: string]: any;

constructor(data?: Partial) {
super(data);
}
}

export interface UserRelations {
// describe navigational properties here
}

export type UserWithRelations = User & UserRelations;

[Jtmaurya]

import {Entity, model, property, belongsTo} from '@loopback/repository';
import {User} from './user.model';

@model({settings: {strict: false}})
export class Order extends Entity {
@Property({
type: 'number',
id: true,
generated: true,
})
orderId?: number;

@Property({
type: 'number',
required: true,
})
total: number;

@belongsTo(() => User)
userId: number;
// Define well-known properties here

// Indexer property to allow additional data
// eslint-disable-next-line @typescript-eslint/no-explicit-any
[prop: string]: any;

constructor(data?: Partial) {
super(data);
}
}

export interface OrderRelations {
// describe navigational properties here
}

export type OrderWithRelations = Order & OrderRelations;

[agnes512]

The models seem valid as well.

Could you check if your query is correct?

const myUser = await userRepository.create({name: 'my_user', ..other props});
await orderRepository.create({userId: myUser.id});  // the order instance must contain the foreign key

const result = await userRepository.find(
  {
     where: {id: myUser.id},
     include: [
        { relation: 'orders'}
    ]
 }
);

then result[0].orders should be the related order that we created above.

[Jtmaurya]

Yes, the above code is working. Can you please help me as i want to get included relation properties.
In my scenario

User hasMany orders,
Order belongsTo User

So i want to write a query which will give me order.user.user_properties;

[agnes512]

It's the same.

const myUser = await userRepository.create({name: 'my_user', ..other props});
const myOrder = await orderRepository.create({userId: myUser.id});  // the order instance must contain the foreign key

const result = await orderRepository.find(
  {
     where: {id: myOrder.id},
     include: [
        { relation: 'user'}
    ]
 }
);

then result[0] is the order instance that you want. And result[0].user is the related User instance. You should be able to access all user properties that you need.

Is the inclusion not working? or you couldn't query data as you want? I believe the Querying related model section has examples to show the query syntax.

[Jtmaurya]

This code is not working for me.
Can you suggest something else?

[dougal83]

Hey @Jtmaurya, can you post an example repo on your account that replicates the issue you are experiencing?

[Jtmaurya]

https://github.com/Jtmaurya/dummy2.git

In the main.controller file i want to get included relation properties.

[agnes512]

@Jtmaurya I tired out you app and finally found where the problem is. All the artifacts are valid, just need to make some small changes.

The issue is caused by migration.
TL;DR, the identifier Order.orderId got escaped during migration (related code). It's a known issue, see #2399. If you check your MySQL table Order, it doesn't have the identifier:

That's why it couldn't create an order instance properly, not to mention for inclusion opertations.

Here are the changes you need to make as a workaround:

In order.model.ts file,

@model() . // SQL databases don't support strict mode, need to be removed 
export class Order extends Entity {
  @property({
    type: 'number',
    id: true,
    generated: true,
    mysql: {  // this setting allows you to have different names for model and db column
      columnName: 'orderid',
      dataType: 'integer',
      dataLength: null,
      dataPrecision: null,
      dataScale: 0,
      nullable: 'NO',
    },
  })
  orderId?: number;

  @property({
    type: 'number',
    //required: true,  // your post request for Order doesn't have this property, it should not be required
  })
  quantity: number;

  @property({
    type: 'number',
    //required: true,  // your post request for Order doesn't have this property, it should not be required
  })
  totalPrice: number;

  @belongsTo(() => User)
  userId: number;

  // [prop: string]: any; // this property needs to be removed

  constructor(data?: Partial<Order>) {
    super(data);
  }
}

Your Order table will look like this:

The setting maps model property name orderId to the db column orderid.

In user.model.ts file:

@model()  // SQL databases don't support strict mode, need to be removed 
export class User extends Entity {
  @property({
   ...
  // [prop: string]: any; // this property needs to be removed

  constructor(data?: Partial<Order>) {
    super(data);
  }

Restart your app with npm start, through the main.controller in your app, you should be able to see the result that's similar to:

For more explanations of the {strict: false } issue, please see the comment and related PRs.

Please let me know if the workaround solves the issue. Thanks!

[Jtmaurya]

@agnes512 @dougal83 Now i am facing the problem related to
result[0].orders it is showing error

[agnes512]

Could you check what result is? And could you post the Order table in your database? thanks.

[Jtmaurya]

[Jtmaurya]

`_@get('/orders/abc/{id}', {
responses: {
'200': {
description: 'Order model instance',
content: {
'application/json': {
schema: getModelSchemaRef(Order, {includeRelations: true}),
},
},
},
},
})
async findById(
@param.path.number('id') id: number,
@param.query.object('filter', getFilterSchemaFor(Order)) filter?: Filter
): Promise {
const res = await this.orderRepository.find(
{include: [
{relation: 'user'}
]
});
return res[0];// this part is showing error
}

}`_

Now , if i am writing <res[0].order> or <res[0].user> then it is showing error

[agnes512]

@Jtmaurya I pushed my fix in https://github.com/agnes512/dummy2. Could you try it on your end? I also have 2 examples in the main controller. If everything goes well, that endpoint should create one User and one Order instances. And it traverses the user instance with related orders and the order instance with related user:

    // the following is for hasMany relation:
    const resultUser = await this.userRepository.find({
      where: {id: myUser.id},
      include: [{relation: 'orders'}],
    });
    console.log(resultUser);
    // the following is for belongsTo relation:
    const resultOrder = await this.orderRepository.find({
      where: {orderId: order.orderId},
      include: [{relation: 'user'}],
    });
    console.log(resultOrder);

[Jtmaurya]

In the above code we will get user along with the corresponding orders and vice-versa.
But what if i want to take only totalPrice along with the user details, not the whole details of order.
In that case, what should i do?

[agnes512]

I don't think you can return a instance with user details along with order details together directly. However, your can manipulate the returned object. For example, if you only want the totalPrice field to be returned, you can use the field clause in the scope:

    const resultUser = await this.userRepository.find({
      where: {id: myUser.id},
      include: [{
            relation: 'orders', 
            scope: {fields: {totalPrice: true, userId: true}},
      }],
    });

With this query, the returned related Order instance would only contain these 2 fields. NOTICE that you will need to include the foreign key userId for the inclusion to work properly. See #3453 (comment) and usage of the field clause: https://loopback.io/doc/en/lb3/Fields-filter.html

returned object: 
{ id: 1, 
   firstName: 'J', 
   lastName:'T', 
   email: '[email protected]', 
   orders: [
         { totalPrice: 'an order', userId: 1, orderId: undefined, quantity: undefined }
    ]
}

However, as I mentioned above, if you want to take only the totalPrice field along with the user instance, not the whole details of the related orders, I don't think we have a way to return it directly. But you can still manipulate the returned object to match your requirement. ( field clause is not needed)

Take the resultUser as an example, resultUser[0] is the target user that we want. And since the related models ordes is an array, resultUser[0].orders[0] gives you the first related orders of this user.

    const resultUser = await this.userRepository.find({
      where: {id: myUser.id},
      include: [{
            relation: 'orders', 
      }],
    });

      const user = resultUser[0];

      const userWithTotalPrice = {
        id: user.id,
        name: user.name,
        parentId: user.parentId,
        totalPrice: user.orders[0].totalPrice,   // from the related model
      };
      console.log(userWithTotalPrice);

Don't forget that in HasMany relation, the related model is an array. You can use scope clause to constraint the related model.

Similarly, the related model of BelongsTo relation is an object. To include something from the related User, you can do:

      const order = resultOrder[0];

      const orderWithUserEmail = {
        orderId: order.orderId,
        quantity: order.name,
        totalPrice: order.totalPrice,
        userId: order.userId,
        email: order.user.email  // from the related model
      };
      console.log(userWithTotalPrice);

[agnes512]

I am closing this issue as no response. The migration isssue is being tracked in #4744