How to Join one table to another table by zipcode?

Question

I have two table I want to join Ad.zipcode with Zipcode.zip please check below details -

Table : ads

+----+----------+---------+----------+
| id | user_id  | zipcode |  photo   |
+----+----------+---------+----------+
|  1 | 1        | 751010  | img1.jpg |
|  2 | 2        | 123456  | img2.jpg |
|  3 | 3        | 756114  | img3.jpg |
|  4 | 4        | 121010  | img4.jpg |
|  5 | 5        | 789520  | img5.jpg |
|  6 | 6        | 404040  | img6.jpg |
|  7 | 7        | 805020  | img7.jpg |
+----+----------+---------+----------+

Table : zipcodes

+--------+---------+----------+
|  zip   | city    |  state   |
+--------+---------+----------+
| 789520 | ctc     | odisha   |
| 756114 | bhadrak | odisha   |
| 123456 | bbsr    | odisha   |
| 756114 | rlc     | odisha   |
| 789502 | bls     | odisha   |
---------+---------+----------+

I want to join with zipcode which is contain in the ads table I need to fetch record in the second table which ads zipcode is 756114

I have tried to join in the $hasMany but id not present in the zipcodes table that why join not working.

My Code:

public $hasMany = array(
    'Zipcode' => array(
        'className' => 'Zipcode',
        'foreignKey' => 'zip',
    ),
);

In my aboave code I am getting blank array. Please help me. Hi I am using CakePHP version-2.x

How to associate model with another model?

Answer 1

In your Zipcode Model you need to:

public $primaryKey = 'zip';

On a side note, why don't you have an autonumeric PK in that table? Zip codes usually contain non-numeric characters...

Answer 2

In Ad Model place:

public $belongsTo = array(
    'Zipcode' => array(
        'className' => 'Zipcode',
        'foreignKey' => 'zipcode',
    )
);

In Zipcode Model place:

public $primaryKey = 'zip';

public $hasMany = array(
    'Ad' => array(
        'className' => 'Ad',
        'foreignKey' => 'zipcode',
    )
);