One query to retrieve values from multiple tables

Question

I've been trying to retrieve the data from multiple tables and it was clear and fine until I've faced the challenge to use the pivot tables (or link tables between manyToMany relationship tables).

So far, I can easily get a data from every single table, but I need to make just ONE query, which will take all the data I need.

Tables = products, details, categories, product_category (this one is a pivot table)

product_category = product_id | category_id (each one of these columns references on the id - product_id on products and category_id on categories)

Code to get queries:

$products = DB::table('products')
                    ->join('details', 'products.id', '=', 'details.product_id')
                    ->leftJoin('product_category', function($join) {
                        $join->on('products.id', '=', 'product_category.product_id');
                    })
                    ->select('products.*', 'details.*', 'product_category.*')
                    ->orderBy('products.created_at', 'desc')
                    ->paginate(10);

Then on index page I can get all the data within foreach loop, but there's one tiny problem - I can only get the columns of product_category, in other words only category_id which references on categories.id - but I need to get those values from the categories table.

Please, don't give me a documentation or something like that, I research a lot every time before I ask a question and read at least a dozen of similar Q&A, but sometimes I need another point of view to get the solution and to understand how it works. Appreciate for every help.

Yep. All the relationships are set up properly and as I said, I can easily get the data, just don't know how to mix it and get single query. — MadMatt

Rutvij Kothari Rutvij Kothari · Accepted Answer · 2017-12-12T21:34:05

Problem is overriding your column's information.

You can modify your query like this:

$query = DB::table('product_category as pc')
    ->leftJoin('products as p', 'p.id', 'pc.product_id')
    ->join('details as d', 'd.product_id', 'p.id')
    ->select('p.id as product_id', 'p.name as product_name', 'd.id as details_id', .......) // <-- you need to select field whichever you want. table.* is applicable if tables have different column names.

One query to retrieve values from multiple tables

1 Answers