Magento Expert Forum - Improve your Magento experience

Results 1 to 10 of 10

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
    Junior Member aaronwallace's Avatar
    Join Date
    Nov 2013
    Posts
    438
    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!

  5. #5

  6. #6
    New member
    Join Date
    Aug 2018
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

  7. #7
    New member
    Join Date
    Aug 2018
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    recipiemania

    recipie mania

    ritik77

    ritik77 livejournal

    ritik77 livejournal

    ritik77 livejournal

    masala omelet recipes

    masala omelet recipes

    doodlekit

    banana hazelnut pain perdu duet

    breakfast tacos

    masala omelet recipes

    egg-kurma-recipie

    raksha-bandhan-quotes-and-whatsapp-status

    tortellini-and-ham

    status34

    status34

    technology778

    recipiemania

    goa-egg-curry-recipe

    https://aajtaak5.wixsite.com/recipie...bengali-recipe

    recipieschool

    No-Hassle-Bread-Making

    Easy-and-Heart-Healthy-Salads

    omelettecurryrecipe

    ricky4you7

    engineeringjobmarketinaustralia

    stuffed-egg-recipe

    sadwichrecipie

    recipieschool.yolasite

    recipieschool.yolasite

    ritikchopr4fc1

    recipie12

    recipie21

    recipie12

    recipie21

    recipie12

    recipie21

    recipie12

    blog/how-to-make-aanda-burji

    recipie21

    egg-roast-biryani-recipe

    will soon become 100

    masala omelet recipes

    extravagant coffee recipes

    aricky77

    tomato egg curry

    best inverter ac

    recipie mania

    goa egg curry recipe

    simple way bengali recipe

    recipie school

    No Hassle Bread Making

    Easy and Heart Healthy Salads

    bikestatus

    make money from facebook

    technologyishere

    omelettecurryrecipe

    ricky4you7

    engineeringjobmarketinaustralia

    stuffed egg recipe

    sadwichrecipie

    recipieschool

    ritikchopr4fc1

    omelet curry

    egg kurma recipie

    stuffed omelette

    bread omelet

    ahmedabad street food

    status34

    technologyishere

    recipiemania

    recipie mania

    ritik77

    ritik77 livejournal

    ritik77 livejournal

    ritik77 livejournal

    masala omelet recipes

    masala omelet recipes

    doodlekit

    banana hazelnut pain perdu duet

    breakfast tacos

    masala omelet recipes

    egg-kurma-recipie

    raksha-bandhan-quotes-and-whatsapp-status

    tortellini-and-ham

    status34

    status34

    technology778

    recipiemania

    goa-egg-curry-recipe

    https://aajtaak5.wixsite.com/recipie...bengali-recipe

    recipieschool

    No-Hassle-Bread-Making

    Easy-and-Heart-Healthy-Salads

    omelettecurryrecipe

    ricky4you7

    engineeringjobmarketinaustralia

    stuffed-egg-recipe

    sadwichrecipie

    recipieschool.yolasite

    recipieschool.yolasite

    ritikchopr4fc1

    will soon become 100

    masala omelet recipes

    extravagant coffee recipes

    aricky77

    tomato egg curry

    best inverter ac

    recipie mania

    goa egg curry recipe

    simple way bengali recipe

    recipie school

    No Hassle Bread Making

    Easy and Heart Healthy Salads

    bikestatus

    make money from facebook

    technologyishere

    omelettecurryrecipe

    ricky4you7

    engineeringjobmarketinaustralia

    stuffed egg recipe

    sadwichrecipie

    recipieschool

    ritikchopr4fc1

    omelet curry

    egg kurma recipie

    stuffed omelette

    bread omelet

    ahmedabad street food

    status34

    technologyishere

    recipiemania

    recipie mania

    ritik77

    ritik77 livejournal

    ritik77 livejournal

    ritik77 livejournal

    masala omelet recipes

    masala omelet recipes

    doodlekit

    banana hazelnut pain perdu duet

    breakfast tacos

    masala omelet recipes

    egg-kurma-recipie

    raksha-bandhan-quotes-and-whatsapp-status

    tortellini-and-ham

    status34

    status34

    technology778

    recipiemania

    goa-egg-curry-recipe

    https://aajtaak5.wixsite.com/recipie...bengali-recipe

    recipieschool

    No-Hassle-Bread-Making

    Easy-and-Heart-Healthy-Salads

    omelettecurryrecipe

    ricky4you7

    engineeringjobmarketinaustralia

    stuffed-egg-recipe

    sadwichrecipie

    recipieschool.yolasite

    recipieschool.yolasite

    ritikchopr4fc1

  8. #8
    New member
    Join Date
    Aug 2018
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    http://recipiemania.zohosites.com/

    http://recipie.zohosites.com/

    https://ritik77.livejournal.com/352.html

    https://ritik77.livejournal.com/707.html

    https://ritik77.livejournal.com/

    https://recipie1234.blogspot.com/

    https://recipie1234.blogspot.com/201...t-recipes.html

    http://recipie1234.blogspot.com/2018...-formulas.html

    http://ritikchopra.doodlekit.com/

    http://recipie1234.blogspot.com/2018...erdu-duet.html

    https://steemit.com/recipie/@ritik77/breakfast-tacos

    https://steemit.com/masala/@ritik77/...omelet-recipes

    https://steemit.com/omlette/@ritik77/egg-kurma-recipie

    https://steemit.com/raksha/@ritik77/...hatsapp-status

    https://steemit.com/recipie/@ritik77/tortellini-and-ham

    https://status34.blogspot.com/

    http://technologyishere.emyspot.com/

    https://issuu.com/technology778

    https://aajtaak5.wixsite.com/recipiemania

    https://aajtaak5.wixsite.com/recipie...g-curry-recipe

    https://aajtaak5.wixsite.com/recipie...bengali-recipe

    https://aajtaak5.wixsite.com/recipieschool

    https://aajtaak5.wixsite.com/recipie...e-Bread-Making

    https://aajtaak5.wixsite.com/recipie...Healthy-Salads

    https://omelettecurryrecipe.cabanova.com/

    http://ricky4you7.strikingly.com/

    http://engineeringjobmarketinaustralia.strikingly.com/

    http://recipieschool.strikingly.com/...fed-egg-recipe

    http://recipiemania.strikingly.com/blog/sadwichrecipie

    https://recipieschool.yolasite.com/

    https://recipieschool.yolasite.com/

    https://www.behance.net/ritikchopr4fc1

    https://steemit.com/important/@steem...oon-become-100

    https://steemit.com/masala/@ritik77/...omelet-recipes

    https://steemit.com/coffee/@dmilash/...730t130544843z

    https://aricky77.tumblr.com/

    https://aricky77.tumblr.com/post/176...mato-egg-curry

    https://aricky77.tumblr.com/post/176...st-inverter-ac

    https://aajtaak5.wixsite.com/recipiemania

    https://aajtaak5.wixsite.com/recipie...g-curry-recipe

    https://aajtaak5.wixsite.com/recipie...bengali-recipe

    https://aajtaak5.wixsite.com/recipieschool

    https://aajtaak5.wixsite.com/recipie...e-Bread-Making

    https://aajtaak5.wixsite.com/recipie...Healthy-Salads

    https://bikestatus.cabanova.com/

    https://makemoneyfromfacebook.cabanova.com/

    https://technologyishere.cabanova.com/

    https://omelettecurryrecipe.cabanova.com/

    http://ricky4you7.strikingly.com/

    http://engineeringjobmarketinaustralia.strikingly.com/

    http://recipieschool.strikingly.com/...fed-egg-recipe

    http://recipiemania.strikingly.com/blog/sadwichrecipie

    https://recipieschool.yolasite.com/

    https://recipieschool.yolasite.com/

    https://www.behance.net/ritikchopr4fc1



    Url Shortner

    https://goo.gl/DvTgAM

    https://goo.gl/5xD7e2

    https://goo.gl/ScWihm

    https://goo.gl/qe8aVt

    https://goo.gl/K1aD9X

    https://goo.gl/CPPoFd

    https://goo.gl/e435fe

    https://goo.gl/eRmWzP

    https://goo.gl/UBBm8u

    https://goo.gl/sKbP4R

    https://goo.gl/zJLkau

    https://goo.gl/8XKSe3

    https://goo.gl/SV98AC

    https://goo.gl/pMWxUk

    https://goo.gl/2Db2kJ

  9. #9

  10. #10
    New member
    Join Date
    Aug 2018
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Appreciation for really being thoughtful and also for deciding on certain marvelous guides most people really want to be aware of.
    Angularjs Online Training
    AngularJs Training in Bangalore

Similar Threads

  1. Magento Module Development - Part 4 - Database and SQL
    By rocker in forum Programming & Development
    Replies: 2
    Last Post: 14-08-2018, 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

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
  •