php - 我如何从 cake php(cake 2) 中的两个表中选择记录

Question

我是 cake php 的新手。我有两张 table

1. ce_landing
2. ce_stat

结构是

ce_stat

id       keyword       click
1        keyword1       24
2        keyword2       2
3        keyword3       6

ce_landing

id       page_keyword
1        keyword1,check,keyword3
2        keyword2,usa,alphanumeric

我想获取 ce_landing.page_keyword 中存在于 ce_stat.keyword 中的所有记录。我在我的蛋糕 php 模型中使用它

 public $hasMany = array(
        'Stat' => array(
            'className' => 'Stat',
            'foreignKey' => 'keywor',
            'dependent' => false,
            'conditions' => array('Stat.keyword LIKE' => '%Landing.page_keywords%'),
            'group' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        )
        );

但这是生成的 sql 查询，如

SQL Query: SELECT Stat.id, Stat.customer_id, Stat.account, Stat.campaign_id, Stat.campaign, Stat.keyword, Stat.ad_grp_id, Stat.ad_grp, Stat.impressions, Stat.clicks, Stat.cost, Stat.qualityScore, Stat.keywordState, Stat.date_from, Stat.date_to, Stat.user_id, Stat.created, Stat.modified, Stat.keywor FROM EB_adwords.ce_stats AS Stat WHERE Stat.keyword LIKE '%Landing.page_keywords%' AND Stat.keyword = ('559f479a-82ac-4e3d-8c24-19b5c0a8011f')

因此它返回空数据，因为 AND Stat.keyword =('559f479a-82ac-4e3d-8c24-19b5c0a8011f') 条件。

更新

我想从 ce_landing 获取所有记录，根据当前关键字获得总点击次数。即 ce_landing 中的记录 1。我会得到结果

id       page_keyword                     clicks
    1        keyword1,check,keyword3        30
    2        keyword2,usa,alphanumeric       2

Answer 1

您需要一个等效于以下的 SQL 语句:

SELECT ce_landing.id, ce_landing.page_keyword, SUM(ce_stat.click) AS total_clicks
FROM ce_landing
LEFT JOIN ce_stat
 ON FIND_IN_SET(keyword,page_keyword)>0
GROUP BY ce_landing.id, ce_landing.page_keyword;

这不容易转换为 Cakephp 的 find 方法。只需使用 query method实现。

$this->Landing->query("SELECT Landing.id, Landing.page_keyword, SUM(ce_stat.click) AS total_clicks
    FROM ce_landing AS Landing
    LEFT JOIN ce_stat
     ON FIND_IN_SET(keyword,page_keyword)>0
    GROUP BY Landing.id, Landing.page_keyword;");