联表查询数据库的方法: joinLeft中第二个array()里面写入的是联表中要加入的字段,如果使用getSelect()方法那么排序使用order,并且排序方法和排序条件写在一起中间加个空格。如果不使用getSelect()的话排序用setOrder()。
1、使用getSelect()方法:
$collection->getSelect()
->joinLeft(['rules' =>'salesrule'], "main_table.rule_id = rules.rule_id", ['discount_amount'])
->where("main_table.times_used = '0'")
->order('main_table.coupon_id DESC');
多表联查不只是两张表进行联表,也可以多张表进行联查(>2):
$store = Mage::app()->getStore()->getId();
$_article = Mage::getModel('faq/faq')->getCollection();
$_article->getSelect()
->joinleft(['a' => 'faq_category_item'], 'main_table.faq_id = a.faq_id', ['category_id'])
->joinleft(['b' => 'faq_category'], 'a.category_id = b.category_id', ['category_name'])
->joinleft(['c' => 'faq_store'], 'main_table.faq_id = c.faq_id', [])
->where("c.store_id = $store");
2、不使用getSelect()方法:
$orders = Mage::getResourceModel('sales/order_collection')
->addFieldToSelect('*')
->addFieldToFilter('customer_id', 33)
->setOrder('created_at', 'desc');