Magento Expert Forum - Improve your Magento experience

Results 1 to 5 of 5

Magento Module Development - Part 9 - Collection and SQL

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

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

    Default

    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
    New member
    Join Date
    Jun 2018
    Location
    Amsterdam
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Buy british, usa, european, canadian real passport, driver licence, id cards for sale

    We are producers and quality sellers of real and fake documents. We process and produce documents that you can use to travel and work in any part of the world. Our real documents are valid and registered in the required government database system. As for our fake documents they look 100% real and can also be use for travel but that is done at the clients risk. We advice clients who want to use thei documents for travel to always go in for the real documents. Visit our renewed website:

    w w w. buyrealpassport.cc

    Registered passport of all countries, visas, biometric passport, degrees, drivers license, ID cards, training certificates, M GCSE, A-levels, Social security card, Canada Cards, United States Cards, Student Cards, International Cards, Private Cards, Adoption Certificates, Baptism Certificates, Birth Certificates, Birth Certificates, Divorce Certificates, Marriage Certificates, Custom Certificates High School Diplomas, G.E.D. Diplomas, Home School Diplomas, College Degrees, University Degrees, Trade Skill Certificates, Validate SSN Number US green cards, Counterfeit dollars/euro, Spy Products, Voice Changers Listening Devices, Invisible Ink, DMV Record Inquiry, buy registered and unregistered ID cards,buy registered and unregistered drivers license worldwide, buy registered and unregistered USA(United States) passports, buy registered and unregistered Australian passports, buy registered and unregistered Belgium passports, purchase registered and unregistered Brazilian(Brazil) passports, buy registered and unregistered Canadian(Canada) passports, buy registered and unregistered Finnish(Finland) passports, buy registered and unregistered French(France) passports, buy registered and unregistered German(Germany) passports, get registered and unregistered Dutch(Netherlands/Holland) passports, buy registered and unregistered Israel passports, buy registered and unregistered UK(United Kingdom) passports, buy registered and unregistered Spanish(Spain) passports, buy registered and unregistered Mexican(Mexico) passports, buy registered and unregistered South African passports, order registered and unregistered Australian driver licenses, buy registered and unregistered Canadian driver licenses, buy registered and unregistered Dutch(Netherlands/Holland) driving licenses, buy registered and unregistered German(Germany) driving licenses, where to get registered and unregistered UK(United Kingdom) driving licenses, sale registered and unregistered Diplomatic passports, GMAT, MCAT, and LSAT Examination Certificates , Novelty Birth, Marriage, Novelty Passports and New Identity Packages , Replicated, False Degrees/Diplomas from most post-secondary institutions from around the world. We are the world best producers documents. Hit us and get all your documents problems solved today. We are flexible and very easy to deal with, we accept nearly all forms of payments and all our clients can use any that best suites them. Visit our renewed website:

    w w w. buyrealpassport.cc

    ---
    Keywords:

    We produce both Real and Real documents. Buy Registered and unregistered USA(United States) passports, Buy Registered and unregistered Australian passports, Buy Registered and unregistered Belgium passports, Buy Registered and unregistered Brazilian(Brazil) passports, Buy Registered and unregistered Canadian(Canada) passports, Buy Registered and unregistered Finnish(Finland) passports, Buy Registered and unregistered French(France) passports, Buy Registered and unregistered German(Germany) passports, Buy Registered and unregistered Dutch(Netherland/Holland) passports, Buy Registered and unregistered Israel passports, Buy Registered and unregistered UK(United Kingdom) passports, Buy Registered and unregistered Spanish(Spain) passports, Buy Registered and unregistered Mexican(Mexico) passports, Buy Registered and unregistered South African passports, Buy Registered and unregistered Australian driver licenses, Buy Registered and unregistered Canadian driver licenses, Buy Registered and unregistered French(France) driver licenses, Buy Registered and unregistered Dutch(Netherland/Holland) driving licenses, Buy Registered and unregistered German(Germany) driving licenses, Buy Registered and unregistered UK(United Kingdom) driving licenses, Buy Registered and unregistered Diplomatic passports, Buy Registered and unregistered USA(United States) passports, Buy Registered and unregistered Australian passports, Buy Registered and unregistered Belgium passports, Buy Registered and unregistered Brazilian(Brazil) passports, Buy Registered and unregistered Canadian(Canada) passports, Buy Registered and unregistered Finnish(Finland) passports, Buy Registered and unregistered French(France) passports, Buy Registered and unregistered German(Germany) passports, Buy Registered and unregistered Dutch(Netherland/Holland) passports, Buy Registered and unregistered Israel passports, Buy Registered and unregistered UK(United Kingdom) passports, Buy Registered and unregistered Spanish(Spain) passports, Buy Registered and unregistered Mexican(Mexico) passports, Buy Registered and unregistered South African passports, Buy Registered and unregistered Australian driver licenses, Buy Registered and unregistered Canadian driver licenses, Buy Registered and unregistered French(France) driver licenses, Buy Registered and unregistered Dutch(Netherland/Holland) driving licenses, Buy Registered and unregistered German(Germany) driving licenses,
    Buy Registered and unregistered UK(United Kingdom) driving licenses, Buy Registered and unregistered Diplomatic passports, Registered and unregistered Camouflage passports, Registered and unregistered passport Duplicates, Registered and unregistered USA(united States) passports for sale, Registered and unregistered Australian passports for sell, Registered and unregistered Belgium passports for sell, Registered and unregistered Brazilian(Brazil) passports for sell, Id card and Driver's License, buy Real passports, get Real passports online, real passports for sale, Real schengen visas for sale, Real German passports for sale, purchase Real passports online, where to buy Real passport online, Real Canadian passport for sale, Real, passport, id cards, uk, sell, online, canadian, british, sale, novelty, conterfeit, bogus, american, united, states, usa, us, italian, malaysian, australian, documents, idetity, idetification, driver, license, licence, driving, residence, permit, SSN Real passport id, free Real passport, identity theft, Real, novelty, camoflauge, passport, anonymous, private, safe, travel, anti terrorism, international, offshore, banking, id, driver, drivers, license, instant, online, for sale, REAL BRITISH PASSPORT, REAL CANADIAN PASSPORT, REAL FRENCH PASSPORT, REAL AMERICAN PASSPORT, REAL RUSSIAN PASSPORT, REAL JAPANESES PASSPORT, REAL CHINESE PASSPORT, AND REAL PASSPORT FOR COUNTRIES IN THE EUROPEAN UNION, REAL DRIVERS LICENSE,I.D CARDS,BIRTH CERTIFICATES,DIPLOMATS,MARRIAGE CERTIFICATES,AND VISAS, REGISTERED AND UNREGISTERED BRITISH PASSPORT, REGISTERED AND UNREGISTERED CANADIAN PASSPORT, REGISTERED AND UNREGISTERED FRENCH PASSPORT, REGISTERED AND UNREGISTERED AMERICAN PASSPORT, REGISTERED AND UNREGISTERED RUSSIAN PASSPORT, REGISTERED AND UNREGISTERED JAPANESES PASSPORT, REGISTERED AND UNREGISTERED CHINESE PASSPORT,REGISTERED AND UNREGISTERED PASSPORT FOR COUNTRIES IN THE EUROPEAN UNION.

  5. #5
    Junior Member aaronwallace's Avatar
    Join Date
    Nov 2013
    Posts
    416
    Thanks
    4
    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 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
  •