Convert mysql query into magento model format

Question

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);

Answer 1

You have a syntax error in your select:

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

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