Problems with SQL query with Knex in React

Question

Next I have two tables one of Students and another of users and I am trying to make a simple login call by checking the user’s email and password through the foreign key between Students and user. But it’s only returning to me empty, someone could help me?

    async create(request, response) {
    const { email, password } = request.body;

    const type = 's'; 


    const student = await connection('users as u')
        .innerJoin('students as s')
        .where('s.email', email)
        .andWhere('s.id', 'u.student_id')
        .andWhere('u.password', password)
        .andWhere('u.type', type)
        .select('*')
        

        console.log(student);   

        if(!student) {
             return response.status(400).json({ error: 'No Student found with this Email' });
        }

        return response.json(null);
}

I made the change as you said Thainam but it is still returning empty. The structure is now like this

const student = await connection('users as u')
        .innerJoin('students as s', function(){
            this.on('s.cpf', '=', 'u.student_id')
        })
        .where('s.email', email)
        .andWhere('s.cpf', 'u.student_id')
        .andWhere('u.password', password)
        .andWhere('u.type', type)
        .select('s.email')

Answer 1

Hello, friend. You are not lacking to reference the innerJoin with the ON ?

Knex is not my area, but looking quickly at the documentation, they give this example:

.innerJoin('accounts', function() {
  this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})

Another tip, refer to ' * ' to avoid field conflicts. Ex: . select('u.*')

Reference: http://knexjs.org/#Builder-innerJoin