SQLSTATE[42000] Error


#1

I am using CakePHP to create an online education system. I am getting this error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: ‘Users’ when I try to get a list of users from my database. I am using the code created by bake, with no modifications. I am not sure what is happening. The query that is created is: SELECT Users.users_id AS Users__users_id, Users.users_username AS Users__users_username, Users.users_password AS Users__users_password, Users.users_hint AS Users__users_hint, Users.users_answer AS Users__users_answer, Users.users_grades AS Users__users_grades FROM users Users INNER JOIN users Users ON Users.users_id = (Users.users_id) LIMIT 20 OFFSET 0.

Any help would be greatly appreciated.
Thank you, Troy L. Marker


#2

It would help showing your table and fields. Ther error is from INNER JOIN users Users ON Users.users_id = (Users.users_id) it seems. You need to provide different aliases.


#3

Thanks for the response. I’m sorry about the lack of information. I will put something together, but my next question is how can I change the query? As I mentioned, I am using the code that bake created for me.


#4

Here is what my users table looks like:
Users Table:
users_id - int(11) - auto-increment primary key
users_username varchar(45)
users_password varchar(255)
users_hint varchar(100)
users_answer varchar(45)


#5

And this is the table model.
public function initialize(array $config)
{
parent::initialize($config);

    $this->setTable('Users');
    $this->setDisplayField('users_id');
    $this->setPrimaryKey('users_id');

    $this->belongsTo('Users', [
        //'foreignKey' => 'users_id',
        //'joinType' => ''
    ]);
    //$this->belongsToMany('Paths', [
    //    'foreignKey' => 'user_id',
    //    'targetForeignKey' => 'path_id',
    //    'joinTable' => 'users_paths'
    //]);
}

I have tried it with the commented lines both ways, with the same effect.


#6

The problem is that you defined users belongs to users. you should use another alias and add a classname

$this->belongsTo('AdminUsers', [
    'className' => 'Users',        
    'foreignKey' => 'users_id',
    'joinType' => ''
]);

#7

rrd,

Thanks for the reply. I made the change, as you suggested and listed here:

    $this->belongsTo('AllUsers', [
        'className' => 'Users',
        'foreignKey' => 'users_id',
        'joinType' => ''
    ]);

However when I run it I get the following error: Users is not associated with users.

I’m note sure why I am getting this error, any suggestions?

Thanks again, Troy


#8

You should remove $this->belongsTo('Users', [.... Than one cause the error.

If you already removed it than show me your find() call


#9

After working with it a little more, I got it to work. My mistake was assuming that the code created by Bake was ready to go, I did not think I would have to tweak it, but after doing just that, it is working. I do appreciate your help, it got me going in the right direction.

Now on to bigger and better things.

Thanks again,
Troy L. Marker


#10

And you were correct, the problem was with my find() call.