Fixed: SQLSTATE[23000]: Integrity constraint violation: 1052

Hello Friends!

In Magento 2, sometimes we can face the following issue due to a column that you have displayed in the custom grid or added in an existing grid. For reference, please look into the following images:

This issue occurs when more than one table are having the same column name and in a join query, the column name is being used without the alias of the table name.

To fix this issue, we can get where part of the collection query and modify it as in the following code:

 // Fix column in where clause is ambiguous error
   $collection = $this->getCollection(); // get collection of main table
   $select = $collection->getSelect();
   $where = $select->getPart('where');
   foreach ($where as &$item) {
      if (strpos($item, '(`created_at`') !== false) {
         $item = str_replace('`created_at`', '`main_table`.`created_at`', $item);
      }
   }
   $select->setPart('where', $where);

Note: You can use your custom column name in place of ‘created_at‘ and use the custom table name in place of ‘main_table‘ according to your need.

Hope this will be helpful. Thanks 🙂