Magento Expert Forum - Improve your Magento experience

Results 1 to 2 of 2

Magento Module Development - Part 9 - Collection and SQL

  1. #1
    Junior Member rocker's Avatar
    Join Date
    Mar 2013
    Posts
    106
    Thanks
    3
    Thanked 11 Times in 9 Posts

    Cool Magento Module Development - Part 9 - Collection and SQL

    In the previous post, we had seen the how to do various sql query in a mysql resource file. In this blog post we will see how to work with magento collection and doing various operations with collections.

    In this section, we will look in all the various sql operations on collections. Collections are also very important when working on admin grids.

    Collection

    Collections in magento is like an array of model object but which many more features. You can think of collections as result of select query in a table, and each row stored in collection as an array of models. Collections are used to do many database queries, basically select query with where, count, and other sql operators.

    Before beginning, there is an important thing to note about collections in magento. There are 2 types of parent classes for magento collection

    1. Mage_Core_Model_Mysql4_Collection_Abstract
    2. Mage_Eav_Model_Entity_Collection_Abstract

    First class, is used in our normal collection tables but the second collection is used in table which has EAV type of database model. Modules like Product, Category, Customer, Order etc use EAV table structure. We will first go through the first type of collection.

    Collection Type 1

    Here is a simple select query with where condition on a collection.

    PHP Code:
    $collection Mage::getModel('aws/aws')->getCollection();
    $collection->addFieldToFilter('file_id',3);
    $collection->addFieldToFilter('list_id',array('in'=>array(1,2,3)));
    $collection->addFieldToFilter('list_id',array('eq'=>'test')); 
    And here is the mysql output

    Code:
    SELECT `main_table`.* FROM `aws` AS `main_table` WHERE (file_id = '3') AND (list_id in (1, 2, 3)) AND (list_id = 'test')
    The complete list of things you can use in the array is ‘in’,’eq’,’ neq’,’like’,’nlike’,’in’,’nin’,’is’,’notnull’,’nul l’,’moreq’,’gt’,’lt’,’gteq’,’lteq’,’finset’

    PHP Code:
    $collection Mage::getModel('aws/aws')->getCollection();
    $collection->addFieldToFilter('file_id',array('from'=>3,'to'=>10));
    echo 
    $collection->getSelect(); 
    Output:

    Code:
    SELECT `main_table`.* FROM `aws` AS `main_table` WHERE (file_id >= 3 and file_id <= 10)
    Using join in collection is similar to what is done in the select object in the previous tutorial. The syntax is same. Here is the magneto function for join

    PHP Code:
    public function join($table$cond$cols='*')
        {
            if (!isset(
    $this->_joinedTables[$table])) {
                
    $this->getSelect()->join(array($table=>$this->getTable($table)), $cond$cols);
                
    $this->_joinedTables[$table] = true;
            }
            return 
    $this;
        } 
    As you see, magento simply calls the getSelect() function on the collection to get the Select Object.
    Important thing to note here is that, you can work on the select object to do all operations as seen in the previous tutorial by using the getSelect() function on the collection. $collection->getSelect() returns the select object. All the operations we did in the previous tutorial are operations on the collection now using getSelect() function.
    So, most of the operations on this collection is done getSelect() function itself.

    Collection Type2

    The collection type2, is used in all model which has EAV tables. Will explain the magento EAV model in detail later, but for now we know the Product, Category, Order, Customer and many more models follow the EAV structure. These collection classes extend Mage_Eav_Model_Entity_Collection_Abstract which intern extends Mage_Eav_Model_Entity_Collection_Abstract, so all the previous function are still valid here. We will look at some special functions here

    PHP Code:
    $collection Mage::getModel('catalog/product')->getCollection();
    $collection->addAttributeToFilter('created_at','2011-02-31');
     
    $collection Mage::getModel('catalog/product')->getCollection();
    // 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('id', array('in' => array(1145152)));
      
    // selecting only those items whose ID is greater than the given value
    $collection->addAttributeToFilter('id', array('gt' => 5));
      
    // select by date range
    $collection->addAttributeToFilter('date_field', array(
        
    'from' => '10 September 2010',
        
    'from' => '21 September 2010',
        
    'date' => true// specifies conversion of comparison values
        
    ));
      
    // Add OR condition:
    $collection->addAttributeToFilter(array(
        array(
            
    'attribute' => 'field_name',
            
    'in'        => array(123),
            ),
        array(
            
    'attribute' => 'date_field',
            
    'from'      => '2010-09-10',
            ),
        )); 
    JoinField in Collection

    Here is magento’s description on the function

    PHP Code:
    /**
         * 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
         */
    public function joinField($alias$table$field$bind$cond=null$joinType='inner'){} 
    So, the above function is used to join tables in eav collection. There are more functions used only for eav collection

    View more threads in the same category:


  2. #2
    New member
    Join Date
    Dec 2016
    Location
    Bangalore
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    We are the leading magento development companies in bangalore see our quality servcies

Similar Threads

  1. Magento Module Development - Part 1 - Get started
    By rocker in forum Programming & Development
    Replies: 3
    Last Post: 12-09-2013, 01:40 PM
  2. Magento Module Development - Part 6 - System.xml Advance and Module
    By rocker in forum Programming & Development
    Replies: 1
    Last Post: 29-05-2013, 12:10 PM
  3. Magento Module Development - Part 8 - Events
    By rocker in forum Programming & Development
    Replies: 0
    Last Post: 29-04-2013, 02:46 AM
  4. Magento Module Development - Part 5 - System.xml and Module
    By rocker in forum Programming & Development
    Replies: 0
    Last Post: 29-04-2013, 02:25 AM
  5. Magento Module Development - Part 4 - Database and SQL
    By rocker in forum Programming & Development
    Replies: 0
    Last Post: 29-04-2013, 02:17 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •