Reputation: 5166
I am new to cake php . i have two tables
structure is
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
i want to fetch all the records ce_landing.page_keyword
is present in ce_stat.keyword
. I am using this in my cake php model
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' => ''
)
);
but this is generated sql query like
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
FROMEB_adwords
.ce_stats
ASStat
WHEREStat
.keyword
LIKE '%Landing.page_keywords%' ANDStat
.keyword
= ('559f479a-82ac-4e3d-8c24-19b5c0a8011f')
so it returns null data because of AND Stat
.keyword
=('559f479a-82ac-4e3d-8c24-19b5c0a8011f') conditions.
Update
what i want to get all the records from ce_landing
with total clicks according to keywords present . i.e for record 1 in ce_landing
. i'll get the result
id page_keyword clicks
1 keyword1,check,keyword3 30
2 keyword2,usa,alphanumeric 2
Upvotes: 1
Views: 665
Reputation: 5271
You needs a SQL statement equivalent to:
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;
which does not easily translate to Cakephp's find
method. Just use the query method to implement.
$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;");
Upvotes: 1