CODEWITHSUNDEEP
phpmysqlcakephp
Manoj Dhiman
Manoj Dhiman

Reputation: 5166

How can i select records from two tables in cake php(cake 2)

I am new to cake php . i have two tables

  1. ce_landing
  2. ce_stat

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 FROM EB_adwords.ce_stats AS Stat WHERE Stat.keyword LIKE '%Landing.page_keywords%' AND Stat.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

Answers (1)

AgRizzo
AgRizzo

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

Related Questions