Unable to include nested object properties in fields and in where condition in lb4

[lakshmansai1980]

Steps to reproduce

1. Create an entity with nested model. Ex : Employee :

{
  empNo: string,
  empName: string,
  deptId: string,
  location: Location{ address1: string, address2:string, locality:string}
}

this.employeeRepository.find({
  fields: {empName: true, departmentId: true, 'location.locality' : true}, 
  where: {'location.locality': {regexp: pattern}}
});

Current Behavior

Not accepting nested object properties to include in select fields or in where condition

Expected Behavior

I should be able to include nested object properties in where or in fields to select ..

Link to reproduction sandbox

not available

Additional information

node -e 'console.log(process.platform, process.arch, process.versions.node)' win32 x64 12.13.0

npm ls --prod --depth 0 | grep loopback

+-- @loopback/[email protected] +-- @loopback/[email protected] +-- @loopback/[email protected] +-- @loopback/[email protected] +-- @loopback/[email protected] +-- @loopback/[email protected] +-- @loopback/[email protected] +-- @loopback/[email protected] +-- @loopback/[email protected] +-- [email protected] `-- [email protected]

[agnes512]

@lakshmansai1980 If you'd like to filter some properties in the object location, you will need to include location in the result. i.e set the whole object to true in the fields filter.

this.employeeRepository.find({fields: {empName: true, departmentId: true, location : true}, where: {'location.locality': {regexp: pattern}}});

[lakshmansai1980]

Hi @agnes512 , Issue what I see here, location.locality is showing as undefined and showing compilation issue for me. This is pretty required for filtering data from the nested objects..

this.employeeRepository.find({fields: {empName: true, location: true}, where: {'location.address1': {regexp: pattern}}});

Showing compilation issue near 'location.address1'. It is not able to recognize nested properties

[lakshmansai1980]

Any updates on this issue pls .. i am looking for the solution for this issue.

[achrinza]

Apologies for the delayed reply, have you tried the following?

this.employeeRepository.find({
  fields: {
    empName: true,
    departmentId: true,
    location : true
  }, 
  where: {
    location: {
      locality: {regexp: pattern}
    }
  }
);

[lakshmansai1980]

Hi @achrinza, I did tried the above approach and it does't looks to be working.

Any solution would be appreciated. This is required for search logic.

[agnes512]

Current status (in MongoDB):

• for LB3, can use nested fields to fetch data from database, but it fails to create an instance with nested fields.
• for LB4, juggler fails to parse the nested fields. Once it is fixed, it has the same issue as lb3

@lakshmansai1980 I haven't run into any compilation issue.

[agnes512]

https://github.com/strongloop/loopback-datasource-juggler/blob/master/lib/dao.js#L207

[lakshmansai1980]

I am still getting compilation. I am not able to include in fields or where condition. Can we please have a look. I am not sure whether any wrong from end

Type '{ "location.address1": string; }' is not assignable to type 'Condition<Employee> | AndClause<Employee> | OrClause<Employee> | undefined'. Object literal may only specify known properties, and '"location.address1"' does not exist in type 'Where<Employee>'.ts(2322)

src/controllers/employee.controller.ts:91:86 - error TS2322: Type '{ "location.address1": string; }' is not assignable to type 'Condition<Employee> | AndClause<Employee> | OrClause<Employee> | undefined'. Object literal may only specify known properties, and '"location.address1"' does not exist in type 'Where<Employee>'.

91 return this.employeeRepository.find({fields: {id: true, location: true}, where: {"location.address1": 'test'}});

[lakshmansai1980]

I have tried below as well. it is still failing:

src/controllers/employee.controller.ts:91:86 - error TS2322: Type '{ address1: string; }' is not assignable to type 'PredicateComparison<Location | undefined> | (Location & string) | (Location & number) | (Location & false) | (Location & true) | (Location & Date) | undefined'. Type '{ address1: string; }' is not assignable to type 'Location & Date'. Type '{ address1: string; }' is missing the following properties from type 'Location': toJSON, toObject

91 return this.employeeRepository.find({fields: {id: true, location: true}, where: {location: {address1: 'test'}}});

[agnes512]

@lakshmansai1980 what I've verified is that the mongodb connector itself is able to fetch correct data from the db with queries, userRepo.find({where: {address.city:'Toronto'}, fields:{'address.city': true}}) for example. i.e execute method works as expected. I didn't run into any incompatible type issues though.

The problem is, even if it fetches the expected data from the db, the juggler doesn't handle it correctly. It fails to parse the returned data into an instance. Juggler deals with all the connectors while the nested fields/nested objects are only supported by a few connectors ( mongo, cloudant), so it is not realistic to change the behavior of the Juggler just for mongo connector. I've talked to the team. I am afraid that we don't have bandwidth to improve it thoroughly. And we think the current solution is to use execute method to handle such cases.

[lakshmansai1980]

@agnes512 , Thanks for clarifying the points. As you mentioned, execute is the only possible options to go about it and should be able to include complex queries as well. I do understand the underline architecture and the complexities involved, as nested objects does supports only in non relational database.

I shall get back to you, If I see any more issues.

[bajtos]

Hi, I believe you are looking for a feature we used to call "Filter on level 2 properties" back in LoopBack 2.x/3.x days, see (see https://github.com/strongloop/loopback/issues/517. I am afraid this feature is still not implemented in LoopBack 4 either.

Also very loosely related: Support for SQL JOIN (INNER JOIN) https://github.com/strongloop/loopback-next/issues/5132

[bajtos]

Cross-posting from https://github.com/strongloop/loopback/issues/517#issuecomment-469072881:

• There is some support in postgres connector
• Mongo also has some basic support (only for dot nested properties)

Cross-posting from a recent Slack thread (thank you @achrinza!): https://loopbackio.slack.com/archives/C01177XQN8N/p1595985757088600?thread_ts=1595965001.082400&cid=C01177XQN8N

IIRC, nested properties can be targeted with dot-delimited syntax:

{
  "where": {
    "attributes.material_one_in": "aluminium"
  }
}

I am afraid I don't have bandwidth to look into this deeper, just posting links that you may find helpful 🙈

[mbnoimi]

@bajtos I faced the same problem here (@achrinza thanks for pointing this issue)

[razyon]

Hi, any news on this issue ?