RSS

Manipulate Collections in Magento

A collection is a Model type containing other Models, it is basically used in Magento to handle product lists (ie. from a category or a bundle option), but not only.

TO DO: Explain how Magento Implements a collection – Use this to explain how Magento implements a collection by looking at code in a model, so that people can learn to write their own collections.

  1. $collection = Mage::getModel(‘catalog/category’)->load($categoryId)
  2.     ->getProductCollection()
  3.     ->addAttributeToSort(‘name’, ‘ASC’);

To sort using multiple Fields, you can chain calls to the Collection’s method addAttributeToSort(preferred)

  1. $collection = Mage::getModel(‘module/model_name’)->getCollection()
  2.     ->addAttributeToSort(‘order’, ‘ASC’)
  3.     ->addAttributeToSort(‘last_name’, ‘ASC’)
  4.     ->addAttributeToSort(‘first_name’, ‘ASC’)
  5. ;

TODO: use Magento’s API use cases, not Zend_Db_Select ones.

You can also pass IF/THEN statements, but be sure to use the proper quotation of your table’s fields.

  1. $collection = Mage::getModel(‘module/model_name’)->getCollection();
  2. $collection->getSelect()->order( array(‘IF(`order`>0, `order`, 9999) ASC’,
  3.      ‘last_name ASC’, ‘first_name ASC’) );

In this example, the table will be sorted by the order field, then by last name, then by first name, where order is greater than zero, followed by order being equal to or less than zero, all ascending.

Joining Tables

To add SQL joins to a select

  1. $collection = Mage::getModel(‘module/model_name’)->getCollection();
  2. $collection->getSelect()->join( array(‘table_alias’=>$this->getTable(‘module/table_name’)), ‘main_table.foreign_id = table_alias.primary_key’, array(‘table_alias.*’), ‘schema_name_if_different’);

In this example the join method takes an array of alias⇒table_name key pairs, then a standard join clause using `main_table` to refer to the original select, then an array of fields to be retrieved in the join (defaults to *), a different schema can be specified as a last parameter.

→join defaults to an inner join, others can be used:

→joinInner() →joinLeft() →joinRight() →joinFull() →joinCross() →joinNatural()

See lib/Zend/Db/Select.php for source.

Collection Manipulation Methods

Varien_Data_Collection

Varien/Data/Collection.php

addFilter($field, $value, $type = ‘and’)
addItem(Varien_Object $item)
clear()
count()
distinct($flag)
each($obj_method, $args=array())
getAllIds()
getCacheKey()
getCacheLifetime()
getCacheTags()
getColumnValues($colName)
getCurPage($displacement = 0)
getFirstItem()
getFlag($flag)
getItemByColumnValue($column, $value)
getItemById($idValue)
getItems()
getItemsByColumnValue($column, $value)
getIterator()
getLastItem()
getLastPageNumber()
getNewEmptyItem()
getPageSize()
getSize()
hasFlag($flag)
isLoaded()
loadData($printQuery = false, $logQuery = false)
load($printQuery = false, $logQuery = false)
removeItemByKey($key)
setCacheKey($key)
setCacheTags($tags)
setCurPage($page)
setDataToAll($key, $value=null)
setFlag($flag, $value = null)
setOrder($field, $direction = self::SORT_ORDER_DESC)
setPageSize($size)
toArray($arrRequiredFields = array())
toOptionArray()
toOptionHash()
toXml()
walk($callback, array $args=array())

Varien_Data_Collection_Db

Varien/Data/Collection/Db.php

Inherits above methods for Varien_Data_Collection, and in addition (re)defines the following:

addBindParam($name, $value)
addFieldToFilter($field, $condition=null)
addOrder($field, $direction = self::SORT_ORDER_DESC)
distinct($flag)
getConnection()
getData()
getIdFieldName()
getSelect()
getSelectCountSql()
getSize()
initCache($object, $idPrefix, $tags)
loadData($printQuery = false, $logQuery = false)
load($printQuery = false, $logQuery = false)
printLogQuery($printQuery = false, $logQuery = false, $sql = null)
resetData()
setConnection($conn)
setOrder($field, $direction = self::SORT_ORDER_DESC)
unshiftOrder($field, $direction = self::SORT_ORDER_DESC)

Filter Condition Codes

Attribute Code SQL Equivalent Description
eq =
neq !=
like LIKE
nlike NOT LIKE
in IN ()
nin NOT IN ()
is IS
notnull IS NOT NULL
null IS NULL
moreq >=
gt >
lt <
gteq >=
lteq <=
finset FIND_IN_SET()
from >= for use with dates
to <= for use with dates
date optional flag for use with from/to to specify that comparison value should first be converted to a date
datetime optional flag for use with from/to to specify that comparison value should first be converted to a datetime

If an array is passed but no attribute code specified, it will be interpreted as a group of OR conditions that will be processed in the same way.

If no attribute code is specified, it defaults to eq.

E.g.

  1. $collection->addAttributeToFilter(‘field_name’, array(
  2.     ‘in’ => array(1, 2, 3),
  3.     ));
  4. $collection->addAttributeToFilter(‘date_field’, array(
  5.    ‘from’ => ‘2000-09-10’,
  6.     ));
  7. $collection->addAttributeToFilter(‘date_field’, array(
  8.     ‘from’ => ’10 September 2000′,
  9.     ‘to’ => ’11 September 2000′,
  10.     ‘date’ => true, // specifies conversion of comparison values
  11.     ));
  12. $collection->addAttributeToFilter(‘field_name’, array(
  13.     ‘notnull’ => true,
  14.     ));
  15. $collection->addAttributeToFilter(‘field_name’, 1); // tests for equality
  16. // Add OR condition:
  17. $collection->addAttributeToFilter(array(
  18.     array(
  19.         ‘attribute’ => ‘field_name’,
  20.         ‘in’        => array(1, 2, 3),
  21.         ),
  22.     array(
  23.         ‘attribute’ => ‘date_field’,
  24.         ‘from’      => ‘2000-09-10’,
  25.         ),
  26.     ));

Mage_Eav_Model_Entity_Collection

Mage/Eav/Model/Entity/Collection/Abstract.php

Inherits above methods for Varien_Data_Collection_Db, and in addition (re)defines the following:

addAttributeToFilter($attribute, $condition=null, $joinType=’inner’) adds WHERE clause on $attribute specified by $condition
addAttributeToSelect($attribute, $joinType=false) gets the value for $attribute in the SELECT clause;
specify * to get all attributes (i.e. to execute SELECT *)
addAttributeToSort($attribute, $dir=’asc’) adds ORDER BY clause on $attribute
addEntityTypeToSelect($entityType, $prefix) doesn’t seem to do anything – don’t use
addExpressionAttributeToSelect($alias, $expression, string|array $attribute) adds SQL expression $expression, using $alias, to SELECT clause (typically containing aggregate functions such as SUM()COUNT());
when $attribute specifies a single attribute as a string, $expression can reference the attribute as simply {{attribute}}, but when passing an array of attributes, each attribute must be referenced in $expression by the name of the specific attribute;
N.B. use with groupByAttribute() when including aggregate functions q.v.
addFieldToFilter($attribute, $condition=null) alias for addAttributeToFilter()
addItem(Varien_Object $object) adds an object to the collection
addStaticField($field) specifies $field as being static, i.e. as existing on the entity table itself, rather than in the EAV table
delete() deletes all entities in the collection
exportToArray() returns collection data as a 2D array
getAllIds($limit=null, $offset=null) returns array of all entity IDs selected by current WHERE clause (optionally specifying $limit and $offset)
getAllIdsSql() not recommended – don’t use
getAttribute($attributeCode) for specified $attributeCode, returnsMage_Eav_Model_Entity_Attribute_Abstract object of the entity object used by the collection (i.e. calls getAttribute($attributeCode) on the entity object and returns the result)
getEntity() returns Mage_Eav_Model_Entity_Abstract object used by collection for attributes
getLoadedIds() returns array of IDs of currently loaded entities
getResource() returns Mage_Core_Model_Resource_Abstract instance;
actually an alias for getEntity() q.v. (an entity is a special type of resource, that is, Mage_Eav_Model_Entity_Abstract extendsMage_Core_Model_Resource_Abstract)
getRowIdFieldName() returns field name of ID attribute for entities in the collection
getTable($table) alias for Mage::getSingleton(‘core/resource’)->getTableName($table)
groupByAttribute($attribute) adds $attribute to GROUP BY clause
importFromArray($arr) imports 2D array into collection as objects
joinAttribute($alias, Mage_Eav_Model_Entity_Attribute_Abstract|string $attribute, string $bind, string $filter=null, string $joinType=’inner’, $storeId=null) joins another entity and adds attribute from joined entity, using $alias, toSELECT clause;
$attribute can specify attribute object, or string in format [entity]/[attribute];
$bind specifies attribute of the main entity on which to make join;
$filter specifies primary key of the joined entity on which to make join (normally defaults to entity_id);
$joinType should be inner or left
joinField($alias, $table, $field, $bind, $cond=null, $joinType=’inner’) joins regular table field using an attribute as foreign key
joinTable($table, $bind, $fields=null, $cond=null, $joinType=’inner’) joins table $table
load($printQuery = false, $logQuery = false) runs query and load data into collection;
specify $printQuery as true to print SQL for debugging
removeAttributeToSelect($attribute=null) removes $attribute from SELECT clause;
specify null to remove all attributes
save() saves all entities in the collection
setEntity(string|object $entity) sets entity object (i.e. Mage_Eav_Model_Entity_Abstract) used by collection for attributes
setObject(Varien_Object $object=null) sets template object for the collection
setOrder(string| $attribute, $dir=’desc’) alias for addAttributeToSort() q.v., identical except that it can accept array of attributes, and default $dir is desc;
N.B. overrides parent function
setPage($pageNum, $pageSize) sets LIMIT clause by specifying page number (one-indexed) and number of records per page;
N.B. equivalent to calling setCurPage($pageNum) andsetPageSize($pageSize) q.q.v.
setRowIdFieldName($fieldName) returns field name of ID attribute for entities in the collection
toArray(array $arrAttributes=array()) calls toArray($attAttributes) on each entity in collection, and returns result as array

ALL above content copied from Magento WIKI..

Advertisements
 
2 Comments

Posted by on July 23, 2013 in Magento

 

Tags:

List all payment methods in Magento

How to get a list with all available payment methods in Magento.

If for any reason you need to a get a list with all payment methods in Magento, you can do it easily by using the payment config class (app/code/core/Mage/Payment/Model/Config.php).

To get a list with all payments active and inactive:

1
$allAvailablePaymentMethods = Mage::getModel('payment/config')->getAllMethods();

To get a list with all active payment methods:

1
$allActivePaymentMethods = Mage::getModel('payment/config')->getActiveMethods();

To get a list with all credit cards that Magento supports:

1
$allCcTypes = Mage::getModel('payment/config')->getCcTypes();
 
1 Comment

Posted by on July 15, 2013 in Magento

 

Tags:

Magento addAttributeToFilter SQL Conditionals

If you want to customize query or product collection data you can easily to do that with addAttributeToFilter funcation in magento. In short you can easily customize product collection select query based on your requirement in magento development.

1
<?php $collection = Mage::getModel('catalog/product')->getCollection(); ?>

If you want get all fields then use below condition select

1
<?php $collection->addAttributeToSelect('*'); ?>

If you want to get limited fields then use below condition

1
<?php $collection->addAttributeToSelect(array('name', 'product_url', 'small_image')); ?>
1
2
3
4
5
6
<?php
$collection = Mage::getModel('catalog/product')->getCollection()
   ->addAttributeToSelect(array('name', 'product_url', 'small_image'))
   ->addAttributeToFilter('sku', array('like' => 'test-product%'))
    ->load();
?>

The above code would get a product collection which contain only products that have an SKU starting with “test-product”.

addAttributeToFilter Conditionals

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<?php
// Is Equal To (eq)
$collection->addAttributeToFilter('status', array('eq' => 1));
// Is Not Equal To (neq)
$collection->addAttributeToFilter('sku', array('neq' => 'test-product'));
// Greater Than (gt)
$collection->addAttributeToFilter('price', array('gt' => 10.05));
// Less Than (lt)
$collection->addAttributeToFilter('price', array('lt' => 15.09));
// Greater Than or Equal To (gteq)
$collection->addAttributeToFilter('price', array('gteq' => 2.29));
// Less Than or Equal To (lteq)
$collection->addAttributeToFilter('price', array('lteq' => 5.44));
// Contains (like) - also uses % wildcards
$collection->addAttributeToFilter('sku', array('like' => 'test-product%'));
// Does Not Contain (nlike) - also uses % wildcards
$collection->addAttributeToFilter('sku', array('nlike' => 'test-product%'));
// In Array (in)
$collection->addAttributeToFilter('id', array('in' => array(1,3,12)));
// Not In Array (nin)
$collection->addAttributeToFilter('id', array('nin' => array(1,2,12)));
// Is NULL (null)
$collection->addAttributeToFilter('description', 'null');
// Is Not NULL (notnull)
$collection->addAttributeToFilter('description', 'notnull');
?>

Debugging The SQL Query
There are two ways to debug the query being executed when loading a collection in Magento.

1
2
3
4
5
6
7
8
<?php
// Method 1
Mage::getModel('catalog/product')->getCollection()->load(true);
 
// Method 2 (Quicker, Recommended)
$collection = Mage::getModel('catalog/product')->getCollection();
echo $collection->getSelect();
?>
 
Leave a comment

Posted by on July 15, 2013 in Magento

 

Tags:

Get order ids filtered by status in Magento

This can be run as a script from the base Magento install folder. If its running inside of a Magento file already (controller or block or whatever) you don’t need the first three lines.

<?php
require_once('app/Mage.php');
Mage::app();

$orders = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('status', 'complete')
   // ->addAttributeToSelect('customer_email')
    ;
foreach ($orders as $order) {
    $email = $order->getCustomerEmail();
    echo $email . "\n";
}

EDIT:

To see all orders with statuses and emails:

$orders = Mage::getModel('sales/order')->getCollection()
    //->addFieldToFilter('status', 'complete')
    //->addAttributeToSelect('customer_email')
    ->addAttributeToSelect('status')
    ;
foreach ($orders as $order) {
    $email = $order->getCustomerEmail();
    echo $order->getId() . ": '" . $order->getStatus() . "', " . $email . "\n";
}
 
Leave a comment

Posted by on July 10, 2013 in Magento

 

Tags:

Programatically create customer and order in Magento with full blown One Page Checkout process under the hood

Its been a while since my last article. Figured I might write one before Google search punishes me. Recently I was working on a task that, among other things, required an order to be created programatically alongside with the customer and its billing and shipping address. Example I will demonstrate is just one way of doing things. If you have extra time on your hands, feel free to trace the code for more elegant solution.

For starters, lets create a customer and establish a login session right away for this newly created customer. Here is the code sample that does just that.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$customer = Mage::getModel('customer/customer');
//$customer  = new Mage_Customer_Model_Customer();
$password = '123456';
$email = 'ajzele@mymail.com';
$customer->setWebsiteId(Mage::app()->getWebsite()->getId());
$customer->loadByEmail($email);
//Zend_Debug::dump($customer->debug()); exit;
if(!$customer->getId()) {
    $customer->setEmail($email);
    $customer->setFirstname('Johnny');
    $customer->setLastname('Doels');
    $customer->setPassword($password);
}
try {
    $customer->save();
    $customer->setConfirmation(null);
    $customer->save();
    //Make a "login" of new customer
    Mage::getSingleton('customer/session')->loginById($customer->getId());
}
catch (Exception $ex) {
    //Zend_Debug::dump($ex->getMessage());
}

You can run this code from anywhere in Magento (model, view, controller…), it should do the trick.

At this point we have created new customer and made him “look like” he is logged in, or shall I say we established a session with his new credentials.

Now lets add a shipping and billing address to customer. Remember, these are needed in order to do checkout. Here is the code that programatically adds the shipping and billing address. In my case, I am using same address for both shipping and billing. If you prefer you can extend the logic and create two separate addresses and make one default for billing, other for shipping.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
//Build billing and shipping address for customer, for checkout
$_custom_address = array (
    'firstname' => 'Branko',
    'lastname' => 'Ajzele',
    'street' => array (
        '0' => 'Sample address part1',
        '1' => 'Sample address part2',
    ),
    'city' => 'Osijek',
    'region_id' => '',
    'region' => '',
    'postcode' => '31000',
    'country_id' => 'HR', /* Croatia */
    'telephone' => '0038531555444',
);
$customAddress = Mage::getModel('customer/address')
//$customAddress = new Mage_Customer_Model_Address();
$customAddress->setData($_custom_address)
            ->setCustomerId($customer->getId())
            ->setIsDefaultBilling('1')
            ->setIsDefaultShipping('1')
            ->setSaveInAddressBook('1');
try {
    $customAddress->save();
}
catch (Exception $ex) {
    //Zend_Debug::dump($ex->getMessage());
}
Mage::getSingleton('checkout/session')->getQuote()->setBillingAddress(Mage::getSingleton('sales/quote_address')->importCustomerAddress($customAddress));

Now that we have a logged in newly created customer with assigned addresses, lets add some products to the cart in his name. Here is the sample code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/* If we wish to load some product by some attribute value diferent then id */
$product = Mage::getModel('catalog/product')->getCollection()
    /* Remember, you can load/find product via any attribute, better if its attribute with unique value */
    ->addAttributeToFilter('sku', 'some-sku-value')
    ->addAttributeToSelect('*')
    ->getFirstItem();
/* Do a full product load, otherwise you might get some errors related to stock item */
$product->load($product->getId());
$cart = Mage::getSingleton('checkout/cart');
/* We want to add only the product/products for this user and do so programmatically, so lets clear cart before we start adding the products into it */
$cart->truncate();
$cart->save();
$cart->getItems()->clear()->save();
try {
    /* Add product with custom oprion? =>  some-custom-option-id-here: value to be read from database or assigned manually, hardcoded? Just example*/
    //$cart->addProduct($product, array('options'=> array('some-custom-option-id-here' => 'Some value goes here');
    $cart->save();
}
catch (Exception $ex) {
    echo $ex->getMessage();
}
unset($product);

And for the very end, the actual process of going trough “One Page Checkout” under the hood. Here is the sample code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$storeId = Mage::app()->getStore()->getId();
$checkout = Mage::getSingleton('checkout/type_onepage');
$checkout->initCheckout();
$checkout->saveCheckoutMethod('register');
$checkout->saveShippingMethod('flatrate_flatrate');
$checkout->savePayment(array('method'=>'checkmo'));
try {
    $checkout->saveOrder();
}
catch (Exception $ex) {
    //echo $ex->getMessage();
}
/* Clear the cart */
$cart->truncate();
$cart->save();
$cart->getItems()->clear()->save();
/* Logout the customer you created */
Mage::getSingleton('customer/session')->logout();

And thats it. This code was tested on Magento 1.3 version, but I see no reason why it would not work on latest (as of time of this writing) 1.4.0.1 version.

In any case, you hope you find it useful. As always, word of advice, this is just for testing/education purpose so please do not blindly embed it on live sites.

 
Leave a comment

Posted by on July 10, 2013 in Magento

 

Tags:

Programmatically Change Admin Password in Magento

 

Mage::getModel('admin/user')
     ->loadByUsername('admin')
     ->setPassword($password)
     ->save();
 

If you want to do it via a Query here is the query to run:

UPDATE admin_user SET password=CONCAT(MD5('qXpassword'), ':qX') WHERE username='admin';

the resulting password would be ‘password’, qX is the salt you could use any 2 letter combo..

UPDATE admin_user SET password=CONCAT(MD5('bL123456'), ':bL') WHERE username='admin';

would make the password 123456 …

 
 
Leave a comment

Posted by on July 10, 2013 in Magento

 

Tags:

Adding address programmatically in magento

$addressData =  array (
                    [prefix] => test
                    [firstname] => test
                    [middlename] => test
                    [lastname] => test
                    [suffix] => tes
                    [company] => test
                    [street] => 34
24
                    [city] => 234
                    [country_id] => US
                    [region] => Alabama
                    [region_id] => 1
                    [postcode] => 234
                    [telephone] => 234
                    [fax] => 234
                    [is_default_billing] => 1
                    [is_default_shipping] => 
                );
 
 $customer = Mage::getModel('customer/customer');
 $customer->loadByEmail('test@test.com');
 $address   = Mage::getModel('customer/address');
 $address->addData($addressData);
 $customer->addAddress($address);

 $customer->save();

 

 
2 Comments

Posted by on July 10, 2013 in Magento

 

Tags: