Pages

Sunday, August 19, 2012

CakePHP Find Query On Associated Model

CakePHP Find Query On Associated Model :-

CakePHP is very flexibile on data mainupulation. CakePHP offer various methods for openration on Model .
Let's start with how to execute find query on Model or We can say Select query on Table :-
First : i am using following tables and use CakePHP 2.x Version.
User Table :
    CREATE TABLE `test`.`users` (
    `id` INT( 5 ) NOT NULL AUTO_INCREMENT ,
    `username` VARCHAR( 40 ) NOT NULL ,
    `password` VARCHAR( 40 ) NOT NULL ,
    `status` ENUM( 't', 'f' ) NOT NULL DEFAULT 't',
    `created` DATETIME NULL ,
    `modified` DATETIME NULL ,
    PRIMARY KEY ( `id` )
    ) ENGINE = InnoDB;

Post Table :
    CREATE TABLE  `test`.`posts` (
    `id` INT( 5 ) NOT NULL AUTO_INCREMENT ,
    `user_id` INT( 5 ) NOT NULL ,
    `title` VARCHAR( 500 ) NOT NULL ,
    `description` TEXT NOT NULL ,
    `created` DATETIME NOT NULL ,
    `modified` DATETIME NULL ,
    PRIMARY KEY (  `id` )
    ) ENGINE = INNODB;

Post Comment Table :
    CREATE TABLE `test`.`post_comments` (
    `id` INT( 5 ) NOT NULL AUTO_INCREMENT ,
    `user_id` INT( 5 ) NOT NULL ,
    `post_id` INT( 5 ) NOT NULL ,
    `comment` VARCHAR( 500),
    `created` DATETIME NULL ,
    `modified` DATETIME NULL ,
    PRIMARY KEY ( `id` )
    ) ENGINE = InnoDB;

Now My Model Struture are as follows:
    class User extends Model
    {
    public $name = array('User');
    public $belongsTo = array('Group');
    public $hasMany = array('Post','PostComment');
    .......
    }

Executing Query On User Model:
If You wish to execute query on User Model then you must include your model name in $uses array
    class YourController extends AppController
    {
    public $uses = array('User','Post','PostComment');
    .......
    }

Now In your action write following code
    class YourController extends AppController
    {
     public $uses = array('User','Post','PostComment');
     .......
     public function getUserDetail($userId=null)
     {
      if(isset($userId) && $userId!=null)
        {
         $result = $this->User->find('first',array('conditions'=>array('User.id'=>$userId)));
         return $result;
        }
        else
        {
         return false;
        }
     }
    }

Above Action will return User detail according to passed userId. Now if you wish to run a query which retrun all users which is belongs to a specific group like "writer" then use following query
    class YourController extends AppController
    {
     public $uses = array('User','Post','PostComment');
     .......
     public function getGroupUsers($groupName=null)
     {
      if(isset($groupName) && $groupName!=null)
        {
         $result = $this->User->find('first',array('conditions'=>array('Group.name'=>$groupName)));
         return $result;
        }
        else
        {
         return false;
        }
     }
    }

We can directly run find query on associated model which has "belongsTo" relationship. but if you wish to execute query on "hasMany" relationship associated Model then use following query
    class YourController extends AppController
    {
     public $uses = array('User','Post','PostComment');
     .......
     public function getUsersByCommentId($commentId=null)
     {
      if(isset($commentId) && $commentId!=null)
        {
         $result = $this->User->find('first',
             array(
                'contain'=>
                  array('PostComment'=>array('conditions'=>array('PostComment.id'=>$commentId)))
                )));
         return $result;
        }
        else
        {
         return false;
        }
     }
    }

Above query will return User details which comment id is equal to passed commentId
If you have any problem place your comment

1 comments:

  1. PHP is the most widely used server side scripting language for developing websites and web applications. PHP programmers opt for PHP as more and more tools are developed on a regular basis that enhances the programming efficiency.
    i want a business

    ReplyDelete