Join a Flat Table to an EAV Table with Magento?

Join a Flat Table to an EAV Table with Magento?

August 27, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Join a Flat Table to an EAV Table with Magento?

Joining a Flat Table to an EAV Table in Magento: A Comprehensive Guide

As a senior developer specializing in e-commerce platforms like Magento, I often encounter complex queries involving Entity-Attribute-VALUE (EAV) tables and flat tables. In this blog post, let’s explore how to join your custom “facility” table with the “customer_entity” table while retrieving only the customer name.

Understanding Your Current Setup

You’ve already made a great start by joining the tables using Magento’s collection methods:

$collection = Mage::getModel('facility/facility')
    ->getCollection()
    ->join('customer/entity', 'customer_id = entity_id');

This query successfully joins your “facility” table to the “customer_entity” table on the customer_id and entity_id fields. However, you’re noticing that some methods aren’t available due to the flat structure of the collection.

Retrieving Customer Name from the EAV Table

To retrieve only the customer name, we need to add a second join for the “customer_entity_varchar” table, which stores static attributes like first and last names:

$collection = Mage::getModel('facility/facility')
    ->getCollection()
    ->join('customer/entity', 'customer_id = entity_id',
        array('name' => 'CONCAT(firstname, " ", lastname)'))
    ->joinLeft('customer/entity_varchar', 'entity_id = entity_id AND attribute_id = 3'); // Assuming first name is ID 2

Here’s what happening in the revised code:

  • We’re joining the “customer_entity_varchar” table on entity_id, using the left join operator (joinLeft) to ensure we don’t lose rows with missing data.
  • The attribute_id = 3 condition selects the first name attribute (assuming it has ID 2).
  • The CONCAT(firstname, " ", lastname) function combines the first and last names into a single “name” field.

Optimizing Your Query for Performance

While this query works, remember that EAV tables can be slow due to their denormalized structure. To optimize performance:

  1. Limit the number of rows: If possible, apply filters like setPageSize() or use Magento’s built-in pagination methods before joining the tables.

  2. Index your fields: Ensure that the joined columns (customer_id, entity_id) are indexed for faster lookups.

  3. Use denormalized data when available: If you have a separate table with pre-rendered customer names, join it instead to avoid complex queries on the EAV tables.

Conclusion

Joining flat and EAV tables in Magento requires careful planning to ensure optimal performance while retrieving the required data. In your case, adding an additional join for the static attribute table allowed you to retrieve only the needed “name” field.

Remember that managing EAV data can be complex but powerful. By understanding how Magento stores this information and using appropriate joins, you can create sophisticated queries that meet your business needs.

For more advanced techniques in Magento development, I recommend exploring our comprehensive guide on “Magento 2 Customization Techniques” available at Laravel Company. It covers best practices and deep dives into the platform’s core components.

Happy coding!

Database Customers

Our network