Eloquent group by product, ordering differently based on product

I currently have the following set up:

Category, SubCategory, Business, Enhancement & Product models.

• A Category has many SubCategorys
• A SubCategory has many Businesss
• A Business may have one Enhancement
• A Enhancement must have one Product

A product could be an enhanced listing, a premium listing, or additional features. If the business has brought either an enhanced or premium listing, it is recorded in the enhancement table.

So, product#1 = Premium Listing. Product#2 = Enhanced listing.

What I am wanting is to return all the businesses for a chosen SubCategory, grouping by the product that the enhancement is attached to. If the business has an enhancement, I'm wanting to randomise the order. If the business does not have a product, I'm wanting to order by name in ASC order.

So the result I'm expecting is to have a complete list of all businesses, with those with a Premium Listing, listed first in a random order. Then all businesses with an enhanced listing in a random order. Then all the other businesses that have no enhancement, in alphabetical order.

I currently have all the businesses listed in name order with the following code:

SubCategory::where('slug', $subCategory)->where('category_id', $category->id)
    ->with(['businesses' => function($query) {
        $query->orderBy('name', 'ASC');
}])->firstOrFail();

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2PMYL6g
via IFTTT