1060 Duplicate Column Error since update to 8.5.1

[arnoldjp57]

Hi

I have done a composer update and laravel datatables updated to 8.5.1.

Most of my queries are now throwing errors

[2018-05-14 02:10:43] LIVE.ERROR: PDOException: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id' in /var/www/html/odie55/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:80 Stack trace:

0 /var/www/html/odie55/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(80): PDO->prepare('select count(*)...', Array)

Please can someone point me in the right direction

This Query causes an the above error ...

$icdcs = Icdc::leftJoin('locks as a1', function ($join) {
                $join->on('icdcs.id','=','a1.obid')
                  ->where('a1.appl',get_base_model('icdc')->id)
                  ->orWhere('a1.id',null);
                })
                ->Leftjoin('peops as a2', function ($join) {
                $join->on('a1.peop','=','a2.id') 
                  ->where('a2.id','!=',null)
                  ->orWhere('a2.id',null);
                })
                ->leftJoin('pmbis as a3', function ($join) {
                $join->on('icdcs.id','=','a3.icdc')
                  ->where('a3.id','>',0)
                  ->orWhere('a3.id',null);
                })  
                ->select('icdcs.id as id',
                        'icdcs.code as code',
                        'icdcs.desc as desc',
                        'icdcs.vald as vald',
                        'icdcs.valp as valp',
                        'icdcs.pmbi as pmbi',
                  DB::raw('icdcs.id, case when a1.id > 0 then concat("Locked by ",a2.name," at ",a1.created_at) else "" end as lockMess'),
                  DB::raw('icdcs.id, case when a3.id > 0 then "Yes"  else "No" end as pmbc'));
      return Datatables::of($icdcs)->make(true);

System details

• UBUNTU 16.04
• PHP 7.1
• Laravel 5.5
• Laravel-Datatables 8.5.1

[TheClassified]

Same issue here. Just roll back to 8.5 until it's fixed.

[arnoldjp57]

Hi ... yup ... done that .... what is the correct way to roll back ... I tried updating composer.json to use 8.5.0 and did a composer update, but nothing rolled back ... so in desperation I restored the yajra directory in vendor .... please advise Regards Arnold

[TheClassified]

Don't close the issue, it's still an issue.

[yajra]

I suspect this is related to this PR #1737. Can you please verify by removing join on complex queries? Will revert the PR if confirmed. Thanks and sorry for the inconvenience.

[yajra]

BTW, can you please paste the generated queries so that we can pinpoint on what part the queries failed.

[TheClassified]

I just updated my project to 8.5.1 again, so that I can specify the issues a bit more.

The issue only persists on tables with complex queries, that use belongsToMany relations and so on.

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'created_at' (SQL:
select count(*) as aggregate from (select * from `suppliers` 
inner join `supplier_manufacturer_to_meta` on `suppliers`.`id` = `supplier_manufacturer_to_meta`.`supplier_id` 
where `supplier_manufacturer_to_meta`.`meta_id` = 1) count_row_table)

[yajra]

@TheClassified thanks for the feedback. Were you able to test by removing join on the array list? Does it fixes your issue?

[TheClassified]

Removing the join from the isComplexQuery function solves the issue, yes.

Also seems to only be an issue on ManyToMany relationships with pivot tables unless they're manually defined.

[yajra]

Reverted on v8.5.2, thanks!

[arnoldjp57]

Hi This is one of the failing queries .... In case you still need it

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id' (SQL: 
select count(*) as aggregate from (
select `icdcs`.`id` as `id`, `icdcs`.`code` as `code`, `icdcs`.`desc` as `desc`, `icdcs`.`vald` as `vald`, `icdcs`.`valp` as `valp`, `icdcs`.`pmbi` as `pmbi`, icdcs.id, 
case when a1.id > 0 then concat("Locked by ",a2.name," at ",a1.created_at) else "" end as lockMess, icdcs.id, 
case when a3.id > 0 then "Yes"  else "No" end as pmbc from `icdcs` 
left join `locks` as `a1` on `icdcs`.`id` = `a1`.`obid` and `a1`.`appl` = 20 or `a1`.`id` is null 
left join `peops` as `a2` on `a1`.`peop` = `a2`.`id` and `a2`.`id` is not null or `a2`.`id` is null 
left join `pmbis` as `a3` on `icdcs`.`id` = `a3`.`icdc` and `a3`.`id` > 0 or `a3`.`id` is null) count_row_table)

Regards Arnold

[yajra]

@arnoldjp57 thanks for the feedback. Will get back to this when I got the chance. I think making join as complex query should not cause an issue but it does. :)