Hi, I'm hoping someone can help me with this.
I've been provided with ecommerce Excel spreadsheets which contain thousands of products. I've got the import working using the Maatwebsite Excel package but have run into an issue with returning paginated results which I need to return only the first of each product but not the other variations.
So, in my database I have the sku of the product along with the product_group column. There may be 20 'variations' which are actually 20 products so when I want to list all products in the admin area, it shows 20 variations of product 1, then 20 variations of product 2 etc.
What I'd like to do is only return the first 'variation' so that my list has product 1, product 2, product 3 etc.
Accessing the variations would be handled by clicking on the first (or parent) product and then I'd pull all the other variations in on a tab in that screen so they can be edited.
So, to clarify, I currently have this:
product_group product
001M - Product 1 (Black)
001M - Product 1 (Red)
001M - Product 1 (Green)
001M - Product 1 (Orange)
002M - Product 2 (Black)
002M - Product 2 (Red)
002M - Product 2 (Green)
002M - Product 2 (Orange)
003M - Product 3 (Black)
003M - Product 3 (Red)
003M - Product 3 (Green)
003M - Product 3 (Orange)
ETC
-----------------
< [1][2][3][4]…[100] >
But I'd like this:
product_group product
001M - Product 1 (Black)
002M - Product 2 (Black)
003M - Product 3 (Black)
004M - Product 3 (Black)
005M - Product 3 (Black)
006M - Product 3 (Black)
ETC
-----------------
< [1][2][3][4]…[100] >
This is the simple $product query I'm using:
$products = Product::orderby('created_at', 'desc')
->paginate(20);
Can anyone tell me what I need to change so I can get the results I need?
Many thanks.
Hi @devedge
just add groupBy to query.
$products = Product::groupBy('product_group')->orderBy('created_at', 'desc');
but u will need to specify columns in select and use some of aggregate functions.
For more information see docs: https://laravel.com/docs/10.x/queries#raw-expressions
You can do a subquery to get the IDs. Example here https://stackoverflow.com/a/2739528/1305117
@Devedge Ask chatgpt to convert it if needed. Might not be 100% correct, but it will get you close :)
@Devedge Just ask it as if you asked here :)
Here is my attempt
Can you please convert this to laravel eloquent. The products table has a model named Product
SELECT product, product_group
FROM products
WHERE id IN (
SELECT min(id)
FROM products
GROUP BY product_group
);
And it spit out this
Product::whereIn('id', function($query){
$query->selectRaw('MIN(id)')
->from('products')
->groupBy('product_group');
})
->select('product', 'product_group')
->get();
@Sinnbeck Thanks for that. I managed to get something similar:
DB::table('sometable')
->select('somecolumn', 'anothercolumn')
->whereIn('id', function($query) {
$query->select(DB::raw('MIN(id)'))
->from('sometable')
->groupBy('somecolumn');
})
->get();
I tweaked it to get pagination working:
$products = DB::table('products')
->select('product_group', 'title', 'id', 'featured_image', 'sku', 'status', 'type', 'normal_price', 'manage_stock', 'url_segments')
->whereIn('id', function($query) {
$query->select(DB::raw('MIN(id)'))
->from('products')
->groupBy('product_group');
})
->paginate(10);
All seems to be okay - thanks so much for the pointer to chatgpt, that was a lifesaver!
@Devedge Yeah as I said, it gets close but often misses stuff :) So an understanding of laravel is needed
Please or to participate in this conversation.