Select only specific fields in Magento

Question

I'm trying to use the MAX function of MySQL to retrieve the latest dates from my table.

$_updates = Mage::getModel('ticket/updates')->getCollection();  
$_updates->getSelect()->columns('MAX(created) as max_created')->group(array('status_id'));

This is the resulting query:

SELECT `main_table`.*, MAX(created) AS `max_created` FROM `em_ticket_updates` AS `main_table` GROUP BY `status_id`

The problem with this is that if all the fields are included (main_table.*) it does not function correctly.

Is there a way to remove main_table.* from the query and only use specific fields?

Thanks.

clockworkgeek · Accepted Answer · 2020-03-19T09:59:56.237

48

A Zend trick can be used here.

$_updates->getSelect()
    ->reset(Zend_Db_Select::COLUMNS)
    ->columns('MAX(created) as max_created')
    ->group(array('status_id'));

NOTE:

For EAV collection you must re-add the entity_id or you will have an error when the collection is loaded.

    $collection = Mage::getModel('catalog/product')
        ->getCollection();
    $collection->getSelect()
        ->reset(Zend_Db_Select::COLUMNS)
        ->columns(array('entity_id')); 

    $collection
        ->addAttributeToSelect(array('image','small_image','thumbnail'))
        ->addFieldToFilter('entity_id', array('in' => $simple_ids));

edited Mar 19 '20 at 09:59

answered Dec 08 '11 at 15:24

clockworkgeek

37,650
9
89
127

Thank you! Saw the reset method on the Magento documentation but not the parameter value. Guess I should really study Zend too. – Jjj Dec 09 '11 at 01:16
Exactly what I was looking for since long. Absolutely awesome answer! Thanks for saving my day! – Prashant Oct 12 '12 at 14:59
Saved me from searching for too long, i ran into this issue when trying to set the columns in the "from" while adding "join" later and was scratching my head why it's including other columns from the joined tables. This reset trick solved it! – Long M K Nguyễn Mar 17 '20 at 18:47

score 7 · Answer 2 · answered May 19 '14 at 06:13

7

Magento provide addFieldToSelect() fumctionality. Use below code to get specific field.

$Collection = Mage::getModel('showdown/votes')->getCollection();
$Collection->addFieldToSelect('id');

answered May 19 '14 at 06:13

Neeraj Garg

695
3
17
38

3

you can select more than one field with array, e.g. `$Collection->addFieldToSelect(array('field_1', 'field_2');` – OSdave Oct 30 '15 at 09:42
Thanks for this nice and easy solution ! Works great in Magento 2.1 ! – Anthony BONNIER Dec 10 '18 at 10:28

score 5 · Answer 3 · answered Feb 06 '14 at 10:25

I recognise this is an old post but I thought I'd post an alternative solution.

I had a similar problem myself, I eventually searched through the source until I reached Zend_Db_Select After consulting the Zend Documentation (Example 8).

$select = $db->select()
    ->from(array('p' => 'products'),
           array('product_id', 'product_name'));

score 0 · Answer 4 · answered Apr 08 '21 at 05:20

0

Just need to use "getColumnValues"

$_updates = Mage::getModel('ticket/updates')->getCollection(); 
$columnValues = $_updates->getColumnValues('Your Column Name');

answered Apr 08 '21 at 05:20

Rakesh

756
8
10

Select only specific fields in Magento

4 Answers4