Select a field without `e.` or `main_table` - Magento

Select a field without `e.` or `main_table` - Magento

September 1, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Select a field without `e.` or `main_table` - Magento

Retrieve Custom Field Values in Magento: A Step-by-Step Guide

In this post, we’ll dive into retrieving values from custom fields you’ve added to your Magento module. We’ll focus on the challenge of selecting data from a specific table without using main_table or e. prefixes.

Understanding the Problem

You’ve already created a custom module and overridden the Review model’s core files to include a new field called ‘email’ in the review_detail table. You can save data to this field successfully, but when trying to fetch it, Magento incorrectly attempts to select the email from the main_table (review) instead of the intended target table (review_detail).

Analyzing the Current Query

Let’s break down your current query:

Mage::getModel('review/review')
    ->getCollection()
    ->addFieldToFilter('status_id' , '1')
    ->addFieldToFilter('email', $emailId)
    ->addFieldToSelect('email');

And the generated SQL:

SELECT `main_table`.`email` AS `review_detail`, `detail`.`detail_id`, `detail`.`title`, `detail`.`detail`, `detail`.`nickname`, `detail`.`customer_id`
FROM `review` AS `main_table`
INNER JOIN `review_detail` AS `detail` ON main_table.review_id = detail.review_id
WHERE (status_id = '1') AND (email = 'test@test.com')

The issue lies in the generated SQL query. It’s incorrectly using main_table to select your custom field email.

The Key to Selecting Custom Fields

To solve this, you need to understand how Magento creates the collection and joins tables. By default, it selects fields from the main table (in this case, review) with an alias of main_table. However, when you join another table like review_detail, it doesn’t automatically include your custom field in the select clause.

The Solution: Manually Selecting Your Custom Field

To retrieve the value from your custom field ‘email’ in the review_detail table, you need to manually specify which fields to select. Here’s how:

  1. First, create a new collection using your custom model:
$collection = Mage::getModel('your_module/your_model')->getCollection();
  1. Add filters as needed (in this case, status_id):
$collection->addFieldToFilter('status_id', '1');
  1. Manually add the field to select from your custom table:
$collection->getSelect()->join(
    array('detail' => $collection->getTable('review/review_detail')),
    'main_table.review_id = detail.review_id',
    array('email')
);
  1. Finally, add the filter for your email field:
$collection->addFieldToFilter('detail.email', $emailId);

The Final Query

Combining all these steps gives you the modified query:

$collection = Mage::getModel('your_module/your_model')->getCollection();
$collection->addFieldToFilter('status_id', '1');

$collection->getSelect()->join(
    array('detail' => $collection->getTable('review/review_detail')),
    'main_table.review_id = detail.review_id',
    array('email')
);

$collection->addFieldToFilter('detail.email', $emailId);

Running this query will correctly fetch the data from your review_detail table, including the custom field ‘email’.

Conclusion

In Magento, when dealing with custom fields in joined tables, you need to manually specify which fields to select. By creating a new collection and joining the necessary tables while explicitly selecting your custom field, you can retrieve the desired data accurately.

Remember to replace 'your_module' and 'your_model' with your actual module name and model class respectively.

This approach ensures that Magento selects from the correct table (review_detail) without using main_table or e. prefixes. It’s a clean way to handle custom field retrieval in your Magento modules.

Don’t forget to check out Laravel Company for more tutorials and tips on Magento development!

Database Extensions Email Customers

Our network