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

Question

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?

Why not using the query itself, with table aliasing so cake will sort it as standard array? — yossi
– yossi, Commented Feb 20, 2014 at 16:37
Do you mean, using the query() method? if so, what about sql injection. PS. I would need to add to the conditions based on user filtering. — LogixMaster
– LogixMaster, Commented Feb 20, 2014 at 16:39
Yes, query(). generally, cake will sanitize any input. it will be done with cake's objects - that you can call yourself (check the api). As to the error - which column?!?! please supply some more info. like table structure and the exact errors — yossi
– yossi, Commented Feb 20, 2014 at 18:59

Roberto Maldonado · Accepted Answer · 2014-02-20 16:31:53Z

1

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

answered Feb 20, 2014 at 16:31

Roberto Maldonado

1,59614 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LogixMaster Over a year ago

I have just editted my question! could you have a look? thanks

Roberto Maldonado Over a year ago

For the conditions, just put a conditions option in your $options array. Look at the second example in the link.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related