How to join tables and retrive data

Question

I want to join entity_id column from customer_entity table with customer_id from new_info table and want to get department column value of given customer.

Answer 1

Try below code to get the department column value from your custom table on basis of customer entity_id field.

protected $_customerFactory;

public function __construct(
    ...
    \Magento\Customer\Model\CustomerFactory $customerFactory,
    ...
) {
   $this->_customerFactory = $customerFactory;
}

public function yourfunctionname(){

   $customersCollection = $this->_customerFactory->create()->getCollection();
        $customersCollection->getSelect()
                            ->joinLeft(['ni' => 'new_info'], 'ni.customer_id = e.entity_id', ['department' => 'ni.department']);

   return $customersCollection;
}

In above code:

ni = your custom table alias name and, new_info = your custom table name

Hope this solution will help you!