How to write this SQL with Adonis query Builder?

Question

Hello, I’m starting with Adonis and I have problems/difficulty to assemble a query. My simplified Sql is like this:

SELECT DISTINCT dtd.id_disciplina, dis.nome_disciplina
FROM  tbl_docente_turma_disciplina dtd, tbl_disciplina dis
WHERE dtd.id_ie = 1
AND  dtd.id_disciplina = dis.id_disciplina

I tried some things like:

  const Table = Database.table('tbl_docente_turma_disciplina as dtd', 'tbl_disciplina as dis')

            return await Table
                        .distinct('dtd.id_disciplina', 'dis.nome_disciplina')
                        .where('dtd.id_ie', 1)
                        .where('dtd.id_disciplina', 'dis.id_disciplina')

But with mistakes:

 ER_BAD_FIELD_ERROR: Unknown column 'dis.nome_disciplina' in 'field list'

From what I could notice I can access the prefix fields (dtd) but not the prefix fields (dis)

Can anyone help me how to write something like this on Adonis? Thank you!!

Answer 1

Hello, have you created Models for each of these tables? If you can’t do it as follows:

Disciplina.js

'use strict'
const Model = use('Model')

class Disciplina extends Model {

  static get table() {
    return 'tbl_disciplina'
 }

 DocenteTurmaDisciplina() {
    return this.hasMany('App/Models/DocenteTurmaDisciplina', 'id_disciplina', 'id_disciplina')
 }
}

In the model Docenteadisciplina.js as follows:

'use strict'
const Model = use('Model')

class Disciplina extends Model {

  static get table() {
    return 'tbl_docente_turma_disciplina'
 }
}

Thus you will create the relationship of the Model with the table, and of Disciplines with the name of the teachers.

Your controller query will look something like this:

Already understanding that you imported the models

const Disciplina = use('App/Models/Disciplina')
const DocenteTurmaDisciplina = use("App/Models/DocenteTurmaDisciplina")

Query:

const query = Disciplina.query()

query.with('DocenteTurmaDisciplina', (builder) => {
  builder.where('id_ie', 1)
})

query.distinct('tbl_docente_turma_disciplina.id_disciplina', 'tbl_disciplina.nome_disciplina')

The explanation for this is here: https://adonisjs.com/docs/4.1/relationships#_has_many

PS: in the models you have to put the Primary key in the primaryKey method, check if you will need to change this in the models.

See you around

Answer 2

Test like this and see if anything comes back

return await Database
    .table('tbl_docente_turma_disciplina')
        .innerJoin('tbl_disciplina',
                   'tbl_docente_turma_disciplina.id_disciplina',
                   'tbl_disciplina.nome_disciplina')
    }).where('tbl_docente_turma_disciplina.id',1)