Returning specific JSON format from Query Builder

Question

I'm using Query Builder to get results from multiple joins tables with this query :

$products = DB::table('products as p')
              ->select(
                  'p.id',
                  'p.name',
                  'p.reference',
                  'p.price',
                  'i.path',
                  'i.name'
                )
              ->join('products_r_images as pri', 'pri.product_id', '=', 'p.id')
              ->join('images as i', 'i.id', '=', 'prd.image_id')
              ->get();

One product can have multiples images associated.

With the above query, I obtains this result :

[
    {
        id: 3,
        name: "Test",
        reference: "ref-test",
        price: 123,
        image_path: "product/product-3/",
        image_name: "product_1.jpg"
    },
    {
        id: 3,
        name: "Test",
        reference: "ref-test",
        price: 123,
        image_path: "product/product-3/",
        image_name: "product_2.jpg"
    }
]

As you can see, two rows are returns for one product, while I would like to have documents datas returns in one row with something like that :

[
    {
        product_id: 3,
        name: "Test",
        reference: "ref-test",
        price: 123,
        image_path: "product/product-3/",
        image_name: 
        [
            "product_1.jpg", "product_2.jpg"
        ]
    }
]

Is there a way to do that directly with Query Builder, or another treatment is needed ?

Do you mean a function in Product model returning hasMany(App\Images) ? If it is, no I didn't, because for this query I only use Query Builder. — zken
– zken, Commented Jul 13, 2017 at 11:48
Fair enough :) Just fyi, the relationship would be a belongsToMany not hasMany — Rwd
– Rwd, Commented Jul 13, 2017 at 11:55

Rwd · Accepted Answer · 2017-07-13 12:03:32Z

3

If you just want to use Query Builder for this then you should be able to:

$products = DB::table('products as p')
    ->select(
        'p.id',
        'p.name',
        'p.reference',
        'p.price',
        'i.path',
        'i.name'
    )
    ->join('products_r_images as pri', 'pri.product_id', '=', 'p.id')
    ->join('images as i', 'i.id', '=', 'prd.image_id')
    ->get()
    ->groupBy('id')
    ->map(function ($products) {

        $product = $products->first();
        $product->image_name = $products->pluck('image_name');

        return $product;
    });

Hope this helps!

answered Jul 13, 2017 at 12:03

Rwd

35.3k7 gold badges71 silver badges85 bronze badges

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

5 Comments

zken Over a year ago

Thank you for your answer ! But I have an error : Call to undefined method Illuminate\Database\Query\Builder::map()

Rwd Over a year ago

@Nenroz What version of Laravel are you using?

zken Over a year ago

I'm using Laravel 5.4

Rwd Over a year ago

@Nenroz Have you copied the query exactly i.e. you're still calling get() before the groupBy() and map()?

zken Over a year ago

It was indeed the problem, thank you very much for you help !

Collectives™ on Stack Overflow

Returning specific JSON format from Query Builder

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related