MVC Developers Part 9 – Varien Data Collections

  • varien data collection

Originally as a PHP programmer, if you wanted to gather together a group of related variables that you had a choice, the venerable Array. Although it shares a name with C’s array of memory addresses, a PHP array is a general purpose dictionary like object with the behavior of a numerically indexed array variable.

In other languages, the choice is not so simple. You have several data structures to choose from, each with specific advantages in storage, speed and semantics. The PHP philosophy was to remove that choice from the client programmer and give them a useful data structure that was “good enough”. I have long held that PHP arrays are a major reason for the popularity of the platform.

Canadian Pharmacy is another fine company at the shop that has a long time history of providing our bodies with the supplements we need. Buy generic viagra. Every discount drug order from Canada Drugs is backed by our No-Risk Guarantee that guarantees you 100% free shipping on every order.

All this is annoying to a certain type of software developer, PHP 5 and put out to change the status quo by offering built-in classes and interfaces that allow your own data structures.

$array = new ArrayObject();
class MyCollection extends ArrayObject{}
$collection = new MyCollection();
$collection[] = 'bar';

Although this is still irritated by a particular type of software developer, if you do not have access to a low level Implementation details, you have the ability to array objects, with methods such as encapsulating functionality specification creation. You can also set rules to a level of security by offering only certain types of objects in your collection.

It should come as no surprise that Magento offers a number of these collections. In fact, any model that satisfies the following interfaces Magento gets a kind collection for free. Understanding how these collections work is an essential component of an effective Magento programmer. We are going to take a look at Magento Collections, starting from the bottom and work our way up. Setup a controller action where you can execute arbitrary code, and let’s get started.

A Collection of Things

First we visit a number of new objects.

$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);

Varien_Object the class defines the object models inherit all of Magento. This is a common pattern in object oriented systems, and ensures that you always have an easy way to add methods / function to every object in your system without having to every class file to edit.

Any object that extends from Varien_Object has magic getter and setters that can be used for data properties to set. Give this a try

var_dump($thing_1->getName());

If you do not know what the property name you are looking for, you can find everything from data as an array

var_dump($thing_3->getData());

The above gives you an array similar

array
'name' => string 'Spot' (length=4)
'last_name' => string 'The Dog' (length=7)
'age' => int 7

Please note the property named “name”? If there is an underscore separate property, your camel case when the getter and setter use magic.

$thing_1->setLastName('Smith');

In more recent versions of Magento, can use array-style bracket to access properties

var_dump($thing_3["last_name"]);

The possibility of such things is part of the power of PHP5, and the development style of a certain class of people mean when they say “Object Oriented Programming”.

So now we have some objects, we add them to a collection. Remember, a collection as an array, but is determined by a PHP programmer.

$collection_of_things = new Varien_Data_Collection();
$collection_of_things
->addItem($thing_1)
->addItem($thing_2)
->addItem($thing_3);

The Varien_Data_Collection is the collection that most data sets Magento inherit. Any method that you can call on a Varien_Data_Collection please call the collections higher up the chain (we’ll see more on this later)

What can we do with a collection? For one, with one can use foreach to iterate over the

foreach($collection_of_things as $thing)
{
    var_dump($thing->getData());
}  

There are also keyboard shortcuts for entering the first and last items

var_dump($collection_of_things->getFirstItem());
var_dump($collection_of_things->getLastItem()->getData());    

Do you collect data as XML? There is a method that

var_dump( $collection_of_things->toXml() );     

Only want a certain area?

var_dump($collection_of_things->getColumnValues('name'));

The team of Magento have even given us some rudimentary filtering capabilities.

var_dump($collection_of_things->getItemsByColumnValue('name','Spot'));

Fun stuff.

Model Collections

So, this is an interesting exercise, but why do we care?

We care because all of Magento was built in data collections inherit from this object. That means that if you have a product you can use the same sort of thing Collection do. Let us take a look

public function testAction()
{
    $collection_of_products = Mage::getModel('catalog/product')->getCollection();
    var_dump($collection_of_products->getFirstItem()->getData());
}

Most Magento Model objects have a method called getCollection a collection that, by default, is initialized for each object of that type in the system restore will return.

A Quick Note: Magento’s Data Collection contains a lot of complicated logic that handles when an index or cache, and the logic for the Authority entity any use. Sequential method is the same collection on his life may often result in unexpected behavior. Because all of the following examples are packaged in a single action method. I would recommend doing the same while you experiment. Var_dump also Xdebug’s been a godsend when working with Magento Objects and collections, for it will (mostly) intelligently shorting Showing huge recursive objects, but still a useful representation of the object structure to show you.

Products Collection, as well as many other Magento Collections also the Varien_Data_Collection_Db class in their ancestor chain. This gives us many useful methods. For example, if the select statement is using your collection to see

public function testAction()
{
    $collection_of_products = Mage::getModel('catalog/product')->getCollection();
    var_dump($collection_of_products->getSelect()); //might cause a segmentation fault
}

The output of the above will be

object(Varien_Db_Select)[94]
  protected '_bind' =>
    array
      empty
  protected '_adapter' =>
...

Oops! Because Magento is using the database abstraction layer Send your Select is also an object. We show that if a more useful string.

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()
    );
}

Sometimes this will result in a simple selection

'SELECT `e`.* FROM `catalog_product_entity` AS `e`'

Other times, something a bit more complicated

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'

The difference depends on which attributes you want to select, and the above mentioned indexing and caching. If you already following along with the other articles, you know that many Magento models (including the Product Model), an EAV system to use. By default, an EFSA Collection does not contain all attributes of an object. You can add them all using the method addAttributeToSelect

$collection_of_products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*');  //the asterisk is like a SQL SELECT *

Or, you can only

//or just one
$collection_of_products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('meta_title');

or chain consists of several

//or just one
$collection_of_products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('meta_title')
->addAttributeToSelect('price');

Lazy Loading

One thing that PHP developers will stumble on new ORM system’s Magento Magento is the database when making calls. If your literal SQL, or even if you write a basic system using ORM, SQL calls are often made directly when instantiating an object.

$model = new Customer();
//SQL Calls being made to Populate the Object
echo 'Done'; //execution continues

Magento does not work that way. Instead, the concept of using Lazy Loading. In simplified terms, Lazy loading means that no SQL calls are made until the client programmer must access the data. This means that when you make something like this

$collection_of_products = Mage::getModel('catalog/product')
->getCollection();

Magento is actually not gone to the database yet. You can safely add attributes later

$collection_of_products = Mage::getModel('catalog/product')
->getCollection();
$collection_of_products->addAttributeToSelect('meta_title');

and not be afraid that Magento is a database query each time a new attribute added. The database query will not happen until you try an item in the collection.

In general, try not worry too much about the implementation details in your daily work. It’s good to know that there s SQL backend and does Magento SQLy thing, but when your’e coding a function to try to forget about it, and just treating the objects as building blocks that do what you need.

Filtering Database Collections

The main method in a database is addFieldToFilter Collection. This adds your WHERE clauses. Consider this piece of code, running against the sample database information (substitute your own SKU’s using a different set of data using product)

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 is the attribute of addFieldToFilter you want to filter. The second is the value you need. Here we have a SKU for the n2610 filter to add value.

The second parameter can also be used to the type of filtering you want to give up. This is what it’s all a bit complicated, and worth to go with a bit more depth.

So by default the following

$collection_of_products->addFieldToFilter('sku','n2610');   

is (essentially) equal to

WHERE sku = "n2610"

Take a look for yourself. Perform 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 quickly complicated when 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 is 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 much about the SQL when you are on deadline.

Other Comparison Operators

I’m sure you’re wondering “what if I want something different than a similar query? Not equal, greater than, less than, etc. The method addFieldToFilter second parameter you there also. It supports an alternative syntax, which, instead of along a string, you pass in a single element array.

The key of this array is the type of comparison you want. The value associated with that key value that you want to filter. Let again 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 our filter

addFieldToFilter('sku',array('eq'=>'n2610'))

As you can see, the second parameter is a PHP array. The key is eq, which stands for equals. The value of this key is n2610, that is the value that we’re filtering on.

Magento is a number of these languages as English filters, a tear of remembrance (and perhaps pain) to all the old perl developers in the audience.

Below are all the filters, along with a sample 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 caption

in, nin, find_in_set

Nin in the conditionals and you can pass in an array of values. That is, the value portion of your filter array itself is allowed to 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 will not usually play nice with the standard equal (=) operator. Specify notnull or null if your type of filter you get the correct syntax for a NULL comparison while ignoring the value you pass in

array("notnull"=>'n2610')
WHERE (e.sku is NOT NULL)

from – to filter

This is a special format that the standard line breaks. Instead of a single element array, specify a two-element array. One element is the key out, the other element is the key to. As the keys above, this filter allows you to build one from / to reach without having to worry about more than or 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 is the rare moment when we can only filter by one attribute. Fortunately, Magento’s Collections us covered. You can chain multiple calls together addFieldToFilter get some “and” questions.

function testAction()
{
        echo(
        (string)
        Mage::getModel('catalog/product')
        ->getCollection()
        ->addFieldToFilter('sku',array('like'=>'a%'))
        ->addFieldToFilter('sku',array('like'=>'b%'))
        ->getSelect()
        );
}

By chaining multiple calls as above, we produce a where clause something that looks like the next

WHERE (e.sku like 'a%') AND (e.sku like 'b%')

For those of you who just raised your hand, yes, the above example would always return 0 records. Sku no one can start with both A and B. What we probably want this one or query. This brings us to another confusing aspect of the second addFieldToFilter parameter.

If you have a query or to build, you need an array of arrays filtering only if the second parameter. I think it suits your individual filter arrays to assign variables

public function testAction()
{
        $filter_a = array('like'=>'a%');
        $filter_b = array('like'=>'b%');
}

and then assign an array of all variables my filter

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 its explicit, here is the aforementioned array of filter arrays.

array($filter_a,$filter_b)

This will give us a WHERE clause something like the following

WHERE (((e.sku like 'a%') or (e.sku like 'b%')))

Wrap Up

You’re a Magento developer to walk around with some serious firepower. Without writing a single line of SQL you now know how Magento query for any model shop or your application might need.

(Based on Alanstorm Turtorial)

This seemed to help me a lot. I noticed an effect after about 2 weeks of use. Get ciprofloxacin on line! There are a lot of legitimate mail-order pharmacies in this country.

Comments

Leave a Reply

Your email address will not be published.

2 + seventeen =

Security Code: