Why not return the id when I put a leftjoin?

Hello guys, I have a problem that I couldn’t identify.

I had to put a leftjoin in the query to be able to order the results the way I want, but as soon as I put the leftjoin, adonis returns “null” in the main id, look:

this way it returns with the Physician id null

const physiciansQuery = Physician.query()
        .where({ status: 'approved' })
        .with('user.profile.avatar')
        .with('user.addresses')
        .with('user.favoritesPatients', builder => {
          builder.where('patient_id', user.id)
        }).leftJoin('patient_physician as pp','physicians.user_id','pp.patient_id')
        .orderBy('pp.id','desc')
        .paginate(page, 20)

that way returns normal, but not ordered the way I want

const physiciansQuery = Physician.query()
        .where({ status: 'approved' })
        .with('user.profile.avatar')
        .with('user.addresses')
        .with('user.favoritesPatients', builder => {
          builder.where('patient_id', user.id)
        }).paginate(page, 20)

Can anyone tell me why this is? Or how to solve?

1 Like

I managed to solve by putting a .select('physicians.*') But I still don’t understand why it doesn’t work without informing the select.

1 Like

Does favoritePatients also refer to patient_phyisician? If yes, it may be because of a conflicting join. Did you check out the generated query? (DEBUG=knex:query in .env file)

1 Like