0

I'm trying to pull a customers collection but i need to join it to their billing address details to add filters.

The only info i can find so far seems to be for Magento 1, such as this: Magento join customer collection with customer address

I have tried similar but joinAttribute() doesn't seem to be present in Magento 2, all i get is join() which expects me to give it the table name, so for example i have this:

/** @var AbstractCollection $customers */
        $customers = $this->customerCollection->getCollection()
            ->setCurPage($page)
            ->setPageSize($pageSize)
            ->setOrder($orderBy,$order);

I'm not sure how to now join this to their billing address since it's across multiple tables, do i need to join all the relevant tables or is there another way in magento to do it?

I have tried the following but i can an error saying "The "billing_telephone" attribute name is invalid. Reset the name and try again."

$customers->getSelect()->joinLeft(
            ['address'=>'customer_address_entity'],
            'e.default_billing = address.entity_id'
        );

        $customers->addFilterToMap('billing_telephone','address.telephone');

        $customers->addFieldToFilter('billing_telephone',[
            'like'=>'%077%'
        ]);

UPDATE

I have tried to change things a little but it's not working, so i am injecting "use Magento\Customer\Model\ResourceModel\Customer\Collection" for the collection

I then start my query:

$customers = $this->customerCollection->setCurPage($page)
            ->setPageSize($pageSize)
            ->setOrder($orderBy,$order);

I then add the join:

$customers->getSelect()->joinLeft(
            ['billing_address'=>'customer_address_entity'],
            'e.default_billing = billing_address.entity_id',
            ['*']
        );

Now this all works, i then add the field to the filters:

$customers->addFieldToFilter('billing_address.telephone',[
            'eq'=>'00000'
        ]);

This is where the problem happens, it says: "The "billing_address.telephone" attribute name is invalid. Reset the name and try again."

1 Answer 1

0

Try below code and check.

$customerCollection->getSelect()->joinLeft(
    ['billing_address' => $customerCollection->getTable('customer_address_entity')],
    'billing_address.parent_id = main_table.entity_id',
    ['billing_address_id' => 'billing_address.entity_id', 'billing_firstname' => 'billing_address.firstname', 'billing_lastname' => 'billing_address.lastname', 'billing_street' => 'billing_address.street', 'billing_city' => 'billing_address.city', 'billing_region' => 'billing_address.region', 'billing_postcode' => 'billing_address.postcode', 'billing_telephone' => 'billing_address.telephone', 'billing_country_id' => 'billing_address.country_id']
);

foreach ($customerCollection as $customer) {
    echo $customer->getFirstname() . ' ' . $customer->getLastname() . '<br>';
    echo $customer->getBillingFirstname() . ' ' . $customer->getBillingLastname() . '<br>';
    echo $customer->getBillingStreet() . '<br>';
    echo $customer->getBillingCity() . ' ' . $customer->getBillingRegion() . ' ' . $customer->getBillingPostcode() . '<br>';
    echo $customer->getBillingTelephone() . '<br>';
    echo $customer->getBillingCountryId() . '<br><br>';
}

This code will join the customer collection with the customer_address_entity table on the parent_id column, and retrieve the billing address details for each customer. You can then access the billing address details using the getBilling*() methods on the customer object.

UPDATE

$customerCollection = $objectManager->create('Magento\Customer\Model\ResourceModel\Customer\Collection');
$billingAddressTable = $customerCollection->getTable('customer_address_entity');

$customerCollection->getSelect()->joinLeft(
    ['billing_address' => $billingAddressTable],
    'main_table.default_billing = billing_address.entity_id',
    ['*']
);

Then, to filter the collection by a specific code, you can use the following code:

$customerCollection->addFieldToFilter('billing_address.postcode', array('eq' => $code));

Let me know if any query.

THANKS.

7
  • Thanks but the issue isn’t selecting the fields, it’s filtering the query by one of the joined fields as per the update to my question
    – noname
    Jan 19, 2023 at 17:09
  • You means you getting issue filtering not working after join both table right? Jan 19, 2023 at 17:14
  • Yeh look at the last bit of my post I try to add the field in the join to the filter map but gives me a weird error. I have bolded the error message in my post for you
    – noname
    Jan 19, 2023 at 17:25
  • Okay, check my updated answer. Jan 19, 2023 at 17:28
  • Apologies for the delay, let me try some things with what you've said in mind, i will then report back
    – noname
    Jan 20, 2023 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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