magento sql 常用用法

  $resource = Mage::getSingleton('core/resource');  
  $tableName_votes = $resource->getTableName('jselfless/votes');  
  $tableName_image = $resource->getTableName('jselfless/image');  
  $collection = Mage::getModel('jselfless/woman')->getCollection();  
  $collection->getSelect()->reset(Zend_Db_Select::COLUMNS);  
  $collection->getSelect()->columns(array('items_count'=>'count(main_table.id)'));  
  $collection->addFieldToFilter('main_table.year',date('Y'));  
  $collection->addFieldToFilter('image.type','main');  
  $collection->addFieldToFilter('award',array('eq'=>''));  
  $collection->getSelect()->join(array('votes' => $tableName_votes), 'main_table.id = votes.selfless_woman_id', '');  
  $collection->getSelect()->join(array('image' => $tableName_image), 'main_table.id = image.selfless_woman_id', array('image.*'));  
  $collection->getSelect()->group('main_table.id');  
  $collection->getSelect()->Order('items_count desc');  
  $collection->getSelect()->Order('votes.created_at desc');  
  $collection->getSelect()->Order('main_table.updated_at desc');  
  $collection->getSelect()->limit(10,$postData['offset']);  

sql get all child cat by parent id

改少少就可以拎哂所有child
平時冇咩用, 但searching 好有用 =]

SELECT t1.* FROM category AS t1

LEFT JOIN category AS t2 ON t2.category_id= t1.parent

LEFT JOIN category AS t3 ON t3.category_id =t2.parent

WHERE 

(t1.category_id = [child_id] OR t2.category_id = [child_id] OR t3.category_id = [child_id])

 ORDER BY t1.LEVEL

sql ifnull

如果sql 要用concat 但又有null, 咁 ifnull 就好好用了
因為concat null 會成個value 變null 的

SELECT CONCAT(IFNULL(FIRSTNAME, ''), ' ', IFNULL(LASTNAME, '') AS FULLNAME FROM USER;

sql group_concat group by

今日先知原來有group_concat

會自動將那個column 的value 用 ',' group

完全救左我~!!

SELECT Class, GROUP_CONCAT(Animal) AS Animal FROM Zoo GROUP BY Family;

table

Class   Animal

===========

Fish   Goldfish

Fish   Grouper

Mammal Tiger

Mammal Lion

Result:

Fish   Goldfish,Grouper

Mammal  Tiger,Lion

"Update" SQL joining more than 1 tables

平時用CODE做, UPDATE SQL 都係好簡單, 但如果想做複雜少少既UPDATE, 其實亦可以COMBINE 埋 SELECT 一齊UPDATE , 但個SYNTAX有少少唔同

假設我select 左一堆node type係 "recipe" ,同profile_values 係"member"既row出黎

select * from node ,users ,profile_values 

where node.type='recipe' 

and users.uid = node.uid 

and profile_values.nid = node.nid

and profile_values.value='member'

之後我想將result 既某一個column (e.g. language )一次過update做新value

update node ,users ,profile_values 
set node.language ='sc_kit_cn' 
where node.type='recipe' and users.uid = node.uid 
and profile_values.nid = node.nid
and profile_values.value='member'

簡單講, 
就係將from 既table list 放去update後面
set 既column一定要簡番要update既table
where 果堆照keep番

sql get parent cat by child id

原來呢個網有教

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

我再改一下

#get the path by child id, only one row,

SELECT t1.name, t2.name, t3.name

FROM category AS t1

LEFT JOIN category AS t2 ON t2.parent = t1.category_id

LEFT JOIN category AS t3 ON t3.parent = t2.category_id

WHERE (t3.category_id = [child_id])

#get the path by child id, row number = member of family

SELECT t1.* FROM category AS t1

LEFT JOIN category AS t2 ON t2.parent = t1.category_id

LEFT JOIN category AS t3 ON t3.parent = t2.category_id

WHERE 

(t1.category_id = [child_id] OR t2.category_id = [child_id] OR t3.category_id = [child_id])

 ORDER BY t1.LEVEL

sql sub category

寫了一條sql 去拎sub category
有3層category, 用個parent id 去拎

寫左我好耐... ><

但sub select一定會慢... 要諗下有冇再好d寫先 

#get all child cat by parent id

SELECT ID FROM CATEGORY WHERE ID IN 

(SELECT ID FROM CATEGORY WHERE ID = [parent_id] OR PARENT_ID = [parent_id]) OR PARENT_ID IN

(SELECT ID FROM CATEGORY WHERE ID = [parent_id] OR PARENT_ID = [parent_id])