How to create a Join query like this

Hello devs
How to create a Join query like this on query builder?

Select t.voip,t.ramal,t.atendente, d.nome from telefones t 
    join departamentos d on d.id = t.id_departamento
where d.nome = 'Marketing'
1 Like

Hi @ramontvr!

There are several ways.
Easiest drop in replacement for it is to just use raw query

await Database.raw(`
    Select t.voip,t.ramal,t.atendente, d.nome from telefones t 
        join departamentos d on d.id = t.id_departamento
    where d.nome = 'Marketing'
`);

With query builder

await Database
    .select('t.voip')
    .select('t.ramal')
    .select('t.atendente')
    .select('d.nome')
    .from('telefones t')
    .join('departamentos d', 'd.id', 't.id_departamento')
    .where('d.nome', 'Marketing')

Or with lucid models

await Telefone.query()
    .with('departamentos', (builder) => {
        builder.where('name', 'Marketing')
    })
    .fetch()

But for last one you need to set up Lucid relationships

2 Likes

Thanks very much.
One problem. When I put my database nome when use “raw” method , it shows:

Telefone.raw is not a function

Im trying:

const ckpj = await Telefone.raw(`
    Select t.voip,t.ramal,t.atendente, d.nome from telefones t 
        join departamentos d on d.id = t.id_departamento
    where d.nome = 'CPD'
                `);

As he mentioned, it should be Database.raw() not Telephone.raw()

1 Like

I see.
So how do i define “Database” to start do use queries in my “Telefone” table

its showing:

Database is not defined

Did you import it ?

const Database = use('Database')

2 Likes