这节打算介绍一下Magento的Varien_Data_Collections,我从Alanstorm翻译过来的(部分翻译,读者也可以练一下英文能力)
Varien Data Collections 这是什么东西?哈哈,你看完下面的文章,你就会知道它在Magento中的核心的作用了。
作为一个PHPer,如果想把一些一组相关的变量集合在一起有两个方法:Array 和自定义的数据结构,Varien_Object 就是后者。
首先介绍一下 Varien_Object 的使用方法:
$thing_1 = new Varien_Object();
$thing_1->setName('Richard');
$thing_1->setAge(24);
$thing_2 = new Varien_Object();
$thing_2->setName('Jane');
$thing_2->setAge(12);
$thing_3 = new Varien_Object();
$thing_3->setName('Spot');
$thing_3->setLastName('The Dog');
$thing_3->setAge(7);
Varient_Object 类是 Magento 中所有的 Models 的父类。继承自Varient_Object 的子类都有 getter 和 setter 两个魔术方法(更多关于魔术方法请参考:)。例子:
var_dump($thing_1->getName());
如果你不知道Varient_Object 的成员属性有哪些,可以getData()把它们打印出来
var_dump($thing_3->getData());
输出如下
array
'name' => string 'Spot' (length=4)
'last_name' => string 'The Dog' (length=7)
'age' => int 7
$thing_1->setLastName('Smith');
大家注意到了没'last_name’多了'_',你可以以Camel 式名称来getter和setter它。
var_dump($thing_3["last_name"]);
上面我们已经建了三个Object,接着把它们放到Collection中(这里只Varento_Data_Collection),Collection是PHPer自定义的数据类型。
$collection_of_things = new Varien_Data_Collection();
$collection_of_things
->addItem($thing_1)
->addItem($thing_2)
->addItem($thing_3);
由于Magento中大部分的data Collections都是继承自Varient_Data_Collection,所以该上面的使用方法在Magento中随处可见。
foreach($collection_of_things as $thing)
{
var_dump($thing->getData());
}
也可以直接访问第一个或者最后一个成员
var_dump($collection_of_things->getFirstItem());
var_dump($collection_of_things->getLastItem()->getData());
或者可以把它转换成XML格式
var_dump( $collection_of_things->toXml() );
或者只提取其中的一个Column的值(Varient_Data_Collection这如其名,它对应SQL语句的查询结果)
var_dump($collection_of_things->getColumnValues('name'));
甚至提供了过滤的能力(可以说SQL的查询条件和排序功能在Magento的Varient_Data_Collection中都实现了)
var_dump($collection_of_things->getItemsByColumnValue('name','Spot'));
Model Collections
因此,这是个非常好的体验,但是有什么重要的作用呢?因为上面我们说过Magento内建的data Collections都继承自它。这就意味着:你可以对一个product Collection 进行sort:
public function testAction()
{
$collection_of_products = Mage::getModel('catalog/product')->getCollection();
var_dump($collection_of_products->getFirstItem()->getData());
}
大部分的Magento Model objects 都有一个名字为 getCollection的方法,该方法返回上述的 Collection,并且系统中Magento Model Objects 默认初始化并返回该Collection.
同Magento中其他大部分的Collection一样,product Collection 继承自Varient_Data_Collection_Db,这样就提供很多有用的方法给我们获取具体的select statement:
public function testAction()
{
$collection_of_products = Mage::getModel('catalog/product')->getCollection();
var_dump($collection_of_products->getSelect()); //might cause a segmentation fault
}
上面的方法将输出
object(Varien_Db_Select)[94]
protected '_bind' =>
array
empty
protected '_adapter' =>
...
哈哈!由于Magento 实现了Zend framework的数据库抽象层,所以你的SQL语句是以Object的形式出现的如:
public function testAction()
{
$collection_of_products = Mage::getModel('catalog/product')->getCollection();
//var_dump($collection_of_products->getSelect()); //might cause a segmentation fault
var_dump(
(string) $collection_of_products->getSelect()
);
}
结果如下:
'SELECT `e`.* FROM `catalog_product_entity` AS `e`'
更多时候是更复杂的一些查询:
string 'SELECT `e`.*, `price_index`.`price`, `price_index`.`final_price`, IF(`price_index`.`tier_price`, LEAST(`price_index`.`min_price`, `price_index`.`tier_price`),`price_index`.`min_price`) AS `minimal_price`, `price_index`.`min_price`,`price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0'
此外由于Magento中部分数据表是设计成EAV形式的,由于column那么多不可能都返回,默认返回部分column,你可以以下面的方式返回全部Column的结果:
$collection_of_products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*'); //the asterisk is like a SQL SELECT *
或者在默认的基础上增加一个Column:
//or just one
$collection_of_products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('meta_title');
或者增加多个Column:
//or just one
$collection_of_products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('meta_title')
->addAttributeToSelect('price');
关于Magento 的 Lazy Loading
由于PHP支持了ORM,所以一般情况下,我们在写一个SQL语句或初始化一个对象的时候,该查询语句将立刻执行
$model = new Customer();
//SQL Calls being made to Populate the Object
echo 'Done'; //execution continues
而在Magento中,使用了 Lazy Loading 的方法,使得该语句没有立刻执行。 Lazy Loading,简而言之就是只有当client-programmer 需要读取数据的时候才会执行相应的查询语句。像下面这样子
$collection_of_products = Mage::getModel('catalog/product')
->getCollection();
实际上之时Magento还没有去读取数据库,接下来你可以增加一些attibutes to select:
$collection_of_products = Mage::getModel('catalog/product')
->getCollection();
$collection_of_products->addAttributeToSelect('meta_title');
下面是一些对Database Collection进行过滤的更多方法(看原文吧)
The most important method on a database Collection is addFieldToFilter
. This adds your WHERE
clauses. Consider this bit of code, run against the sample data database (substitute your own SKU is you’re using a different set of product data)
public function testAction()
{
$collection_of_products = Mage::getModel('catalog/product')
->getCollection();
$collection_of_products->addFieldToFilter('sku','n2610');
//another neat thing about collections is you can pass them into the count //function. More PHP5 powered goodness
echo "Our collection now has " . count($collection_of_products) . ' item(s)';
var_dump($collection_of_products->getFirstItem()->getData());
}
The first parameter of addFieldToFilter
is the attribute you wish to filter by. The second is the value you’re looking for. Here’s we’re adding a sku
filter for the value n2610
.
The second parameter can also be used to specify the type of filtering you want to do. This is where things get a little complicated, and worth going into with a little more depth.
So by default, the following
$collection_of_products->addFieldToFilter('sku','n2610');
is (essentially) equivalent to
WHERE sku = "n2610"
Take a look for yourself. Running the following
public function testAction()
{
var_dump(
(string)
Mage::getModel('catalog/product')
->getCollection()
->addFieldToFilter('sku','n2610')
->getSelect());
}
will yield
SELECT `e`.* FROM `catalog_product_entity` AS `e` WHERE (e.sku = 'n2610')'
Keep in mind, this can get complicated fast if you’re using an EAV attribute. Add an attribute
var_dump(
(string)
Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*')
->addFieldToFilter('meta_title','my title')
->getSelect()
);
and the query gets gnarly.
SELECT `e`.*, IF(_table_meta_title.value_id>0, _table_meta_title.value, _table_meta_title_default.value) AS `meta_title`
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_varchar` AS `_table_meta_title_default`
ON (_table_meta_title_default.entity_id = e.entity_id) AND (_table_meta_title_default.attribute_id='103')
AND _table_meta_title_default.store_id=0
LEFT JOIN `catalog_product_entity_varchar` AS `_table_meta_title`
ON (_table_meta_title.entity_id = e.entity_id) AND (_table_meta_title.attribute_id='103')
AND (_table_meta_title.store_id='1')
WHERE (IF(_table_meta_title.value_id>0, _table_meta_title.value, _table_meta_title_default.value) = 'my title')
Not to belabor the point, but try not to think too much about the SQL if you’re on deadline.
Other Comparison Operators
I’m sure you’re wondering “what if I want something other than an equals by query”? Not equal, greater than, less than, etc. The addFieldToFilter
method’s second parameter has you covered there as well. It supports an alternate syntax where, instead of passing in a string, you pass in a single element Array.
The key of this array is the type of comparison you want to make. The value associated with that key is the value you want to filter by. Let’s redo the above filter, but with this explicit syntax
public function testAction()
{
var_dump(
(string)
Mage::getModel('catalog/product')
->getCollection()
->addFieldToFilter('sku',array('eq'=>'n2610'))
->getSelect()
);
}
Calling out our filter
addFieldToFilter('sku',array('eq'=>'n2610'))
As you can see, the second parameter is a PHP Array. Its key is eq
, which stands for equals . The value for this key is n2610
, which is the value we’re filtering on.
Magento has a number of these english language like filters that will bring a tear of remembrance (and perhaps pain) to any old perl developers in the audience.
Listed below are all the filters, along with an example of their SQL equivalents.
array("eq"=>'n2610')
WHERE (e.sku = 'n2610')
array("neq"=>'n2610')
WHERE (e.sku != 'n2610')
array("like"=>'n2610')
WHERE (e.sku like 'n2610')
array("nlike"=>'n2610')
WHERE (e.sku not like 'n2610')
array("is"=>'n2610')
WHERE (e.sku is 'n2610')
array("in"=>array('n2610'))
WHERE (e.sku in ('n2610'))
array("nin"=>array('n2610'))
WHERE (e.sku not in ('n2610'))
array("notnull"=>'n2610')
WHERE (e.sku is NOT NULL)
array("null"=>'n2610')
WHERE (e.sku is NULL)
array("gt"=>'n2610')
WHERE (e.sku > 'n2610')
array("lt"=>'n2610')
WHERE (e.sku < 'n2610')
array("gteq"=>'n2610')
WHERE (e.sku >= 'n2610')
array("moreq"=>'n2610') //a weird, second way to do greater than equal
WHERE (e.sku >= 'n2610')
array("lteq"=>'n2610')
WHERE (e.sku <= 'n2610')
array("finset"=>array('n2610'))
WHERE (find_in_set('n2610',e.sku))
array('from'=>'10','to'=>'20')
WHERE e.sku >= '10' and e.sku <= '20'
Most of these are self explanatory, but a few deserve a special callout
in, nin, find_in_set
The in
and nin
conditionals allow you to pass in an Array of values. That is, the value portion of your filter array is itself allowed to be an array.
array("in"=>array('n2610','ABC123')
WHERE (e.sku in ('n2610','ABC123'))
notnull, null
The keyword NULL is special in most flavors of SQL. It typically won’t play nice with the standard equality (=
) operator. Specifying notnull
or null
as your filter type will get you the correct syntax for a NULL comparison while ignoring whatever value you pass in
array("notnull"=>'n2610')
WHERE (e.sku is NOT NULL)
from - to filter
This is another special format that breaks the standard rule. Instead of a single element array, you specify a two element array. One element has the key from
, the other element has the key to
. As the keys indicated, this filter allows you to construct a from/to range without having to worry about greater than and less than symbols
public function testAction
{
var_dump(
(string)
Mage::getModel('catalog/product')
->getCollection()
->addFieldToFilter('price',array('from'=>'10','to'=>'20'))
->getSelect()
);
}
The above yields
WHERE (_table_price.value >= '10' and _table_price.value <= '20')'
AND or OR, or is that OR and AND?
Finally, we come to the boolean operators. It’s the rare moment where we’re only filtering by one attribute. Fortunately, Magento’s Collections have us covered. You can chain together multiple calls to addFieldToFilter
to get a number of “AND” queries.
function testAction()
{
echo(
(string)
Mage::getModel('catalog/product')
->getCollection()
->addFieldToFilter('sku',array('like'=>'a%'))
->addFieldToFilter('sku',array('like'=>'b%'))
->getSelect()
);
}
By chaining together multiple calls as above, we’ll produce a where clause that looks something like the the following
WHERE (e.sku like 'a%') AND (e.sku like 'b%')
To those of you that just raised your hand, yes, the above example would always return 0 records. No sku can begin with BOTH an a
and a b
. What we probably want here is an OR
query. This brings us to another confusing aspect of addFieldToFilter
’s second parameter.
If you want to build an OR
query, you need to pass an Array of filter Arrays in as the second parameter. I find it’s best to assign your individual filter Arrays to variables
public function testAction()
{
$filter_a = array('like'=>'a%');
$filter_b = array('like'=>'b%');
}
and then assign an array of all my filter variables
public function testAction()
{
$filter_a = array('like'=>'a%');
$filter_b = array('like'=>'b%');
echo(
(string)
Mage::getModel('catalog/product')
->getCollection()
->addFieldToFilter('sku',array($filter_a,$filter_b))
->getSelect()
);
}
In the interest of being explicit, here’s the aforementioned Array of filter Arrays.
array($filter_a,$filter_b)
This will gives us a WHERE clause that looks something like the following
WHERE (((e.sku like 'a%') or (e.sku like 'b%')))
Wrap Up
You’re now a Magento developer walking around with some serious firepower. Without having to write a single line of SQL you now know how to query Magento for any Model your store or application might need.