After join the customer name with custom module, i can't filter the grid by customer name

Question

I create a custom module for product question. I added in my db the customer_id, but in the grid I want to show the name of customer. For that I did a join with customer_entity_varchar and got the full name of customer.

When I want to filter the grid by customer name:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'value' in where clause is ambiguous.

$this->addColumn('customer_name', [
            'header' => $this->__('Customer Name'),
            'align' => 'left',
            'index' => 'customer_name',
            'filter_index' => 'value',
        ]);


$first = Mage::getModel('eav/entity_attribute')->loadByCode('1', 'firstname');
        $last = Mage::getModel('eav/entity_attribute')->loadByCode('1', 'lastname');
        $this->getSelect()->join(['ce1' => 'customer_entity_varchar'], 'ce1.entity_id=main_table.customer_id', ['firstname' => 'value'])
            ->where('ce1.attribute_id='.$first->getAttributeId())
            ->join(['ce2' => 'customer_entity_varchar'], 'ce2.entity_id=main_table.customer_id', ['lastname' => 'value'])
            ->where('ce2.attribute_id='.$last->getAttributeId())
            ->columns(new Zend_Db_Expr("CONCAT(`ce1`.`value`, ' ',`ce2`.`value`) AS customer_name"));

UPDATE: I changed value with main_table.value

$this->addColumn('customer_name', [
       'header' => $this->__('Customer Name'),
       'align' => 'left',
       'index' => 'customer_name',
       'filter_index' => 'main_table.value',
]);

and receive:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.value' in 'where clause'

Answer 1

The error :

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'value' in where clause is ambiguous

occurs as value occurs in both the tables, as a solution use below: (using main_table.value)

$this->addColumn('customer_name', [
       'header' => $this->__('Customer Name'),
       'align' => 'left',
       'index' => 'customer_name',
       'filter_index' => 'main_table.value',
]);

Answer 2

I would suggest you should use renderer instead like,

$this->addColumn('customer_name', [
    'header' => $this->__('Customer Name'),
    'align' => 'left',
    'index' => 'customer_name',
    'filter_index' => 'value',
    'renderer'  => 'YOUR_RENDERER_CLASS_NAME'
]);

<?php 
class YOUR_RENDERER_CLASS_NAME
        extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{

    public function render(Varien_Object $row)
    {
        $customer = Mage::getModel('customer/customer')->load($row->getCustomerId());
        $customer->getPrefix();
        $fname = $customer->getName(); // Full Name
        $customer->getFirstname(); // First Name
        $customer->getMiddlename(); // Middle Name
        $lName = $customer->getLastname(); // Last Name
        $customer->getSuffix();
        return $fname . $lName;
    }

}