Update function negates leftJoin statements when using SQLite

[dschreij]

Hi,
I am currently on Adonis 4.1 using Sqlite3 5.0.0 (but the below problem also occurs for v4) running on Ubuntu 20.04. I have the weird problem that calling the update function on the query builder erases inner joins that have been stated before.

This code:

await Database.transaction(async (trx) => {
  const query = trx.table('job_states')
    .leftJoin('jobs', 'job_states.job_id', 'jobs.id')
    .leftJoin('studies', 'jobs.study_id', 'studies.id')
    .where('studies.id', id)
    .whereBetween('jobs.position', [from, (to - 1)])
  if (participant) {
    query.leftJoin('participants', 'job_states.participant_id', 'participants.id')
    query.where('participants.identifier', participant)
  }
  console.log(query.toQuery())
  const updateQuery = query.update({ status_id: state })
  console.log('====')
  console.log(updateQuery.toQuery())
  rowsUpdated = await updateQuery
})

Prints:

select * from `job_states` left join `jobs` on `job_states`.`job_id` = `jobs`.`id` left join `studies` on `jobs`.`study_id` = `studies`.`id` left join `participants` on `job_states`.`participant_id` = `participants`.`id` where `studies`.`id` = '1' and `jobs`.`position` between 1 and 2 and `participants`.`identifier` = 'dwxbajdF'
====
update `job_states` set `status_id` = 2 where `studies`.`id` = '1' and `jobs`.`position` between 1 and 2 and `participants`.`identifier` = 'dwxbajdF'

As you can see, there is no trace of the joins anymore in the second print output (except for the aliases). This works fine when using MySQL and appears to be SQLite specific. Does anyone have a clue why this happens?

for completeness sake, here are the packages I use:

{
  "dependencies": {
    "@adonisjs/ace": "^5.0.8",
    "@adonisjs/auth": "^3.1.0",
    "@adonisjs/bodyparser": "^2.0.9",
    "@adonisjs/cors": "^1.0.2",
    "@adonisjs/fold": "^4.0.5",
    "@adonisjs/framework": "^5.0.13",
    "@adonisjs/ignitor": "^2.0.8",
    "@adonisjs/lucid": "^6.2.0",
    "@adonisjs/mail": "^3.0.10",
    "@adonisjs/persona": "^1.0.5",
    "@adonisjs/session": "^1.0.19",
    "@adonisjs/shield": "^1.0.4",
    "@adonisjs/validator": "^5.0.6",
    "@nuxtjs/auth": "^4.9.1",
    "@nuxtjs/axios": "^5.12.0",
    "@nuxtjs/dotenv": "^1.4.0",
    "@vuex-orm/core": "^0.36.3",
    "@vuex-orm/plugin-axios": "^0.9.3",
    "adonis-bumblebee": "^2.2.0",
    "adonis-swagger": "github:ahmadarif/adonis-swagger#v1.3.0",
    "cross-env": "^7.0.2",
    "date-fns": "^2.15.0",
    "mysql2": "^2.1.0",
    "nuxt": "^2.14.0",
    "nuxt-i18n": "^6.13.1",
    "sqlite3": "^5.0.0",
    "swagger-jsdoc": "^4.0.0",
    "validator": "^13.1.1",
    "vue-fragment": "^1.5.1",
    "vuex-pathify": "^1.4.1",
    "vuex-router-sync": "^5.0.0"
  },
  "devDependencies": {
    "@babel/plugin-proposal-class-properties": "^7.10.4",
    "@mdi/font": "^5.4.55",
    "@nuxtjs/eslint-config": "^2.0.2",
    "@nuxtjs/eslint-module": "^2.0.0",
    "@nuxtjs/vuetify": "^1.11.2",
    "@vue/test-utils": "^1.0.3",
    "babel-core": "^7.0.0-bridge.0",
    "babel-eslint": "^10.1.0",
    "babel-jest": "^26.1.0",
    "eslint": "^6.8.0",
    "eslint-plugin-nuxt": "^1.0.0",
    "eslint-plugin-vue": "^6.2.2",
    "eslint-plugin-vuetify": "^1.0.0-beta.7",
    "flush-promises": "^1.0.2",
    "jest": "^26.1.0",
    "jest-serializer-vue": "^2.0.2",
    "jest-transform-stub": "^2.0.0",
    "nodemon": "^2.0.4",
    "ts-jest": "^26.1.4",
    "typescript": "^3.9.7",
    "vue-jest": "^3.0.6"
  }
}

[dschreij]

Hmm, I just found this: knex/knex#2796 (comment). It is a limitation of SQLite then?

[dschreij]

It seems like something that is SQLite / Knex specific so marking this as answered.