LoopBack 4 REST API example for getting record with Mysql

Question

i'm learning loopback 4, i have created model, repository and datasource, it also connected to mysql and i can retrieve results from http://127.0.0.1:3000/myapi/{id}

in my default example getting by id is :

@get('/citySchedule/{id}', {
    responses: {
      '200': {
        description: 'Schedule model instance',
        content: {'application/json': {schema: {'x-ts-type': Schedule}}},
      },
    },
  })
  async findById(@param.path.number('id') id: number): Promise<Schedule> {
    return await this.ScheduleRepository.findById(id);
  }

However, i didnt found any tutorial for getting data with more parameters.

let say mysql table of schedule has contain column id, city_name, city_code, date, task, item.

for example, i want to get "SELECT task, item FROM schedule WHERE city_code=123 AND date=2019-05-01"

my question, how to write code to get those data at loopback controller ? any example code...

my expectations, i can query from my api :

http://127.0.0.1:3000/myapi/{city_code}/{date}/ to get data results or

http://127.0.0.1:3000/myapi/{city_name}/{date}/

Answer 1

If you have generated your controller using loopback cli, you must have got another method in controller class like this

@get('/citySchedule', {
    responses: {
      '200': {
        description: 'Array of Schedule model instances',
        content: {
          'application/json': {
            schema: {type: 'array', items: {'x-ts-type': Schedule}},
          },
        },
      },
    },
  })
  async find(
    @param.query.object('filter', getFilterSchemaFor(Schedule)) filter?: Filter,
  ): Promise<Schedule[]> {
    return await this.ScheduleRepository.find(filter);
  }

You can use this API to fetch more filtered data.

Considering your example

SELECT task, item FROM schedule WHERE city_code=123 AND date=2019-05-01

for this query, you need to hit the API like this.

GET /citySchedule?filter=%7B%22where%22%3A%7B%22city_code%22%3A123%2C%22date%22%3A%222019-05-01%22%7D%2C%22fields%22%3A%7B%22task%22%3Atrue%2C%22item%22%3Atrue%7D%7D

Here, the filter query parameter value is actually url encoded string for the below json string

{
    "where":{
        "city_code":123,
        "date":"2019-05-01"
    },
    "fields":{
        "task":true,
        "item":true
    }
}