How to prescribe products using multiple requirements

Question

I'm having a problem on how to do prescriptive analytics in products based on its ratings, price, location, sold, and category.

Here is my code

$products = Products::select(
            'products.*',
            'sellers.shop_name',
            'products.status as status',
            'products.price',
            'sellers.barangay as seller_location',
            'reviews.star',
            'products.category'
        )
        ->where('products.stocks', '>', 0)
        ->where('products.status', 'Approved')
        ->where('product_name', 'like', "%$searchQuery%")
        // ->whereIn('sellers.barangay', $uniqueNearbyBarangays)
        ->join('sellers', 'products.sellerID', '=', 'sellers.id')
        ->join('reviews', 'products.id', '=', 'reviews.productID')
        ->join('addresses as seller_address', 'sellers.id', '=', 'seller_address.userID')
        ->join('addresses as product_address', 'products.id', '=', 'product_address.userID')
        ->orderBy('products.price')
        ->orderByDesc('reviews.star') 
        ->orderBy('seller_location')
        ->paginate(50);

So, I first joined all the tables related to the products and used orderBy to sort it out.

My first code is this which I tried manually grouping the nearest places to each other using an array, but it does not seem to be working since I still get random product from random places.

$nearbyBarangaysGroups = [
            ["Dolores", "Juliana", "Del Pilar", "San Jose", "Santo Rosario", "San Nicolas", "Santo Niño", "Santa Lucia", "Magliman", "Santa Teresita", "San Agustin", "San Felipe"],
            ["Alasas", "Baliti", "Bulaon", "Calulut", "Dela Paz Norte", "Dela Paz Sur", "Del Carmen", "Del Rosario", "Lara", "Maimpis", "Pulung Bulo", "Lourdes", "Quebiawan", "Saguin", "Malino", "Malpitic", "Pandaras", "Panipuan", "San Isidro", "San Juan", "San Pedro Cutud"]
        ];
        
        $uniqueNearbyBarangays = collect($nearbyBarangaysGroups)->flatten()->unique()->toArray();
        
        $products = Products::select(
            'products.*',
            'sellers.shop_name',
            'products.status as status',
            'products.price',
            'sellers.barangay as seller_location',
            'reviews.star',
            'products.category'
        )
        ->where('products.stocks', '>', 0)
        ->where('products.status', 'Approved')
        ->where('product_name', 'like', "%$searchQuery%")
        ->whereIn('sellers.barangay', $uniqueNearbyBarangays)
        ->join('sellers', 'products.sellerID', '=', 'sellers.id')
        ->join('reviews', 'products.id', '=', 'reviews.productID')
        ->join('addresses as seller_address', 'sellers.id', '=', 'seller_address.userID')
        ->join('addresses as product_address', 'products.id', '=', 'product_address.userID')
        ->orderBy('products.price')
        ->orderByDesc('reviews.star') 
        ->orderBy('seller_location')
        ->paginate(50);

Answer 1

If you primarily want to order on seller_location you should call that orderBy first. Then it will sort all the records by seller_location, secondly by price and third by review.

->orderBy('seller_location')
->orderBy('products.price')
->orderByDesc('reviews.star') 

// Location 1, price: 5.00, review: 5 stars
// Location 1, price: 6.00, review: 5 stars
// Location 1, price: 6.00, review: 4 stars
// Location 2, price: 5.00, review: 5 stars
// Location 2, price: 6.00, review: 5 stars
// Location 2, price: 6.00, review: 4 stars