CODEWITHSUNDEEP
sqlcakephp
zakblack
zakblack

Reputation: 59

How to Alias one of the table sql in cakephp 3.6.3

I'm trying to access Contacts. I'm getting below error

Error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'Contacts'

If you are using SQL keywords as table column names, you can enable identifier quoting for your database connection in config/app.php.

Check SQL Query Screenshot

How do I setAlias() in table associations ?

ContactsController.php

     public function index()
        {
    $this->paginate = [
        'contain' => ['Users', 'Contacts', 'SourceProspects', 'Status', 
    'Secteurs', 'Products']
    ];
    $contacts = $this->paginate($this->Contacts);
    $this->set(compact('contacts'));
    }

public function view($id = null)
    {
    $contact = $this->Contacts->get($id, [
        'contain' => ['Users', 'Contacts', 'SourceProspects', 'Status', 
    'Secteurs', 'Products', 'Leads', 'Accounts']
    ]);

    $this->set('contact', $contact);
   }

ContactsTable.php

$this->setTable('contacts');
    $this->setDisplayField('name');
    $this->setPrimaryKey('id');

    $this->addBehavior('Timestamp');

    $this->belongsTo('Users', [
        'foreignKey' => 'user_id'
    ]);
    $this->belongsTo('Contacts', [
        'foreignKey' => 'contact_type_id'
    ]);
    $this->belongsTo('Leads', [
        'foreignKey' => 'lead_id'
    ]);
    $this->belongsTo('SourceProspects', [
        'foreignKey' => 'source_prospect_id'
    ]);
    $this->belongsTo('Accounts', [
        'foreignKey' => 'account_id'
    ]);
    $this->belongsTo('Status', [
        'foreignKey' => 'statut_id'
    ]);
    $this->belongsTo('Secteurs', [
        'foreignKey' => 'secteur_id'
    ]);
    $this->belongsTo('Products', [
        'foreignKey' => 'product_id'
    ]);
    $this->hasMany('Accounts', [
        'foreignKey' => 'contact_id'
    ]);
    $this->hasMany('Leads', [
        'foreignKey' => 'contact_id'
    ]);
}

Upvotes: 0

Views: 2657

Answers (1)

arilia
arilia

Reputation: 9398

The problem seems to be in ContractsTable here

$this->belongsTo('Contacts', [
    'foreignKey' => 'contact_type_id'
]);

in this way cake join the Contacts table with itself so creating a non unique alias

maybe is just a typo and you wanted to do

$this->belongsTo('ContactTypes', [
    'foreignKey' => 'contact_type_id'
]);

but if you actually want to use that relationship then you have to alias the joined Contacts table

$this->belongsTo('ParentContacts', [ // choose your alias here
    'className' => 'Contacts'
    'foreignKey' => 'contact_type_id'
]);

so every time you have to refer to the joined table you can do something like

'contain' => [
    ..., 
   'ParentContacts', 
 ],

Upvotes: 1

Related Questions