0

I've below MySql query, I would like to change use it in Magento format.

How can i do that ?

SELECT customer_entity_varchar.value, customer_entity_text.value from customer_entity INNER JOIN customer_entity_varchar ON customer_entity.entity_id=customer_entity_varchar.entity_id INNER JOIN customer_entity_text ON customer_entity.entity_id = customer_entity_text.entity_id

I've tried below but it only returns me. One table values not other ones.

 $connection = $this->_resource->getConnection();
    $select = $connection->select()
        ->from(
            ['customer_entity']
        )
        ->join('customer_entity_varchar', 'customer_entity.entity_id = customer_entity_varchar.entity_id')
        ->join('customer_entity_text', 'customer_entity.entity_id = customer_entity_text.entity_id');
    $selectnew = $connection->fetchAssoc($select);
    echo "<pre>";
    print_r($selectnew); exit;

Any help would be really appreciated.

12
  • Check this, framework.zend.com/manual/1.10/en/zend.db.select.html if you'll still have problem - update question with code you have and I'll help May 26, 2018 at 18:33
  • You also can debug query using $select->__toString() it will return select statement May 26, 2018 at 18:36
  • @StepanFurman, Above is my code. Pure SQL is working awsome, I need to convert it into Magento's format. Check my code and let me know your thoughts.
    – anonymous
    May 28, 2018 at 4:41
  • did you read the article by my link above? What did you try after that? May 28, 2018 at 5:03
  • Check below: $items->getSelect()->join(array('l' => 'customer_entity_varchar'), 'l.entity_id = l.entity_id') ->join(array('m' => 'customer_entity_text'), 'm.entity_id = l.entity_id')
    – anonymous
    May 28, 2018 at 5:54

1 Answer 1

3

Try this:

$items->getSelect()
->join(
    array('l' => 'customer_entity_varchar'), 
    'l.entity_id = l.entity_id', 
     array('profile' => 'l.value')
 )
->join(
    array('m' => 'customer_entity_text'),         
    'm.entity_id = l.entity_id', 
    array('status' => 'm.value')
 );
5
  • It will not work, Due to Zend_Db_Select will thorw undefined in Magento.
    – anonymous
    May 28, 2018 at 7:42
  • @Vivek I updated the answer above. May 28, 2018 at 7:44
  • IF i use reset it will reset all collums including main table also, I only need to reset collumn of joined tables. How ?
    – anonymous
    May 28, 2018 at 7:46
  • @Vivek you can't reset particular table, see updated answer May 28, 2018 at 7:57
  • @Stephan, Oh i See, No Worries. Still it works. Also now need to look for Zend too. :)
    – anonymous
    May 28, 2018 at 7:59

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.