Magento Join, filter, select and sort attributes, fields und tables

Gepostet am Oktober 24, 2012

Collection zum Auslesen von Daten

Wie folgt liest man eine Collection im Magento aus:

1
2
$collection = Mage::getModel('catalog/product')->getCollection();
//Beispiel: $collection =  Mage::getModel('catalog/product')->getCollection();

Collection Ausgeben des SQL Befehls

1
echo Mage::getModel('catalog/product')->getCollection()->getSelect();

Collection Filtern

Zu dieser Collection können Filter hinzugefügt werden, also eigentlich eine WHERE Abfrage

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
// select all attributes
$collection->addAttributeToSelect('*');
 
// select specific attributes
$collection->addAttributeToSelect(array('name', 'url_key', 'type_id'));
 
// select only those items whose status = 1
$collection->addAttributeToFilter('status', 1);
 
// alternative to select only those items whose status = 1
$collection->addAttributeToFilter('status', array('eq' => 1));
 
// using LIKE statement
$collection->addAttributeToFilter('sku', array('like' => '%CH%'));
 
// using IN statement,
// i.e. selecting only those items whose ID fall in the given array
$collection->addAttributeToFilter('entity_id', array('in' => array(1, 14, 51, 52)));
 
// selecting only those items whose ID is greater than the given value
$collection->addAttributeToFilter('entity_id', array('gt' => 5));
 
// select by date range
$collection->addAttributeToFilter('date_field', array(
    'from' => '10 September 2010',
    'to' => '21 September 2010',
    'date' => true, // specifies conversion of comparison values
    ));
 
// Add OR condition:
$collection->addAttributeToFilter(array(
    array(
        'attribute' => 'field_name',
        'in'        => array(1, 2, 3),
        ),
    array(
        'attribute' => 'date_field',
        'from'      => '2010-09-10',
        ),
    ));
 
/*
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
*/

Collection Sortieren

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

Collection SQL Expression hinzufügen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
* Add attribute expression (SUM, COUNT, etc)
*
* Example: (‘sub_total’, ‘SUM({{attribute}})’, ‘revenue’)
* Example: (‘sub_total’, ‘SUM({{revenue}})’, ‘revenue’)
* Example: addExpressionAttributeToSelect('categories',"CONCAT(',',{{category_ids}},',')",'category_ids')
*
* For some functions like SUM use groupByAttribute.
*
* @param string $alias
* @param string $expression
* @param string $attribute
* @return Mage_Eav_Model_Entity_Collection_Abstract
*/
addExpressionAttributeToSelect($alias, $expression, $attribute)

Collection Hinzufügen von GroupBy und OrderBy

1
2
3
4
5
6
/**
* Groups results by specified attribute
*
* @param string|array $attribute
*/
groupByAttribute($attribute)
1
2
3
4
//nach getSelect()
$collection
->getSelect()
->group('fildname');

Collection per Attribute zur Abfrage hinzufügen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
* Add attribute from joined entity to select
*
* Examples:
* (‘billing_firstname’, ‘customer_address/firstname’, ‘default_billing’)
* (‘billing_lastname’, ‘customer_address/lastname’, ‘default_billing’)
* (‘shipping_lastname’, ‘customer_address/lastname’, ‘default_billing’)
* (‘shipping_postalcode’, ‘customer_address/postalcode’, ‘default_shipping’)
* (‘shipping_city’, $cityAttribute, ‘default_shipping’)
*
* Developer is encouraged to use existing instances of attributes and entities
* After first use of string entity name it will be cached in the collection
*
* @todo connect between joined attributes of same entity
* @param string $alias alias for the joined attribute
* @param string|Mage_Eav_Model_Entity_Attribute_Abstract $attribute
* @param string $bind attribute of the main entity to link with joined $filter
* @param string $filter primary key for the joined entity (entity_id default)
* @param string $joinType inner|left
* @return Mage_Eav_Model_Entity_Collection_Abstract
*/
joinAttribute($alias, $attribute, $bind, $filter=null, $joinType=’inner’, $storeId=null)
1
2
//nach getSelect()
$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');

Collection Tabelle zur Abfrage hinzufügen

1
2
3
4
5
6
7
8
9
10
11
/**
* Join a table
*
* @param string|array $table
* @param string $bind
* @param string|array $fields
* @param null|array $cond
* @param string $joinType
* @return Mage_Eav_Model_Entity_Collection_Abstract
*/
joinTable($table, $bind, $fields=null, $cond=null, $joinType=’inner’)

Collection Feld zur Abfrage hinzufügen (JoinLeft)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
* Join regular table field and use an attribute as fk
*
* Examples:
* (‘country_name’, ‘directory/country_name’, ‘name’, ‘country_id=shipping_country’, “{{table}}.language_code=’en’”, ‘left’)
*
* @param string $alias ‘country_name’
* @param string $table ‘directory/country_name’
* @param string $field ‘name’
* @param string $bind ‘PK(country_id)=FK(shipping_country_id)’
* @param string|array $cond “{{table}}.language_code=’en’” OR array(‘language_code’=>’en’)
* @param string $joinType ‘left’
* @return Mage_Eav_Model_Entity_Collection_Abstract
*/
joinField($alias, $table, $field, $bind, $cond=null, $joinType=’inner’)
 
//Beispiel: joinField('ratingcount', 'review/review_aggregate', 'reviews_count', 'entity_pk_value=entity_id', '{{table}}.store_id=1', 'left')

Tags: , , , ,

Leave a Reply

 

Posts of Current Category



Facebook: