1

I want to join two tables, the customer_entity, and customer_entity_varchar. but I have no idea how to do it. This is my current query:

Let's assume I the entity_id is 5 and attribute_id is 139.

$customerCollection = Mage::getModel('customer/customer')->getCollection()->addAttributeToSelect('*')->getSelect()->join( array('varchar'=> 'customer_entity_varchar'), 'varchar.entity_id = main_table.entity_id', array('varchar.attribute_id'))->addAttributeToFilter('entity_id', array('eq' => '5'))->addAttributeToFilter('attribute_id',array('eq' => '139'));

Below is the query I want to achieve.

SELECT attribute_id, entity_id FROM customer_entity INNER JOIN customer_entity_varchar ON customer_entity.entity_id = customer_entity_varchar.entity_id WHERE customer_entity.entity_id = 5 AND customer_entity_varchar.attribute_id = 139

I am stuck with this. I am using Magento 1.9.

1

use this way:

$customer=Mage::getModel('customer/customer')->getCollection();
$customer->getSelect()->join(array('cev' => 'customer_entity_varchar'), 'cev.entity_id = e.entity_id')
->where("e.entity_id =5 AND cev.attirbute_id=139");

As I think you cannot use addAttributeToFiltee here

  • Is there any way to add the AND clause to the JOIN clause so that i filter when the JOIN occurs vs after when the join occurs? this makes a big performance difference on larger tables. – domdambrogia Oct 9 at 15:29
0

Try to execute the same and debug. you will get to know.

    $customerCollection = Mage::getModel('customer/customer')->getCollection();
    $customerCollection->addAttributeToSelect('*')
    ->getSelect()->join( array('cev'=> 'customer_entity_varchar'), 'cev.entity_id = main_table.entity_id', array('cev.attribute_id'))
    ->addAttributeToFilter('entity_id', array('eq' => '5'))
    ->addAttributeToFilter('attribute_id', array('eq' => '139'));

   echo $customerCollection->getSelect(); exit();
0

You need to define table alias in addAttributeToFilter for your query, like, main_table.entity_id and varchar.attribute_id

 $customerCollection = Mage::getModel('customer/customer')->getCollection()
            ->addAttributeToSelect('*')
            ->getSelect()->join( array('varchar'=> 'customer_entity_varchar'), 'varchar.entity_id = main_table.entity_id', array('varchar.attribute_id'))
            ->addAttributeToFilter('main_table.entity_id', array('eq' => '5'))
            ->addAttributeToFilter('varchar.attribute_id', array('eq' => '139'));
    echo $customerCollection->getSelect();
  • I got error in main_table.entity_id. – larp Jun 5 '18 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.