L
L
leonardooleg2020-04-22 20:18:59
MySQL
leonardooleg, 2020-04-22 20:18:59

Laravel DB query with Join?

There is a table of goods, there is a table with colors (red, blue).
You need to make 1 request and get all colors in the product.
In the product table, this field is "color_id" with the value "1,5,6"
how to join two tables

$products = DB::table('products')
            ->leftJoin('categoryables', 'categoryables.categoryable_id', '=', 'products.id')
            ->leftJoin('categories', 'categoryables.category_id', '=', 'categories.id')
            ->leftJoin('brands', 'products.brand_id', '=', 'brands.id')

            ->leftJoin('colors', 'products.color_id',  '=',  'colors.id)  ///products.color_id = "1,5,6" -строка

            ->leftJoin('sizes', 'products.size_id', '=', 'sizes.id')
            ->whereIn('categoryables.category_id', $categories)
            ->orderBy('created_at', 'desc')
            ->select('products.*', 'categories.path', 'categories.title', 'brands.name_brand', 'colors.name_color', 'colors.img_color', 'sizes.brand_name_size')
            ->paginate(5);


Ask leading questions

Answer the question

In order to leave comments, you need to log in

1 answer(s)
L
leonardooleg, 2020-04-22
@leonardooleg

did so

$products = DB::table('products')
            ->leftJoin('categoryables', 'categoryables.categoryable_id', '=', 'products.id')
            ->leftJoin('categories', 'categoryables.category_id', '=', 'categories.id')
            ->leftJoin('brands', 'products.brand_id', '=', 'brands.id')

            ->leftjoin('colors',DB::raw('FIND_IN_SET(colors.id,products.color_id)'),">",DB::raw("'0'"))

            ->leftjoin('sizes',DB::raw('FIND_IN_SET(sizes.id,products.size_id)'),">",DB::raw("'0'"))
            ->whereIn('categoryables.category_id', $categories)
            ->orderBy('created_at', 'desc')
            ->select('products.*', 'categories.path', 'categories.title', 'brands.name_brand', 'colors.name_color', 'colors.img_color', 'sizes.brand_name_size')
            ->paginate(5);

but only the last field in the database. not an array of values

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question