Problem when used with chunk method

[michele-grifa]

I'm tried to using the package in my project to test it out, but when i use the Laravel chunk method on the query, i get the following error: "Column specified multiple times"

I checked the query and i noticed that this package and the Laravel chunk method, use the alias temp_table and this cause the error.

Thanks.

Laravel Version: 8.74.0
Php Version: 8.0

[staudenmeir]

What's the whole error message?

[michele-grifa]

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'CKY_MERC' is specified multiple times for 'temp_table'. (SQL: with [catTree] as (select * from (select [CKY_MERC] from [BGS_ANAMER] as [root] where [root].[CKY_MERC] = 0060000) as [temp_table] union all select * from (select [childs].[CKY_MERC] from [BGS_ANAMER] as [childs] inner join [catTree] on [catTree].[CKY_MERC] = [childs].[CKY_MERC_PADRE]) as [temp_table]) select * from (select *, row_number() over (order by [articoli].[CKY_ART] asc) as row_num from [BGS_ARTI] as [articoli] inner join [catTree] on [articoli].[CKY_MERC] = [catTree].[CKY_MERC] where ([IFL_ART_CANC] = 0 and [IFL_ART_ANN] = 0)) as temp_table where row_num between 101 and 200 order by row_num)

[staudenmeir]

How are you generating this in Laravel?

[michele-grifa]

Yes

[staudenmeir]

How? What's the query with ->withExpression()?

[michele-grifa]

I forgot to mention that is ->withRecursiveExpression

$query = Articolo::from('BGS_ARTI as articoli')->with('somerelation');
$queryCatMerc = DB::connection('mexal')->table('BGS_ANAMER as root')->select('CKY_MERC')
->where('root.CKY_MERC', '=', '0060000')
->unionAll(
    DB::connection('mexal')->table('BGS_ANAMER as childs')
        ->select('childs.CKY_MERC')
        ->join('catTree', 'catTree.CKY_MERC', '=', 'childs.CKY_MERC_PADRE')
);

$query->join('catTree', 'articoli.CKY_MERC','=','catTree.CKY_MERC')
  ->withRecursiveExpression('catTree', $queryCatMerc);

$query->orderBy('articoli.CKY_ART')->chunk(100, function ($articoli) {
    //Business logic...
});

[staudenmeir]

The issue is that your query selects all columns from both BGS_ARTI/articoli and catTree. Since the cky_merc column appears in both, the database doesn't know which one to use.

You need to limit the selected columns:

Articolo::select('articoli.*')->from('BGS_ARTI as articoli')->...

[michele-grifa]

Thanks the problem is solved