laravel relation using MySQL join

Question

I have a relation in my Product Model and it's working fine

        self::where('products.id', '=', $id)
        ->select('products.category_id', 'main_categories.id as main_category_id')
            ->join('categories', 'products.category_id', '=', 'categories.id')
                ->join('main_categories', 'categories.main_category_id', '=', 'main_categories.id')
                    ->first();

i have replaced it with

        self::where('products.id', '=', $id)
        ->select('products.category_id', 'categories.id as parent_id')
            ->join('categories', 'products.category_id', '=', 'categories.id')
                ->join('categories', 'categories.parent_id', '=', 'categories.id')
                    ->first();

Now i get this error

Syntax error or access violation: 1066 Not unique table/alias: 'categories' (SQL: select products.category_id, categories.id as parent_id from products inner join categories on products.category_id = categories.id inner join categories on categories.parent_id = categories.id where products.id = 13 limit 1)

you are joining `categories` table two times. is that you expecting? — Sandeep Sudhakaran, Oct 23 '19 at 12:13
you can check this link https://stackoverflow.com/questions/1435177/why-does-this-sql-code-give-error-1066-not-unique-table-alias-user — Noob Coder, Oct 23 '19 at 12:14
you are joining `categories` table two times. is that you expecting? if yes, please add alias name by simple adding `->join('categories as category1', ........` — Sandeep Sudhakaran, Oct 23 '19 at 12:19
@SandeepSudhakaran yes I'm joining categories table two times — kareem elsharkawy, Oct 23 '19 at 12:20
`->join('categories', 'categories.parent_id', '=', 'categories.id')` check this statement...Why you join `categories` with `categories` — Jithesh Jose, Oct 23 '19 at 12:21
add alias name for your tables and try. please check my previous commnts — Sandeep Sudhakaran, Oct 23 '19 at 12:22

score 1 · Accepted Answer · answered Oct 23 '19 at 12:26

1

You are using same table two times for joining without using any alias names.

please refer the below code

self::where('products.id', '=', $id)
        ->select('products.category_id', 'cate1.id as parent_id')
            ->join('categories as cate1', 'products.category_id', '=', 'cate1.id')
                ->join('categories as cate2', 'cate1.parent_id', '=', 'cate2.id')
                    ->first();

NOTE: hope, the parent_id you refered from cate1. if not please, feel free to change the alias name to appropriate.

answered Oct 23 '19 at 12:26

Sandeep Sudhakaran

1,072
2
9
22

I made some edit. get the latest answer please. it is because I forgot to add alias in select – Sandeep Sudhakaran Oct 23 '19 at 12:31
coool. happy coding. hope you understood the problem. if not feel free to ask. – Sandeep Sudhakaran Oct 23 '19 at 12:32
1

I did Thanks a lot – kareem elsharkawy Oct 23 '19 at 12:32

laravel relation using MySQL join

1 Answers1