Filter Magento collection but not products, using distinct

Question

In my magento store I am trying to retrieve a list of youtube videos from a DB table, some videos are duplicated.

I'm looking to filter videos by using distinct video "value" but can't seam to find any information about using distinct with addFieldToFilter()

// get video collection

$collection = Mage::getModel('video/video')->getCollection();
$collection->addFieldToFilter('provider', 'youtube');

// filter by video value

$collection->addFieldToFilter('value')->distinct(true);

By removing $collection->addFieldToFilter('value')->distinct(true); it works, but retrieves all the videos.

Solution 1

You can try this:

$collection->getSelect()->distinct(true);

But this will retrieve distinct values based on id. If you want to retrieve videos using distinct video values, you should group by "value".

$collection->getSelect()->group('value');

If you want to debug the query executed :

$collection->getSelect()->__toString();

Hope this helps

Solution 2

While adding the join make sure blank array is passed in the third parameter.

$this->getSelect()->joinLeft(
    'sales_order_item',
    'sales_order_item.order_id = main_table.order_id',
    []
);

After that add the following to apply DISTINCT

$this->getSelect()->distinct(true);

Comments

[Colin009]

Fabrizio... you make me smile. You make it look so easy, that works perfectly and even i can understand what you have explained. Thank you.