Magento Expert Forum - Improve your Magento experience

Results 1 to 4 of 4

Magento Module Development - Part 9 - Collection and SQL

  1. #1
    Junior Member rocker's Avatar
    Join Date
    Mar 2013
    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.


    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();
    And here is the mysql output

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

    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;
    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 Mage::getModel('catalog/product')->getCollection();
    // select all attributes
    // select specific attributes
    // select only those items whose 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:
    'attribute' => 'field_name',
    'in'        => array(123),
    '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
    Thanked 0 Times in 0 Posts


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

  3. #3
    New member
    Join Date
    May 2018
    Thanked 0 Times in 0 Posts


    Find useful Magento extensions and modules customized for your ecommerce online store. Complete Magento solutions for administration, checkout, customer service, online advertisement, product reviews, online store enhancement, discount shopping, shipping and logistics.
    soundcloud to mp3

  4. #4
    Junior Member aaronwallace's Avatar
    Join Date
    Nov 2013
    Thanked 9 Times in 9 Posts

    Default Get Flat 50% OFF On Premium Magento 1 & 2 Extensions

    Special EID Sale Offers And Discounts On All M1 and M2 Extensions.

    Seasonal Sale Is On!
    Get Flat 50%
    OFF On Premium Magento 1 & 2 Extensions

    Use Coupon Code: OFF50

    On this special
    occasion of EID SoftProdigy offers a great discount on all top quality premium Magento extensions which make your eCommerce store more reliable and help to engage more customers.

    Don’t forget to check these extensions:

    Magento 2 most selling extensions:

    Daily Deals / Multiple-deals Magento 2 Extension

    Launch great deals daily and schedule multiple promotions to increase customer base using Daily Deal extension for Magento2. This extension stimulates customers to buy more products before the deal expires by offering discounts, thereby saving their money. Multiple deals are supported, with featured deals easily highlighted.

    Blue Dart Shipping Extension Magento 2.0 extension

    Blue Dart Shipment Integration extension for Magento2 is the smartest way to let your customers check the availability of Cash on Delivery option for a particular region or the Total Price of the order. A PDF, including product and user details, is sent to
    salesdepartment for every sale.

    Request a Quote magento2 extension

    Request a Quote Extension for Magento2 allows online shoppers to interact with the store owners and suggest
    a desired quote for products they wish to purchase. This extension supports various product types.

    One-Step Checkout magento2 extension

    One-Page Checkout Magento2 Extension offers online shoppers a 40% faster checkout with dynamic loading and other features that make it a must-have for every
    eRetail store. It helps reduce the cart abandonment rate while increasing your sales 2x while enhancing user experience.

    Gifting Reminders magento2 extension

    Never miss a single important event with Gifting Reminders and Cross Sales Magento2 Extension. This allows online buyers to set reminders for special events like birthdays and anniversaries and notify them as the date approaches.

    Get Deals On All Magento Extensions And Woocommerce Plugins!

Similar Threads

  1. Magento Module Development - Part 4 - Database and SQL
    By rocker in forum Programming & Development
    Replies: 2
    Last Post: Yesterday, 11:38 AM
  2. Magento Module Development - Part 1 - Get started
    By rocker in forum Programming & Development
    Replies: 3
    Last Post: 12-09-2013, 01:40 PM
  3. 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
  4. Magento Module Development - Part 8 - Events
    By rocker in forum Programming & Development
    Replies: 0
    Last Post: 29-04-2013, 02:46 AM
  5. 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

Tags for this Thread


Posting Permissions

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