Getting distinct rows from a model with relationships

If you are selecting the popular brands based on the products sale, maybe you could add the count of all the products sale together for each brand then list those brand based on that final sale count.

This is an example for using count and groupBy

https://laravel.com/docs/5.8/queries#raw-expressions

You can share the code of brands listing query if this doesn't work with you so we can help better :)

Hope this all makes sense.

Not to me. Definitely I need more info/code/db to understand the problem fully.

I solved my issue by getting the related products of a category sorted by popularity score and leftJoined the brands of those products with a SELECT DISTINCT query.

$category->products()->select('name')->leftJoin('brands', 'products.brand_id', '=', 'brands.id')->distinct()->limit(6)->get();

I knew how to do it with an SQL query but it was Just a matter of finding the right method from the documentation.

Btw it also works with a GROUP BY

$category->products()->select('brand_id', 'name')->leftJoin('brands', 'products.brand_id', '=', 'brands.id')->groupBy(['brand_id', 'name'])->limit(6)->get();

Which one is more performant... I wonder :)

Replace leftJoin() with with() if you have relationships defined.

@bugsysha

Thanks for your input ;)

It does not return distinct brands

$category->brands = $category->products()->with('brand')->distinct()->limit(6)->get();

It returns 6 products and includes a brand property with each product object.

With my leftJoin I get exactly 6 distinct brands.

Then I misunderstood you. You then do not need with(), but just something like this:

$category->products()->brand()->distinct()->limit(6)->get();

Point I was trying to make is that you do not need leftJoin() since that will be handled by relationships.

@bugsysha

Getting the following error

"Call to undefined method Illuminate\Database\Eloquent\Relations\HasMany::brand()"

I have the relationship set up in the product model and the brand model.

// Product.php model
namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;

class Product extends Model
{
    public function brand()
    {
        return $this->belongsTo(Brand::class);
    }
}

// Brand.php model
namespace App;

use Illuminate\Database\Eloquent\Model;

class Brand extends Model
{
    public function product()
    {
        return $this->hasMany(Product::class);
    }
}

Also, I'm throwing in the category model as well

// Category.php model
namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{    
    public function parent()
    {
        return $this->belongsTo(self::class, 'parent_id');
    }

    public function children()
    {
        return $this->hasMany(self::class, 'parent_id', 'id');
    }

    public function products()
    {
        return $this->hasMany(Product::class);
    }
}

Its late is it a typo? Although I made it work with the leftJoin() if it is better with the relationship (performance-wise) I would like to make it work that way.

Thanks for your time ;)

What about

$category->products()->groupBy('brand_id')->limit(6)->get();

And if you need brands to work with then

$category->products()->with('brand')->groupBy('brand_id')->limit(6)->get();

@bugsysha

$category->products()->groupBy('brand_id')->limit(6)->get();

"SQLSTATE[42000]: Syntax error or access violation: 1055 'database.products.id' isn't in GROUP BY (SQL: select * from `products` where `products`.`category_id` = 2 and `products`.`category_id` is not null and `is_active` = 1 group by `brand_id` limit 6) 

I guess MySQL strict is at fault here.

Without strict mode I get 6 products (distinct by brand) so I guess I could get the brand name with the relationship chain later in the blade template and with the second chain, I get the distinct products including the brand property. Again distinct brands. It just needs nested foreach loops to get to the brand names.

It's hard without database and models to get that right.

Yes, of course, it is.