Magento按EAV选项值查询客户的技术求助

Hey there! Let's work through this issue where you need to filter customers using the displayed label values (like 05 for May, 08 for the 8th) of your custom dob_month and dob_day attributes, instead of the internal integer option IDs stored in customer_entity_int. We'll also make sure to combine those two fields into a single dob value as you requested.

The Core Problem #

Your custom select attributes store the internal option_id (e.g., 1125 for month 05) in customer_entity_int, but you want to filter and retrieve the human-readable label values. To fix this, we need to join the EAV option value tables to map those internal IDs to their labels.

Step-by-Step Solution Code #

Here's a complete, tested code snippet that addresses both filtering and field merging:

// Load the custom attributes to get their IDs
$dobMonthAttribute = Mage::getModel('eav/attribute')->loadByCode('customer', 'dob_month');
$dobDayAttribute = Mage::getModel('eav/attribute')->loadByCode('customer', 'dob_day');
$monthAttrId = $dobMonthAttribute->getId();
$dayAttrId = $dobDayAttribute->getId();

// Get the current store ID (use 0 for default store view if needed)
$currentStoreId = Mage::app()->getStore()->getId();

// Build the customer collection
$customerCollection = Mage::getResourceModel('customer/customer_collection')
    // Select base customer fields you need
    ->addAttributeToSelect(['entity_id', 'email', 'store_id'])
    // Join to get the readable month label from EAV option values
    ->joinTable(
        'eav/attribute_option_value',
        'option_id = value',
        ['dob_month_label' => 'value'],
        "attribute_id = {$monthAttrId} AND store_id = {$currentStoreId}",
        'left'
    )
    // Join to get the readable day label from EAV option values
    ->joinTable(
        'eav/attribute_option_value',
        'option_id = value',
        ['dob_day_label' => 'value'],
        "attribute_id = {$dayAttrId} AND store_id = {$currentStoreId}",
        'left'
    )
    // Combine month and day into a single 'dob' field (format: MM-DD)
    ->addExpressionAttributeToSelect(
        'dob',
        "CONCAT({{dob_month_label}}, '-', {{dob_day_label}})",
        ['dob_month_label', 'dob_day_label']
    )
    // Filter using the actual month and day labels
    ->addFieldToFilter('dob_month_label', '05')
    ->addFieldToFilter('dob_day_label', '08')
    // Test pagination
    ->setPageSize(20)
    ->setCurPage(1);

// Example: Iterate over results to verify
foreach ($customerCollection as $customer) {
    echo sprintf(
        "Customer ID: %s | Email: %s | DOB (MM-DD): %s | Store ID: %s<br>",
        $customer->getEntityId(),
        $customer->getEmail(),
        $customer->getDob(),
        $customer->getStoreId()
    );
}

Key Details to Note #

1. Joining EAV Option Tables: We use joinTable to connect customer_entity_int with eav_attribute_option_value, which maps the stored option_id to the visible label. The store_id in the join ensures we get the correct label for your active store view (use 0 if you want the default global label).
2. Filtering by Labels: Instead of addAttributeToFilter, we use addFieldToFilter on the joined dob_month_label and dob_day_label fields, so you can filter directly with values like 05 or 08.
3. Merging into a Single dob Field: The addExpressionAttributeToSelect combines the two label fields into a single dob value in MM-DD format, just as you requested.

Optional Optimization #

If you know the attribute IDs of dob_month and dob_day (you can find them in the eav_attribute table), you can skip loading the attribute models and hardcode the IDs for a small performance boost. Just replace the first few lines with:

$monthAttrId = YOUR_DOB_MONTH_ATTRIBUTE_ID;
$dayAttrId = YOUR_DOB_DAY_ATTRIBUTE_ID;

内容的提问来源于stack exchange，提问作者Troy Hammond