Query Builder Laravel Left Join-CodePudding

I'm using laravel 8, and I make a query builder to make a left join, when laravel execute the query show me a error, but in SQL Server MS execute correctly

SQLSTATE[42S22]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name '2'. (SQL: select [a].[id] as [id1], [b].[id] as [id2], [a].[reg], [a].[parte], [a].[ubiclinea], [a].[descripcion], [a].[numConteo] as [conteo1], [a].[cantidad] as [cantidad1], [a].[counter] as [contador1], [a].[created_at] as [created1], [b].[numConteo] as [conteo2], [b].[cantidad] as [cantidad2], [b].[counter] as [contador2], [b].[created_at] as [created2] from [directosCount] as [a] left join [directosCount] as [b] on [a].[reg] = [b].[reg] and [b].[numConteo] = [2] and [b].[deleted_at] is null where [a].[numConteo] = 1 and [a].[deleted_at] is null)

that is the error that show

but I copy the query and execute in SQL Server and it works

my query in laravel is this

DB::table('directosCount as a')
->leftJoin('directosCount as b', function($join)
{
    $join->on('a.reg', '=', 'b.reg');
    $join->on('b.numConteo', '=', '2')->whereNull('b.deleted_at');
})
->select('a.id as id1', 'b.id as id2', 'a.reg', 'a.parte', 'a.ubiclinea', 'a.descripcion', 'a.numConteo as conteo1', 'a.cantidad as cantidad1', 'a.counter as contador1', 'a.created_at as created1', 'b.numConteo as conteo2', 'b.cantidad as cantidad2', 'b.counter as contador2', 'b.created_at as created2')
->where('a.numConteo', '1')
->whereNull('a.deleted_at')
->get();

hope someone can help me

CodePudding user response：

i think this should be outside of join

$join->on('b.numConteo', '=', '2')->whereNull('b.deleted_at');

please change on to where

->where('b.numConteo', '=', '2')->whereNull('b.deleted_at');

CodePudding user response：

You have 2 on statements in your join. Try this

DB::table('directosCount as a')
->leftJoin('directosCount as b', function($join)
{
    $join->on('a.reg', '=', 'b.reg')
       ->where('b.numConteo', '=', '2')
       ->whereNull('b.deleted_at');
})
->select('a.id as id1', 'b.id as id2', 'a.reg', 'a.parte', 'a.ubiclinea', 'a.descripcion', 'a.numConteo as conteo1', 'a.cantidad as cantidad1', 'a.counter as contador1', 'a.created_at as created1', 'b.numConteo as conteo2', 'b.cantidad as cantidad2', 'b.counter as contador2', 'b.created_at as created2')
->where('a.numConteo', '1')
->whereNull('a.deleted_at')
->get();