2

I have the following query that I am trying to convert in Magento Model format, but the result is not the same after conversion and getting same errors.

Query is:

SELECT 
  ce_varchar.value AS CODE,
  sfoi.order_id,
  sfo.increment_id,
  COUNT(sfoi.`order_id`) AS totalOrders,
  ea.attribute_id,
  ea.attribute_code,
  pv.value,
  sfo.created_at 
FROM
  mage_catalog_product_entity AS ce
  INNER JOIN mage_eav_attribute AS ea
    ON ce.entity_type_id = ea.entity_type_id
  INNER JOIN mage_catalog_product_entity_varchar AS ce_varchar
    ON ce.entity_id = ce_varchar.entity_id 
    AND ea.attribute_id = ce_varchar.attribute_id 
    AND ea.backend_type = 'varchar' 
  INNER JOIN mage_sales_flat_order_item AS sfoi 
    ON sfoi.product_id = ce.entity_id 
  INNER JOIN mage_sales_flat_order AS sfo 
    ON sfo.entity_id = sfoi.order_id 
  INNER JOIN mage_catalog_product_entity_varchar pv 
    ON pv.entity_id = ce.entity_id 
WHERE ea.attribute_id = 1062 
  AND pv.attribute_id = 96 
  AND LOWER(sfo.lead_status) IN ('converted', 'unconverted') 
  AND ce_varchar.value IS NOT NULL
;

Here is my Failed attempt of Magento's Model Formatted query

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->getSelect()
    ->join(array('ea'=>'eav/attribute'), 
        'ea.entity_type_id = ea.entity_type_id',array(''))
    ->join(array('ce_varchar'=>'catalog/product_entity_varchar'), 
        'main_table.entity_id = ce_varchar.entity_id')
    ->join(array('sfoi'=>'sales/flat_order_item'),
        'sfoi.product_id = ce.entity_id',array(''))
    ->join(array('sfo'=>'sales/flat_order'),
        'sfo.entity_id = sfoi.order_id ',array(''))
    ->join(array('pv'=>'catalog/product_entity_varchar'),
        'pv.entity_id = main_table.entity_id',array(''))
    ->addFieldToSelect('entity_id')
    ->addFieldToSelect('cut_sample_location')
    ->addExpressionFieldToSelect('tatalOrders','COUNT(sfoi.`order_id`)','sfoi.order_id')
    ->addFieldToFilter('ea.attribute_id',1062)
    ->addFieldToFilter('pv.attribute_id',96)
    ->addFieldToFilter('sfo.lead_status', array('in' => array('converted', 'unconverted')))
    ->addFieldToFilter('ce_varchar.value' != null) 
    ->group('main_table.entity_id , ce_varchar.value')
    ->order('DESC')
    ->limit(30);
  • What error are you getting? – Sander Mangel Sep 14 '15 at 19:52
  • Call to a member function getBackend() on a non-object in – Asim Mukhtar Sep 16 '15 at 5:30
1

You have a syntax error in your select:

->addFieldToFilter('ce_varchar.value' != null) should read

->addFieldToFilter('ce_varchar.value', array('neq' => 'null'));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.