Relation belongsToMany unknown columns

Need Help
1

good night, I’ve use three table

Regions and Districts

the relation is hasMany and belongsToMany. I’ve used generate auto baking

cake bake all Regions

cake bake all Districts

cake bake all Subdistricts

the structure database MySQL below

Table Regions
+------+---------------+
|  No  |    Fields     |
+------+---------------+
|  1   |      ID       |
|  2   |  province_id  |
|  3   |    Kabupaten  |
+------+---------------+ 

Table Districts
+------+---------------+
|  No  |    Fields     |
+------+---------------+
|  1   |      ID       |
|  2   |   region_id   |
|  3   |   Kecamatan   |
+------+---------------+

Table regions_districts
+------+---------------+
|  No  |     Fields    |
+------+---------------+
|  1   |       ID      |
|  2   |   region_id   |
|  3   |  district_id  |
+------+---------------+

Table Subdistricts
+------+---------------+
|   No |       ID      |
+------+---------------+
|   1  |      ID       |
|   2  |  district_id  |
|   3  |   Kelurahan   |
+------+---------------+

the table model district, region and subdsitrict is below

table Region

$this->hasMany('Districts', [
    'foreignKey' => 'region_id'
]);
$this->belongsToMany('Districts', [
    'foreignKey' => 'region_id',
    'targetForeignKey' => 'district_id',
    'joinTable' => 'regions_districts'
]);

Table Districts

$this->belongsTo('Regions', [
   'foreignKey' => 'region_id'
]);
$this->belongsToMany('Regions', [
    'foreignKey' => 'district_id',
    'targetForeignKey' => 'region_id',
    'joinTable' => 'regions_districts'
]);

Table Subdistricts

$this->belongsTo('Districts', [
   'foreignKey' => 'district_id'
]);
$this->belongsToMany('Districts', [
   'foreignKey' => 'subdistrict_id',
   'targetForeignKey' => 'district_id',
   'joinTable' => 'districts_subdistricts'
]);

The Controller inside tables Districts, Regions and Subdistricts is below

Controller Districts function view

public function view($id = null)
 {
   $district = $this->Districts->get($id, [
     'contain' => ['Regions', 'Subdistricts', 'Customers', 'Postcodes']
   ]);
   $this->set('district', $district);
 }

Controller Region function view

public function view($id = null)
 {
   $region = $this->Regions->get($id, [
      'contain' => ['Provinces', 'Districts', 'Customers', 'Postcodes']
   ]);
   $this->set('region', $region);
 }

Conroller Subdistricts function view

public function view($id = null)
 {
    $subdistrict = $this->Subdistricts->get($id, [
       'contain' => ['Districts', 'Postcodes', 'Customers']
    ]);
    $this->set('subdistrict', $subdistrict);
 }

next I’m access template District, with click view record, the error message shown below

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'DistrictsSubdistricts.subdistrict_id' in 'on clause' 

SQL Query
SELECT DistrictsSubdistricts.ID AS `Subdistricts_CJoin__ID`, DistrictsSubdistricts.district_id AS `Subdistricts_CJoin__district_id`, DistrictsSubdistricts.subdsitrict_id AS `Subdistricts_CJoin__subdsitrict_id`, Subdistricts.ID AS `Subdistricts__ID`, Subdistricts.district_id AS `Subdistricts__district_id`, Subdistricts.Kelurahan AS `Subdistricts__Kelurahan` FROM subdistricts Subdistricts INNER JOIN districts_subdistricts DistrictsSubdistricts ON Subdistricts.ID = (DistrictsSubdistricts.subdistrict_id) WHERE DistrictsSubdistricts.district_id in (:c0)

Could this be caused by using Auto-Tables?
Some of the Table objects in your application were created by instantiating "Cake\ORM\Table" instead of any other specific subclass.


This could be the cause for this exception. Auto-Tables are created for you under the following circumstances:

    The class for the specified table does not exist.
    The Table was created with a typo: $this->getTableLocator()->get('Atricles');
    The class file has a typo in the name or incorrect namespace: class Atricles extends Table
    The file containing the class has a typo or incorrect casing: Atricles.php
    The Table was used using associations but the association has a typo: $this->belongsTo('Atricles')
    The table class resides in a Plugin but no plugin notation was used in the association definition.


Please try correcting the issue for the following table aliases:

    RegionsDistricts
    DistrictsSubdistricts

I have added some code in table Subdistricts

I hope someone help me fixing it, I’ve try repeat but did’nt found the answer, thanks very much to help me

2

Nothing at all in the code you’ve shown mentions subdistricts at all. Please add that code, or else we can’t help.

2 Likes
3

mr. Zuluru, I have added some code in tables Subdistrict (structure MySQL, table method, and controller function view).

thanx for help me, from the solution

4

Is it valid to have two associations with the same name in a model? That feels like it would be unpredictable.

Regions has 2 different associations called District, belongsTo and belongsToMany. Districts has the same two types of associations to Regions, both named Regions. SubDistricts has two associations named District also.

I don’t know for sure if this would cause a problem but it makes it hard to know what is intended. Either choose the association that works for your situation or rename them to make it clear which you are using in your code.

When renaming you will need to use the ‘className’ key to identify the table to use:

$this->hasMany('Districts', [
    'foreignKey' => 'region_id'
]);
$this->belongsToMany('AlsoDistricts', [
    'className' => 'Districts',
    'foreignKey' => 'region_id',
    'targetForeignKey' => 'district_id',
    'joinTable' => 'regions_districts'
]);
1 Like
5

okeh, thanks for the solution, I’ll try and remove one options inside relation,

two tables above I’ve used same relation, and it’s work, I don’t know why the relation

tables regions_districts

tables districts_subdisrricts

shown display error.

the fact show display error unknown column only inside

Districts controller function view

Table method Districts

oke I’ll try again, and thanx for solution

6

It looks like maybe you’re not sure of the difference between hasMany, belongsTo and belongsToMany relations. Read up on those to make sure that you’re using the right one in each situation. Getting them wrong will mean that you’re basically lying to your code about what your database looks like, which most certainly leads to errors of the type you’re describing.

1 Like
7

yes, Mr.Zulur, might be I’m doesn’t understand to used relation in any condition, thanks for solution