0

I need to sent a newsletter for specific customer group, so I need to collect emails from a specific customer group.

I searched about tables:

  1. customer group:

    +-------------------+---------------------+--------------+
    | customer_group_id | customer_group_code | tax_class_id |
    +-------------------+---------------------+--------------+
    
  2. Newsletter_subscriber:

    +---------------+----------+------------------+-------------+----------------- ---+-------------------+----------------------------------+
    | subscriber_id | store_id | change_status_at | customer_id | subscriber_email   | subscriber_status | subscriber_confirm_code          |
    +---------------+----------+------------------+-------------+--------------------+-------------------+----------------------------------+
    
  3. Customer_entity:

     | entity_id | website_id | email| group_id | increment_id| store_id | created_at| updated_at | is_active | disable_auto_group_change | created_in  | prefix | firstname | middlename | lastname | suffix | dob | password_hash  | rp_token | rp_token_created_at | default_billing | default_shipping | taxvat | confirmation | gender |
    

The Customer Entity table has all the information Customer Group id and Cutomer EMail

so how can I get emails from specified customer group using customer_entity table? Or any other easy way to collect emails form a specific customer group?

2 Answers 2

1

Let's say your customer group id is 3.
You can run this query:

SELECT 
    email 
FROM  
    customer_entity
WHERE 
    group_id = 3

Replace 3 with your real customer group id.
Export the rows you get and use them in your newsletter tool.
If you need additional info, not just the emails you can replace email with email, firstname, lastname.

[EDIT]
If you want to retrieve the emails via code you can do this.
Let's say you have a class that should retrieve the emails and sent the newsletter.
You can do this:

namespace My\Namespace;
class MyClass extends Some\Other\Class
{
     protected $customerRepository;
     protected $searchCriteriaBuilder;
     protected $filterBuilder
     public function __construct(
        ...//your other dependencies here
        \Magento\Customer\Api\CustomerRepositoryInterface $customerRepository,
        \Magento\Framework\Api\SearchCriteriaBuilder $searchCriteriaBuilder,
        \Magento\Framework\Api\FilterBuilder $filterBuilder,
        ...//your other dependencies here
    ) {
        ....
        $this->customerRepository = $customerRepository;
        $this->searchCriteriaBuilder = $searchCriteriaBuilder;
        $this->filterBuilder = $filterBuilder;
        ....
    }

    public function getCustomersbyGroup($groupId) 
    {
         $filter = $this->filterBuilder
                        ->setField('group_id')
                        ->setConditionType('eq')
                        ->setValue($groupId)
                        ->create();
         $filters = [$filter];
         $this->searchCriteriaBuilder->addFilters($filters);
         $searchCriteria = $this->searchCriteriaBuilder->create();
         $searchResults = $this->customerRepository->getList($searchCriteria);
         //then you can collect emails just by looping through the search results
         $emails = [];
         foreach ($searchResults->getItems() as $customer) {
              $emails = $customer->getEmail();
         }
         return $emails; //or you can simply return $searchResults if you need the customers as objects.  
    }
}
2
  • Is way Best practice to run query in code? Oct 27, 2016 at 5:29
  • @Rajkumar.E. See my edit.
    – Marius
    Oct 27, 2016 at 5:41
1

Yes you can extract some data from Magento MySQL DB, but before executing a queries below notice following things:

  • You must not fetch data from MySQL, but you must use Magento Customer module (models, resource models).

  • Magento EAV structure is way to complex to get data from MySQL.

  • You cannot extract ALL customer data just by one query or even multiple queries. Because many informational objects are created within models and only model has the logic to gather all data within one customer object. I talk about shipping/billing addresses, store credit, reward points (for EE version) and so on.

However to get customer email for a specific customer group you can use query

SELECT `e`.`email` FROM `customer_entity` AS `e` WHERE (`e`.`group_id` = '1')

To get from collection factory inject collection factory and use

$customerCollection = $this->_customerFactory->create()->getCollection(); //Cusotmer Collection
$customerCollection->addFieldToFilter('group_id', 1); // Filter Your Group
$emails = $customerCollection->getColumnValues('email'); // Get Emails (Array)
var_dump($emails);die;

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.