php - 使用 MySQL join 的 Laravel 关系

Question

我的产品模型中有一个关系，并且工作正常

        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();

我已将其替换为

        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();

现在我收到此错误

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)

Answer 1

您使用同一个表两次进行连接，而不使用任何别名。

请引用以下代码

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();

注意:希望是您从 cate1 引用的 parent_id。如果没有，请随时将别名更改为合适的名称。