Cakephp Model Query

Cakephp Model Fetch query

Retrieve data from database
$this->Resume->find('first',array('contain'=>array('ResumeSkill'=>array('Skill'=>array('name'), 'conditions'=>array('ResumeSkill.skill_id !='=>''))), 'conditions'=>array('Resume.user_id'=>$user_id),'order'=>'Resume.modified DESC'));

$this->Layout->query("SELECT *,if(sort IS NULL,0,sort) as sort1  FROM `layouts` AS Layout WHERE `user_id` LIKE '".$user_id."' && Layout.active=1 ORDER BY (`sort1`>0) DESC,(`sort1`) ASC");

$this->Invoice->find('first',array('fields'=>array('*','curdate() as curdate','DATE_FORMAT(plan_start_date,"%Y-%m-%d") as plan_start_date1'), 'conditions'=>array('Invoice.id'=>$last_invoice_id,'Invoice.user_id'=>$this->Auth->user('id') ,'Invoice.payment_flag'=>1,'Invoice.plan_end_date > now()')));

$this->Agency->findBySql("SELECT * FROM tAgencies AS Agency LEFT JOIN tApplications AS Application ON Agency.AgId=Application.AgId LEFT JOIN tApplicationTechRequests AS ApplicationTechrequest ON ApplicationTechrequest.ApId=Application.ApId WHERE $cond ORDER BY Agency.AgName LIMIT 100 ");

How to count duplicate values in cakephp?

$result = $this->Answer->Vote->find('all', array(
                'fields' => array('Vote.answer_id', 'count(*) as TotalVotes'),
                'group' => array('Vote.answer_id HAVING COUNT(*) > 1')));

Update query

An example of updating a single field would be:

if ($this->User->id) {
    $this->User->saveField('mobileNo', $mobileNo);
}
$this->User->updateAll(array('mobileNo' => "'$MobileNo'"),array('email' => $email));

$travel_details['Singleroute']=array(
                'userprofile_id'=>$upid,
                'day'=>$data['day'],
                'dayNumeric'=>$daynum,
                'morning_route'=>$morning_route,
                'morning_time'=>$morning_time,
                'evening_route'=>$evening_route,
                'evening_time'=>$evening_time,
                'traveller' =>$traveller
            );

$this->Singleroute->updateAll($travel_details,array('Singleroute.userprofile_id'=>$upid,'Singleroute.daynumeric'=>$daynum,'Singleroute.day'=>$data['day']));

$this->User->query("UPDATE users set mobileNo = '".$mobileNo."' where email = '".$email."' ");

$this->Invoice->query("UPDATE invoices set plan_start_date=DATE_ADD('".$accountplans[0]['accountplans']['plan_end']."',INTERVAL 1 DAY),plan_end_date=DATE_ADD('".$accountplans[0]['accountplans']['plan_end']."',INTERVAL $plan_interval DAY),payment_date=NOW(), post_value='".$post_value."',payment_flag='1' WHERE id='".$invoice_id."'");

Deleting the User

$this->User->query("DELETE FROM sections WHERE  user_id='".$id."'");

Insert the data

$this->User->query("INSERT INTO `accountplans` (`user_id`, `plan_id`, `last_invoice_id`, `plan_start`, `plan_end`, `recurring_flag`, `payment_gateway`, `payment_date`, `cancel_plan_date`,`created`,`modified`) VALUES ('".$id."', '1', NULL, NULL, NULL, '0', NULL, NULL, NULL,NOW(),NOW());");

$this->User->create();
$this->User->save($this->request->data);

Save in association

 $user = $this->User->save($this->request->data);

            // If the user was saved, Now we add this information to the data
            // and save the Profile.

            if (!empty($user)) {
                $this->request->data['Two']['two_one_id'] = $this->User->id;

                // Because our User hasOne Profile, we can access
                // the Profile model through the User model:
                $this->User->Profile->save($this->request->data);
            }
OR
$this->Message->saveAll($this->data);