1
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!!
What have you tried? What difficulties have you had? Any mistakes?
– Costamilam
I forgot to put my tests. I updated my post. Thanks for the return!
– AndersonL
Apparently Adonis does not support Cartesian product, it is mandatory to use JOIN
– Costamilam
Putz, I really took a long time to understand this, I ended up creating a view in mysql because I don’t know much about SQL to do this adaptation. Thank you for the reply.
– AndersonL