Issue with pivot table

[lokendrajoshi54]

While fetching relations facing issue with pivot table. When I use "whereHas" on model it does not look deleted_at in pivot table. Please check below example:

Route.query().whereHas('regions', (builder) => {
builder.where('regions.id', queryParams.ward_id)
})
there is pivot table "route_regions" which holds "route_id" and "region_id" and for some rows in "route_regions" table there are some entries which have not null value for deleted_at. But above code fetching all records (including relations for which mappings are soft deleted).
I have to add condition for this in every query (Please check below):
Route.query().whereHas('regions', (builder) => {
builder.where('regions.id', queryParams.ward_id)
builder.whereNull('route_regions.deleted_at')
})
Is there anything I missed or Is there any workaround for this problem?

[radmen]

Hey, thanks for feedback!

It's a longshot, but have you tried to use pivotModel? You should create one and attach to it @provider:Lucid/SoftDeletes trait.

[lokendrajoshi54]

Hi, Thanks for reply,

I tried following but didn't work:
Model.addGlobalScope(
query => {
query.whereNull(${Model.table}.deleted_at)
if (query._PivotModel && query.relatedQuery) {
query.whereNull(${query._PivotModel.table}.deleted_at);
}
},
'soft_deletes'
)

[radmen]

I'm not sure what you're trying to achieve. From the given code I don't see that you're using SoftDeletes trait. Also I'm not able to determine if you created a pivot model.

Could you give a more complete example of your models? Something that would allow me to recreate the problem? Thanks.

[lokendrajoshi54]

Sorry for late reply. Please check example below in my case:
Model:

`const Model = use('Model')

class GpsDevice extends Model {
static boot () {
super.boot()
this.addTrait('@Provider:Lucid/SoftDeletes')
}

vehicles() {
return this.belongsToMany('App/Models/Vehicle').pivotModel('App/Models/VehicleGpsdevice')
}

}`

Query:
GpsDevice.query().whereHas('vehicles', (builder) => {
builder.where('vehicle_type_id', 2)
}).with(vehicles).fetch()

The above query fetching records with deleted mappings in vehiclegpsdevice model. It seems its not checking deleted_at on pivot table. Can you please help me in that. Thanks

[radmen]

@lokendrajoshi54 thanks for the code example. Could you also post contents of VehicleGpsdevice model (at least boot() method of that model)? Thanks!

[lokendrajoshi54]

Sure @radmen here is content of VehicleGpsdevice Model:
const Model = use('Model')

class VehicleGpsdevice extends Model {
static boot () {
super.boot()
this.addTrait('@Provider:Lucid/SoftDeletes')
}
..
..
}

module.exports = VehicleGpsdevice

Same issue I faced with withTrashed() method, I'm trying to figure out if I missed anything, if you can tell me it would be helpful.
Thanks in advance

[radmen]

Thanks for the code. I'll try to check it tomorrow.

[radmen]

@lokendrajoshi54 I did some testing with the examples you gave.

SoftDeletes trait works with pivot model when fetching related records. Here's the test case I used:

adonis-lucid-soft-deletes/test/softDeletes.spec.js

Lines 314 to 336 in 5835dd4

	it('does not include deleted pivots', async () => {
	const user = await User.create({ username: 'Jon' })
	const tags = await Promise.all([
	Tag.create({ title: 'Foo' }),
	Tag.create({ title: 'Bar' })
	])
	await Promise.all(tags.map(model => user.tags().save(model)))
	await lucid.db.table('user_tags')
	.where({ tag_id: tags[0].id })
	.update({ deleted_at: new Date() })
	const userTags = await user.tags().fetch()
	expect(userTags.rows.length).to.equal(1)
	const freshUser = await User.query()
	.with('tags')
	.first()
	expect(freshUser.getRelated('tags').rows.length).to.equal(1)
	})

There's a problem with applying SoftDeletes global scope in whereHas(). Test case with example:

adonis-lucid-soft-deletes/test/softDeletes.spec.js

Lines 339 to 359 in 5835dd4

	it.skip('appends scope to whereHas() statement', async () => {
	const user = await User.create({ username: 'Jon' })
	const tags = await Promise.all([
	Tag.create({ title: 'Foo' }),
	Tag.create({ title: 'Bar' })
	])
	await Promise.all(tags.map(model => user.tags().save(model)))
	await lucid.db.table('user_tags')
	.where({ tag_id: tags[0].id })
	.update({ deleted_at: new Date() })
	const freshUser = await User.query()
	.whereHas('tags', (query) => {
	query.where('title', 'Foo')
	})
	.first()
	expect(freshUser).to.be.undefined // eslint-disable-line
	})

So, TLDR:

• trait should work without any problem when fetching related items
• trait fails to apply its scope in whereHas() query; most likely it's a bug in Lucid

I'll try to verify if issues with whereHas() are indeed Lucid-related.

[lokendrajoshi54]

Hello @radmen

Thanks for your reply and looking into issue. Same thing is applicable for withTrashed() method.

Is there any way I can help in this?

[radmen]

Same thing is applicable for withTrashed() method.

In which context does this apply? whereHas()?

Is there any way I can help in this?

First thing we need to determine is whether this is an issue with the package or Lucid. If you think that the problem is with the package you could try writing a test case which fails - this would help me finding the bug,j

[radmen]

I was able to reproduce the problem on clean Adonis application. Its been reported: adonisjs/lucid#429

[lokendrajoshi54]

@radmen Thanks for looking into this.

[lokendrajoshi54]

@radmen withTrashed() method is not working inside with method. Here is the snippet:
let vehicles = await Vehicle.query().whereHas('gps_devices').with('gps_devices', (build) => {build.withTrashed()}).fetch()

[radmen]

@lokendrajoshi54 I'm aware of that. In fact it seems that this is something that is not possible in Lucid.

I've asked about it, yet without any answer.