Getting an EAV option's value (eav_attribute_option_value) instead of ID when fetching products using GraphQL

Question

I should preface this by saying I know nothing about Magento 2 development, and barely anything about GraphQL, yet I've been added to this project so I have to learn both of them at the same time. I've spent a considerable amount of time trying to find a solution to my problem, but the only one I've been able to get working is inefficient.

We've got a GraphQL query to fetch a list of all products from a specific store. Part of the query is fetching the manufacturer, which is a custom EAV dropdown product attribute. The query I'm running is the following;

    ProductsForWebsites {
        items {
            total_items
            items {
                entity_id
                manufacturer
                name
                price
                sku
            }
            website { ... }
        }
    }
}

and in the resolver I've added 'manufacturer' to the addAttributeToSelect method;

$productCollection = $this->productCollectionFactory->create();
$productCollection->addAttributeToSelect([
    'sku',
    'name',
    'price',
    'manufacturer',
]);

This works, but instead of returning the option label (so the manufacturer name), it returns the option ID.

One solution I've found is to fetch the attributes for each item, find the one you need (manufacturer in my case) and call ->getFrontend()->getValue($item) to get the attributes value;

foreach ($items as $item) {
    $attributes = $item->getAttributes();
    $manufacturer = $attributes['manufacturer']->getFrontend()->getValue($item);

    ...
}

This once again works, but turns a ~4 second request into a timed out request because (I presume) it's querying the database for every individual getValue call.

Is there a more efficient way of including the manufacturer's name? I've tried a couple different ways of joining tables (joinAttributes(...), getSelect()->join(...)) but none of them work.

I understand the database structure; the attribute itself is stored in eav_attribute, its options in eav_attribute_option and the value for each option in eav_attribute_option_value, but I can't figure out how to join this together in one efficient query, instead of N inefficient queries, in Magento 2/GraphQL.

Answer 1

As Product Collection is retrieved from Product Entity EAV Tables, a simple join to eav_attribute_option_value table with the Collection SQL is not possible. Though you either get rid of collections and use the RAW SQL query using Magento Framework DB Classes is a possible approach, I just added below a better approach, which is better one.

In the resolver class, just get your attribute options with labels beforehand in an array and get the option label from the attributeOptions array using the option id that you have already from the product collection.

use Magento\Store\Model\StoreManagerInterface;
use Magento\Eav\Model\Config;

/* @var \Magento\Eav\Model\Config */
protected $eavConfig;

/* @var \Magento\Store\Model\StoreManager  */
protected $storeManager;

// Inject the above classes in your GraphQL Resolver contructor class


// add a method below in your GraphQL Resolver class 

    /*  
     * returns an EAV Attribute Options with labels and values
     * 
     * @param string $attributeCode
     * 
     * @return array Options
     * 
     */
    
    public function getAttributeOptions(string $attributeCode):array
    {
        $storeId   = $this->storeManager->getStore()->getId();
        $attribute = $this->eavConfig->getAttribute(\Magento\Catalog\Model\Product::ENTITY, $attributeCode);
        $attributeValues    = $attribute->setStoreId($storeId)->getSource()->getAllOptions();
        $attributeOptions = [];
        foreach($attributeValues as $key => $option ):
            if($option['label']!=""){
                $attributeOptions[$option['value']] = $option['label'];
            }
        endforeach;
        return $attributeOptions;
    }

And add this code in the resolver method

$manufacturerAttributeOptions = $this->getAttributeOptions('manufacturer'); // here manufacturer is your attribute code
foreach ($items as $item) {
    /* @var \Magento\Catalog\Model\Product  */
    $manufacturerOption = $item->getManufacturer(); // returns manufacturer option id
    $manufacturerLabel = $manufacturerAttributeOptions[$manufacturerOption] ?? ''; // gets the label from the array
}

This avoids the EAV join which is expensive and the much costlier call to getValue in an interation.