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