Lucid : how to use ORM relationships to make join requests

[ThomasPrioul]

Hi, I'm trying to leverage the ORM and relationships of Lucid instead of using .join() in the query builder.
In join(), I have to use table names and column names, which for me loses the added value of an ORM.

Here is the relevant model :

class Stf extends BaseModel {
  public static table = 'stfs'

  @column({ isPrimary: true })
  public id: number

  @column()
  public name: string
}

class Engin extends BaseModel {
  public static table = 'engins'

  @column({ isPrimary: true })
  public id: number

  @column()
  public invariantOsmose: number

  @belongsTo(() => Stf, {
    foreignKey: 'stfActive'
  })
  public stf: BelongsTo<typeof Stf>

  @column()
  public stfActive: number
}

class Intervention extends BaseModel {
  public static table = 'interventions'

  @column({ isPrimary: true })
  public id: number

  @belongsTo(() => Engin, {
    localKey: 'invariantOsmose',
    foreignKey: 'enginInvariant'
  })
  public mr: BelongsTo<typeof Engin>

  @column()
  public enginInvariant: number
}

I want to filter my Interventions based on a criteria not directly present in the table.
The Intervention references an Engin which itself references a Stf which has the column of interest.

This is a working code using the ModelQueryBuilder :

query = Intervention.query().whereHas(('mr'), (mr) => {
      mr.whereHas('stf', (stf) => {
        stf.where('name', 'SPE')
      })
    }).preload('mr', mr => mr.preload('stf'))

It creates the following output:

I have to use preload() otherwise the mr field and it's stf field are not populated.

The problem is that under the hood Lucid (and Knex) does 3 requests to achieve this result:

select * from `interventions` where exists (
    select * from `engins` where (
	    exists (
		    select * from `stfs`
			where (`name` = 'SPE')
			and (`stfs`.`id` = `engins`.`stf_active`)
		)
	)
	and (`engins`.`invariant_osmose` = `interventions`.`engin_invariant`)
);
select * from `engins` where `invariant_osmose` in (222222)
select * from `stfs` where `id` in (2)

When the same thing can be expressed like this in pure SQL:

select * from interventions
inner join engins on interventions.engin_invariant = engins.invariant_osmose
inner join stfs on engins.stf_active = stfs.id
where stfs.name = 'SPE'

Here are my questions :

1. Can I configure Lucid or use another method to make it use JOIN clauses instead of where exists ?
2. How can I get rid of the preload() calls which create the additional requests ?

[ruanitto]

You tried this package?

https://github.com/chirgjin/adonisjs-select-related