CODEWITHSUNDEEP
mysqlcakephp
LogixMaster
LogixMaster

Reputation: 586

cakePHP- sql syntax based query to cake's array based query

I need to execute this query from one of my controllers: 

SELECT  `tel_no` 
        FROM  `donors` AS  `dnr` 
        LEFT JOIN  `donations` AS  `dn` ON  `dnr`.id =  `dn`.donor_id
        LEFT JOIN  `donation_methods` AS  `dm` ON  `dn`.donation_method_id =  `dm`.id
        WHERE  NOW() >= DATE_ADD(dn.created, INTERVAL dm.recovery_time DAY)

As you might notice, 3models are involved in this query. I am struggling with how to generate an array based query with cake for the above. 

 $elligibleDonors = $this->Donor->find('all', array(
        'fields' => array('Donor.tel_no', 'Donor.email'),
        'conditions' => array('NOW() >= Donation.created + Donation_method.recovery_time'),
        'recursive' => 2
        ));
         $this->set('elligibleDonors', $elligibleDonors);

I tried this but a error states that column does not exis This is ofcourse a syntax error, one which I cannot figure out!

[EDIT]

relation ships are

Donor hasMany Donation Donation belongsTo Donor Donation belongsTo DonationMethod DonationMethod hasMany Donation

$joins = array(
           array('table'=>'donations', 
                 'alias' => 'Donation',
                 'type'=>'left',
                 'conditions'=> array(
                 'Donation.donor_id = Donor.id'
           )),
           array('table'=>'donation_methods', 
                 'alias' => 'DonationMethod',
                 'type'=>'left',
                 'conditions'=> array(
                 'DonationMethod.id = Donation.donation_method_id'
           ))
         );

I ve found that this is what I want, but where do I put the 'WHERE' clause condition within this code?

Upvotes: 0

Views: 87

Answers (1)

Roberto Maldonado
Roberto Maldonado

Reputation: 1595

Just use a join option in Cake, if default find doesn't suit your needs.

http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#joining-tables

Upvotes: 1

Related Questions