Saturday, 10 December 2022

Query using Knex - withGraphFetched and where clause

I need to search the database using Knex, returning only sales in a given city. For this,

I have two tables:

sale: 
id
drugstore_id

drugstore:
id
name
city

My models:

class Sale extends Model {
  static modelPaths = [__dirname]

  static get tableName() {
    return 'sale'
  }

  static relationMappings = {
    drugstore: {
      relation: Model.BelongsToOneRelation,
      modelClass: Drugstore,
      join: {
        to: 'drugstore.id',
        from: 'sale.drugstore_id',
      },
    },
  }
}


class Drugstore extends Model {
  static get tableName() {
    return 'drugstore'
  }

  static relationMappings = {
    sale: {
      relation: Model.HasManyRelation,
      modelClass: Sale,
      join: {
        to: 'sale.drugstore_id',
        from: 'drugstore.id',
      },
    },
  }
}

I'm running the query like this:

this.repository
  .query()
  .select('id', 'drugstore_id')
  .withGraphFetched('[drugstore(drugstoreByCity, drugstoreSelect)]')
  .modifiers({
    drugstoreByCity(builder) {
      builder.where('city', 'Sorocaba')
    },
    drugstoreSelect(builder) {
      builder.select('name', 'city')
    },
  })

I get the following array in return:

[
    {
        "id": 1503,
        "drugstore_id": "36",
        "drugstore": {
            "name": "Farmácia Teste",
            "city": "Sorocaba"
        }
    },
    {
        "id": 1502,
        "drugstore_id": "14",
        "drugstore": null
    }
]

The first object was returned, because the city is the same as "Sorocaba", however, the second one also returned, but in the second the city is different. What I need is that it only returns the sale of drugstores in a certain city.



from Query using Knex - withGraphFetched and where clause

No comments:

Post a Comment