Laravel SQLSTATE[42000]: Syntax error or access violation

Question

I am new to laravel and im stuck with my relationshops what looks like the following

Categories

    id  name            slug
-----------------------------------------------------------------
    3   Location        location
    4   Outfits         outfits
    5   Other           other

sub_categories

  id    category_id     name                slug
-----------------------------------------------------------------------------
     12     3           Club                club
     13     3           Home / Hotel        home-hotel
     14     3           Outdoor             outdoor
     15     3           Studio              studio
     16     4           Bikini / Swimwear   bikini-swimwear
     17     4           Dress               dress
     19     4           Jeans               jeans
     35     5           Dancing             dancing

Category model

<?php

class Category extends Eloquent {

    public $timestamps = false;

    public function subcategory()
    {
        return  $this->belongsToMany('subcategory', "sub_categories");
    }
}

And i get the following error

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'sub_categories' (SQL: select `sub_categories`.*, `sub_categories`.`category_id` as `pivot_category_id`, `sub_categories`.`subcategory_id` as `pivot_subcategory_id` from `sub_categories` inner join `sub_categories` on `sub_categories`.`id` = `sub_categories`.`subcategory_id` where `sub_categories`.`category_id` = ?) (Bindings: array ( 0 => 1, ))

Could please someone point out what I am doing wrong?

I think you'll need to describe your intended model for categories and subcategories. As it's mapped (as far as I can see), a category can have many subcategories, and a subcategory can belong to multiple categories at once. Is that what you're intending? — Joachim Isaksson
– Joachim Isaksson, Commented Jul 28, 2013 at 13:12
The problem would seem to be that two tables are mapped to the same name sub_category at once. A many-to-many relationship requires 3 tables (category/subcategory/category_subcategory) which need distinct names. If two of the tables get the same name, you'll get the error you're showing. — Joachim Isaksson
– Joachim Isaksson, Commented Jul 28, 2013 at 13:19

echo_Me · Accepted Answer · 2013-07-28 13:08:03Z

1

you should give different aliases names to same table

change this

 inner join `sub_categories`

to

   inner join `sub_categories` as sc   
                                   ^^--//-this alias use it instead of sub_categories

in your query it will be

    select `sub_categories`.*, sc.`category_id` as `pivot_category_id`, sc.`subcategory_id` as `pivot_subcategory_id` from `sub_categories` inner join `sub_categories` sc on sc.`id` = `sub_categories`.`subcategory_id` where `sub_categories`.`category_id` = ?) (Bindings: array ( 0 => 1, )

Example:

 select * from table1 as t1
 inner join table1 as t2
on t1.id = t2.id

Clarification of your error: Not unique table/alias

you are joining same table without aliases to different between them.

edited Jul 28, 2013 at 13:08

answered Jul 28, 2013 at 13:01

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joachim Isaksson · Accepted Answer · 2013-07-28 13:47:38Z

0

The problem looks like that two tables are mapped to the same name sub_category at once.

A many-to-many relationship requires 3 tables (category/subcategory/category_subcategory) which need distinct names. If two of the tables get mapped with the same name, you'll get the error you're showing.

Change one of the mappings, and you should be up and running.

answered Jul 28, 2013 at 13:47

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

1 Comment

Erik Aybar Over a year ago

How might you go about doing this "change one of the mappings" regarding Eloquent and $model->belongsToMany('OtherModel') ? I've run into a similar situation, but I am using the 3 table relationship that you've described above. I am receiving an almost identical error. Hmmm...

Collectives™ on Stack Overflow

Laravel SQLSTATE[42000]: Syntax error or access violation

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related