Sum all the rows with same column value

[harshuchilzy]

Is there any method to join / merge all rows with same values.
In real scenario, I have several products with different quantities as the database rows. I want to show them as one row with sum of quantities.

https://i.ibb.co/qmJXD8H/Screenshot-2023-05-26-at-3-30-31-PM.png

[lrljoe]

Looking at a glance at that, you may benefit from some normalisation in your database if you haven't already

E.g.
Products
ProductID
ProductName
ProductDetails

ProductPurchase
ProductID
SupplierID
PurchaseDetails (e.g. quantity)

Supplier
SupplierID
SupplierDetails

Then use relationships to link it all together.

You can use the withSum and withCount methods in the builder method to get the additional information out.

[harshuchilzy]

Yes, already those details are fetching from the relationships. But quantity stays in the same db table which I am trying show (below screenshot).

https://i.ibb.co/TLX6BbS/Screenshot-2023-05-27-at-8-12-01-AM.png

Tried many ways but got no luck yet :(

[lrljoe]

It'll be possible using the builder() approach.

If you are able to share:

• Models
• Relevant fields for each model
• Any with() on the model as default
• Relationship methods between the models

And which fields you are trying to group them on

I should then be able to steer you in the right direction

Are you open to some additional relationship methods or additional packages as well if needed?

There are some good packages out there for working with deeper relations that may help across your application.

If you attach or paste your table component code too then I can give some pointers with that and the above info.

It may be that eager loading or is not being your friend here, but it'll be fixable easily enough.

I've yet to come across something unusual enough to not work entirely!

[harshuchilzy]

public function builder(): Builder
    {
        return MyModel::query()->with(['product', 'reasonable', 'warehouse'])->whereRelation('product','supplier_id', $this->supplier_id)->whereNot('status', 'po')->selectRaw('SUM(po_items.quantity) as qty')->groupBy('po_items.product_id');
        }

This is working :) Hope this is a good practice to use? Thank you so much for your effort on supporting me

[lrljoe]

If it's working, then that's great.

What you should do however is limit the fields in your with method to only the IDs used in relations, and the fields you need.

It may not be critical depending on size of your models and the related db tables, but it's best practice all the same

e.g.
with(['someModel:id,name,price'])

There is a withSum() and withCount() option. Which is a Laravel method, you can add in your constraints too, which will make it a little cleaner.
So you can sum related fields too e.g.

withSum('relatedModel.price')

Use barryvdh debug bar to look at the queries you're actually generating as a whole (not just this package), and best practice is to only return fields you actually need.