duanbi6522
2014-02-19 16:37 阅读 45
已采纳

cakePHP-SQL-在sql中添加来自不同表的值

I am working on a project which requires me to do some querying which I m having a bit of trouble since my sql is a little rusty. There are 3 tables involved in the query :

donors
donations
donation_methods 

I need to run a valid sql statement logically similar to this with the correct syntax :

SELECT  `tel_no` 
  FROM  `donors` as `dnr` 
        LEFT JOIN  `donations` as `dn` 
                   ON  `dnr`.id =  `dn`.donor_id
        LEFT JOIN  `donation_method` as `dm` 
                   ON `dn`.donation_method_id = `dm`.id
  WHERE `dn`.created`+`dm`.recovery_time >= NOW() // here is the problem

Im not entirely sure on how to do it! Any help? Also if this can be done using cake's method (find,etc), in conjunction with the 'conditions' array, that would be great! Thanks

[EDIT] Recovery time is of INT type, whilst created is of type datetime

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    dourang6423 dourang6423 2014-02-19 16:44

    You should be able to use the DATE_ADD function for this

    WHERE DATE_ADD(dn.created, INTERVAL dn.recovery_time YEAR) >= NOW()
    
    点赞 评论 复制链接分享
  • doushaqing7080 doushaqing7080 2014-02-19 16:43

    change

    WHERE `dn`.created`+`dm`.recovery_time >= NOW()
              ^ missing `    
    

    to

    WHERE `dn`.`created`+`dm`.`recovery_time` >= NOW()
    
    点赞 评论 复制链接分享

相关推荐