Magento Expert Forum - Improve your Magento experience

Results 1 to 5 of 5

Magento Module Development - Part 4 - Database 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 4 - Database and SQL

    First let’s look at model and collection architecture in magento. If you look at a magento model class it extends Mage_Core_Model_Abstract which intern extends Varien_Object.The class Mage_Core_Model_Abstract has function like getId(), load(), save(), delete() etc which we have seen in Part 3

    SQL and Mysql4 Resource Files

    Mysql4 resource files, as I mentioned earlier are used to perform database queries on tables. Below will list how we do sql operations inside resource files.

    SELECT QUERY

    Suppose we have the file Excellence_Test_Model_Mysql4_Test resource file.

    PHP Code:
    <?php
    class Excellence_Test_Model_Mysql4_Test extends Mage_Core_Model_Mysql4_Abstract
    {
        public function 
    _construct()
        {   
            
    $this->_init('test/test''test_id');
        }
        public function 
    loadByField($field,$value){
            
    $table $this->getMainTable();
            
    $where $this->_getReadAdapter()->quoteInto("$field = ?"$value);
            
    $select $this->_getReadAdapter()->select()->from($table,array('test_id'))->where($where);
            
    $id $this->_getReadAdapter()->fetchOne($sql);
            return 
    $id;
        }
    }
    And here is the model file Excellence_Test_Model_Test

    PHP Code:
    <?php
    class Excellence_Test_Model_Test extends Mage_Core_Model_Abstract
    {
        public function 
    _construct()
        {
            
    parent::_construct();
            
    $this->_init('test/test');
        }
        public function 
    loadByField($field,$value){
            
    $id $this->getResource()->loadByField($field,$value);
            
    $this->load($id);
        }
    }
    In this file there are many new functions which have been used. Let’s take them one by one.
    In our model file, we have used this function

    PHP Code:
    $id $this->getResource()->loadByField($field,$value); 
    $this->getResource() function returns the resource model’s object. So we are simply calling the loadyByField($field,$value) function in the resource model.

    getTable() function

    Now in our Mysql4 file, in the loadByField() function we have used $table = $this->getMainTable(); .This function returns the table name of the current model, i.e the one defined in the class’s constructor. If you want to get table name of another sql table in magento, we need to the getTable() call e.g

    PHP Code:
    $table $this->getTable(‘newsletter/subscribe’); 
    _getReadAdapter() function

    Next we have: $where = $this->_getReadAdapter()->quoteInto(“$field = ?”, $value);

    Here the first function too see is the $this->_getReadAdapter() function. This function return an object which has function to perform database query, like fetch(), fetchAll(), query(), etc.

    Next, in read adapter, we can perform operations like:


    • fetchAll() : returns entire database table in multiple dimensional array as per the select query. e.g if we are executing an sql like “select * from table_name”, we would use the fetchAll() function to get all the values.
    • fetchRow(): returns a single row of the database table as per the select query. e.g if we are executing sql like “select * from table where id = ’1′” would use fetchRow() to get the single row.
    • fetchCol(): returns single column of a database table as per the select query. e.g if query is a select name from table” we would use fetchCol() function.
    • fetchOne(): return since value, i.e single row and single column. e.g if sql query “select count(*) from table” then we would use fetchOne()


    The read adapter has many other functions as well, but above are the important functions used.

    _quoteInfo() function


    Another function we have used is quoteInto(): This function is used to create our where conditions. In this the actual value is substituted into the question mark we have written. For e.g $this->_getReadAdapter()->quoteInto(“user_id = ?”, 3); translates to “user_id = 3″. Another frequently used variation of quoteInfo() is

    PHP Code:
    $where $this->_getReadAdapter()->quoteInto("$field IN(?)", array(1,2,3)); 
    this translates to “field in (1,2,3)”

    Getting deeper into the quoteInto() function: If we have multiple where conditions we can use

    PHP Code:
    $where $this->_getReadAdapter()->quoteInto("$field = ? AND "$value).$this->_getReadAdapter()->quoteInto("$field1 = ? OR "$value1).$this->_getReadAdapter()->quoteInto("$field2 = ?"$value2); 
    So this way, we can have multiple where conditions.

    Continuing on the loadByField function in our resource model, next we have

    $select = $this->_getReadAdapter()->select()->from($table,array(‘test_id’))->where($where);

    Here we have created the select query using the select(), and on that called the from() function.

    from() function

    The from() function takes many different parameters, if you want to fire a simple select * query, you need to only pass the table name like from($table).

    But right now, I wanted to select just a single column that is test_id, so I passed an array with column name to select. i.e array(‘test_id’). If we want to select multiple column, we need to array(‘test_id’,’col1’,’col2’). This will create a sql like “select test_id, col1, col2 from $table”. But support we want a sql query like “select test_id as id,col1 as column1 from $table” , i would call

    PHP Code:
    from($table,array('id'=>'test_id','column1'=>'col1')); 
    Note:

    If you want to see the exact sql that is generated by your function, you can any time do this

    PHP Code:
    <?php echo $select?>
    Again, back to the loadByField() function. The last line

    PHP Code:
    <?php
        $id 
    $this->_getReadAdapter()->fetchOne($sql);
    ?>
    This is already explained, above.

    Select Object

    The select() function we used above return a select object which provides us with many more functions to perform all operations on a sql query.

    The class for the select object is Varien_Db_Select, where you can look into more details for all functions. Let look at a sample php code below.

    PHP Code:
    public function getAll(){
            
    $table $this->getMainTable();
            
    $where $this->_getReadAdapter()->quoteInto("id = ?"123);
    $select $this->_getReadAdapter()->select()->from($table)->columns(array('test_id'))->where($where)->limit(10,5)->order('created_time')->group('list_id')->having('list_id > ?',10);    
    echo 
    $select;
        } 
    As we can see all function in the select object, return the select object itself. Due to this we are able to chain the functions.

    In this example, I have used almost all the sql select parameters you might require expect joins, which will be covered later.

    Let’s take each function one at a time.

    • select() : used to initialize the sql query. This returns the Select object, which is the object on which we do all our operations.
    • from(): table name on which select operation is done. we have seen this in detail above.
    • columns() : here we can specify exactly which columns to select in the query
    • where() : used to give the where condition
    • limit(): takes to parameters. First are how many rows to return and second is offset from where to start. So in current case, it means return 10rows, leaving first 5rows. this uses the LIMIT 10 OFFSET 5 syntax in sql query, rather than LIMIT 10,5
    • order(): you can set how you want to order the result set. Default is ascending order. But if you want to mentioned descending order you do order(array(‘created_time Zend_Db_Select::SQL_DESC))
    • group(): put group by clause in your select query in the specified field.
    • Having(): put having condition for group by clause


    JOINS

    Let look at sample function

    PHP Code:
    public function joinUs()
    {
            
    $table $this->getMainTable();
            
    $table2 $this->getTable('customer/entity');
            
    $cond $this->_getReadAdapter()->quoteInto('t1.id = t2.customer_id','');
            
    $where $this->_getReadAdapter()->quoteInto('t1.list_id = "?"',123);
            
    $select $this->_getReadAdapter()->select()->from(array('t1'=>$table))->join(array('t2'=>$table2), $cond)->where($where);
            echo 
    $select."<br/>";
            
    $select $this->_getReadAdapter()->select()->from(array('t1'=>$table))->joinLeft(array('t2'=>$table2), $cond)->where($where);
            echo 
    $select."<br/>";
            
    $select $this->_getReadAdapter()->select()->from(array('t1'=>$table))->joinRight(array('t2'=>$table2), $cond)->where($where);
            echo 
    $select."<br/>";
            echo 
    $select."<br/>";

    This is the output I got in the my pc

    Code:
    SELECT `t1`.*, `t2`.* FROM `aws` AS `t1` INNER JOIN `customer_entity` AS `t2` ON t1.id = t2.customer_id WHERE (t1.list_id = "123")
    SELECT `t1`.*, `t2`.* FROM `aws` AS `t1` LEFT JOIN `customer_entity` AS `t2` ON t1.id = t2.customer_id WHERE (t1.list_id = "123")
    SELECT `t1`.*, `t2`.* FROM `aws` AS `t1` RIGHT JOIN `customer_entity` AS `t2` ON t1.id = t2.customer_id WHERE (t1.list_id = "123")
    So, here you can see clearly how to use joins. I don’t think much explanation is required, the function call are self-explanatory. We can also use joinCross, joinFull, joinNatural as well, if you require it in any query. Please look at Varien_Db_Select class for details.

    COUNT,MAX

    Again, lets take another sample code

    PHP Code:
    public function countUs(){
            
    $table $this->getMainTable();
            
    $where $this->_getReadAdapter()->quoteInto("id = ?"123);
            
    $select $this->_getReadAdapter()->select()->from($table)->reset('columns')->columns(new Zend_Db_Expr('count(*)'));
            echo 
    $select.'';
            
    $select $this->_getReadAdapter()->select()->from($table)->reset('columns')->columns(new Zend_Db_Expr('max(list_id)'));
            echo 
    $select.'';
        } 
    This is the output.

    Code:
    SELECT count(*) FROM `aws`
    SELECT max("list_id") FROM `aws`
    As you can see here, using Expr classes, we do mysql averaging functions.

    query() function

    In the end, if you have a very complex sql query, you want to execute you can simple use the query function.

    $sql = ‘…complex sql…’;

    $this->_getReadAdapter()->query($sql);

    UPDATE, DELETE Query

    PHP Code:
    public function updateUs(){
            
    $table $this->getMainTable();
            
    $where $this->_getWriteAdapter()->quoteInto('id = ?'1);
    $query $this->_getWriteAdapter()->update($table, array('product_id'=>2,'file_id'=>3),$where);

    Here you can see how to execute the update query.

    Code:
    UPDATE `aws` SET `product_id` = ?, `file_id` = ? WHERE (id = 1)
    For delete query, it’s very simple

    PHP Code:
    $this->_getWriteAdapter()->delete($table,$where)->limit(1); 
    Important Note: Its considered good practice in magento, to execute these sql query in the resource files only. You can get the read adaptor object in model, phtml or any other files and do the above operations in any file. But as good practices, always try to do the query in resource/mysql4 files only.

    View more threads in the same category:


  2. #2
    New member
    Join Date
    Jul 2018
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Thanks for sharing this post with us, i was giving edubirdie.com reviews and while giving it, i have found your post. Your post is really helpful for me. It is very well written and briefly explained. I would love to hear more about this topic.

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

    Default

    Your post is full of knowledge. I have got alot of information from your post. Now i can wrote very well and also best dissertations explained about magento module development. I was looking for from very long time and i have found many. But i can only get full information from your post only. Thanks alot for sharing about magento module.

  4. #4
    New member
    Join Date
    Jan 2021
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Reader

    Do you want to open multiples website with one click than i will suggest you a best bulk url opener website link which makes your work too easy and save your time. I am 100% sure that you will like this Multiple website (Urls) opener. This is very useful and time saver for those people who are doing SEO work.

  5. #5
    Junior Member
    Join Date
    Sep 2018
    Location
    Oman, Muscat
    Posts
    2,084
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Default

    Every module name in Magento 2 comprises of two sections – the seller and the module itself. Adjusting the data set by direct SQL questions won't work.

Similar Threads

  1. Magento Module Development - Part 1 - Get started
    By rocker in forum Programming & Development
    Replies: 5
    Last Post: 07-08-2021, 06:11 AM
  2. Magento Module Development - Part 9 - Collection and SQL
    By rocker in forum Programming & Development
    Replies: 9
    Last Post: 21-08-2018, 01:48 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
  •