Laravel Undefined column: 7 ERROR: column "1" does not exist

Question

In the join, I am passing a direct value, and Laravel is trying to "read" this value as if it were a table.

function ($join) {

    $join
       ->on('e.pee_fk_pes_codigo', '=', 'p.pes_codigo')
       ->on('e.pee_padrao', '=', '1');
});

Answer 1

Use of DB::Raw()

The class JoinClause (used internally within the Closure, that you called in join, by Laravel) by default expects value with the name of the fields of the related tables.

To use a number, you can use DB::raw(). Thus:

function ($join) {

    $join
        ->on('e.pee_fk_pes_codigo', '=', 'p.pes_codigo')
        ->on('e.pee_padrao', '=', \DB::raw('1'));
})

DB:raw will cause Laravel to accept an SQL expression to be passed as a on.

Use of the Where

In another form, which I believe is the most suitable, you can also use the clause where instead of on:

function ($join) {

    $join
        ->on('e.pee_fk_pes_codigo', '=', 'p.pes_codigo')
        ->where('e.pee_padrao', '=', '1');
})

See how the result is compiled in SQL:

$sqlString = Usuario::join('niveis', function ($j) { 

    $j->on('niveis.id', '=', 'usuarios.nivel_id')
       ->where('niveis.id', '=', 4); 
       
})->toSql();

 echo $sqlString;

Upshot:

select * from 
    `usuarios`

inner join 
    `niveis` on `niveis`.`id` = `usuarios`.`nivel_id` 
    and `niveis`.`id` = ?