how to get following result by raw sql query?

Question

I need to get result following way and need to store in same way.and I don’t want loop the product collection.Anyone have idea on this to get result with single query?

sku,name,price,created_at,descrption,qty

1 xxx 30 somedate,this is desc,10

2 yyy 40 somedate,this is desc,25

3 zzz 30 somedate,this is desc,10

Answer 1

You can get the SQL from a collection if need be.

Because products use EAV it's fairly complicated to write a sql query, but very easy to build a collection.

I'm guessing what you need it this

$attributes = ['sku','name','price', 'descrption' , 'qty' ];
$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect($attributes);

Now you can get the select by doing the following:

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

Which is the following:

SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`sku`, `e`.`name`, `e`.`price` FROM `catalog_product_flat_1` AS `e`%

You'll see Magento adds a few extra columns in, please just remove them.