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

Question

I'm trying to create a form that adds data to my database. My table is called coupons and I have the following code:

//CouponsController.php
public function addCoupon() {
   $coupon = $this->Coupons->newEntity();
       if ($this->request->is('post')) {
         $coupon = $this->Coupons->patchEntity($coupon, $this->request->data);
         if ($this->Coupons->save($coupon)) {
             $this->Flash->success(__('The coupon has been saved.'));
             return $this->redirect(['action' => 'index']);
             } else {
             $this->Flash->error(__('The coupon could not be saved. Please, try again.'));
             }
        }
        $coupons = $this->Coupons->Coupons->find('list', ['limit' => 200]);
        $this->set(compact('coupon', 'coupons'));
        $this->set('_serialize', ['coupon']);
}

//CouponsTable.php
public function initialize(array $config)
{
    parent::initialize($config);
    $this->table('coupons');
    $this->displayField('coupon_id');
    $this->primaryKey('coupon_id');
    $this->belongsTo('Coupons', [
        'foreignKey' => 'coupon_id',
        'joinType' => 'INNER'
    ]);
}

//add_coupon.ctp
<h3>Add Coupon</h3>
<?php 
    echo $this->Form->create(null,['url' => ['action' => 'addCoupon']]);
    echo $this->Form->input('coupon_code');
    echo $this->Form->input('expiration_date');
    echo $this->Form->input('discount_amount');
    echo $this->Form->input('usage_limit');
    echo $this->Form->input('domain_limit');
    echo $this->Form->input('description');
    echo $this->Form->input('type');
    echo $this->Form->button('Submit');
    echo $this->Form->end();
?>

When I click on submit, the data gets stored in the database but I'm presented with "Error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'Coupons'". SQL Query: SELECT Coupons.coupon_id AS Coupons__coupon_id, Coupons.coupon_code AS Coupons__coupon_code, Coupons.expiration_date AS Coupons__expiration_date, Coupons.discount_amount AS Coupons__discount_amount, Coupons.usage_limit AS Coupons__usage_limit, Coupons.domain_limit AS Coupons__domain_limit, Coupons.description AS Coupons__description, Coupons.type AS Coupons__type FROM coupons Coupons INNER JOIN coupons Coupons ON Coupons.coupon_id = (Coupons.coupon_id) LIMIT 20 OFFSET 0 So what is the problem here?

Charlie Dalsass Charlie Dalsass · Accepted Answer · 2016-12-03T01:59:11

Change

$this->Coupons->Coupons->find('list', ['limit' => 200])

to

$this->Coupons->find('list', ['limit' => 200])

You are causing the table to join against itself by referencing it twice.

Also, remove this code. I see no reason to join against the table:

$this->belongsTo('Coupons', [
    'foreignKey' => 'coupon_id',
    'joinType' => 'INNER'
]);

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

2 Answers