Posted by: james | August 1, 2009

How to write query in magento and zend framewrok?

For Ex. We have query like this,

SELECT  sf.product_id,sf.name,count(sf.name) as total,s.customer_id,c.value FROM sales_flat_order_item sf

LEFT JOIN sales_order s ON sf.order_id = s.entity_id LEFT JOIN

customer_entity_varchar c ON s.customer_id = c.entity_id

WHERE c.attribute_id = 1 AND s.created_at between ‘2009-07-04 11:22:15’ and ‘2009-07-31 10:10:36’  GROUP BY sf.name,customer_id ORDER BY s.customer_id

then it will written in mysql as under.

$read = $this->_getReadAdapter();

$select = $read->select()

->from(array(‘sf’ => $this->getTable(‘orderreport/flat_order_item’)), array(‘sf.product_id’, ‘sf.name’, ‘total’=>’count(sf.name)’))

->joinLeft(array(‘s’ => $this->getTable(‘orderreport/order’)),’sf.order_id = s.entity_id’, array(‘s.customer_id’))

->joinLeft(array(‘c’ => $this->getTable(‘orderreport/entity_varchar’)), ‘s.customer_id = c.entity_id’, array(‘c.value’))

->where(‘c.attribute_id = 5’)

->where(‘s.created_at > ?’, $from)

->where(‘s.created_at < ?’, $to)

->group(array(‘sf.name’, ‘customer_id’));

$result = $read->fetchAll($select);

And if you Have query like this..

SELECT count(s.product_id) as total_order,s.name FROM sales_flat_order_item s GROUP BY s.product_id

then it will be come in this format.

$select = $read->select()

->from(array(‘s’ => $this->getTable(‘orderreport/flat_order_item’)), array(‘total_order’=>’count(s.product_id)’, ‘s.name’))

->where(‘s.created_at > ?’, $from)

->where(‘s.created_at < ?’, $to)

->group(‘s.product_id’);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: