Magento 2 - Add Custom Attribute to Product Collection without Flat Catalog EAV?

Question

I have the below collection:

$this->_collection->getSelect()
                  ->order(new \Zend_Db_Expr('price_index.min_price / IF(e.unit_of_sale = NULL, 1, e.unit_of_sale) DESC'));

This is used for sorting by each price on the frontend and worked perfectly while product flat catalog was enabled. Now however with product flat catalog disabled the following error is shown:

Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.unit_of_sale' in 'order clause'

I'm guessing i need to add the attribute so it can be used for the ORDER clause. I have tried the below however with no luck:

1. ->addAttributeToSelect('unit_of_sale')
2. ->addFieldToSelect('unit_of_sale')

Using ->addAttributeToFilter('unit_of_sale') gets rid of the error and so have just created a false filter ->addAttributeToFilter('unit_of_sale', array('neq' => '-1')) so that I can use the attribute for the order but would rather know how to add the attribute for order clause.

How can i add a custom attribute to a collection while flat catalog is disabled?

Answer 1

Adding the catalog_product_entity_varchar table as a second paramater to the addAttributeToSelect method call seemed to fix my issue:

->addAttributeToSelect('unit_of_sale', 'catalog_product_entity_varchar')

Answer 2

            $collection = $productCollection->create()
                    ->addAttributeToFilter('is_featured', array('eq' => 1))
                    ->load();

Answer 3

Second parameter is join type, you can use 'left' to get extra field into the collection. eg. ->addAttributeToSelect(['price','special_price'],'left');