Ebay API Question posted in
The official documentation of all the APIs offered by Ebay can be found here.

Ebay API – Laravel eloquent join with min() and get first result of relationship

I have two tables named products and merchants.
products table schema:

id name slug
1 Product product
2 Product 2 product-2

merchants table schema:

id store price mrp product_id
1 Amazon 99 120 1
2 Amazon 149 179 2
3 Ebay 80 120 1

Each product can have multiple merchants and are related using hasMany relationship.
Now I want to fetch both products to show on homepage BUT I only need is one merchant HAVING the lowest price.

I tried using join where price is min()

Product::leftJoin('merchants', function ($q) {
         $q->on('products.id', '=', 'merchants.product_id')
           ->where('merchants.price', '=', DB::raw("(select min(`price`) from merchants)"));
})->get();

and the output is:

[{
    "id": 3, //ID gets replaced by merchant id
    "name": "Product",
    "slug": "product",
    "store": "Ebay",
    "price": 80,
    "mrp": 120
}, {
    "id": null, //ID is null
    "name": "Product 2",
    "slug": "product-2",
    "store": null, //Should be Amazon
    "price": null, //Should be 149
    "mrp": null //Should be 179
}]

It shows first product properly, only joining merchant with minimum price but values of other products is coming null.
What I am missing here?

Tags:

2

Answers


  1. 0

    You’re selecting the lowest price of all merchants. Instead, you want the lowest price possible for your product.

    Product::leftJoin('merchants', function ($q) {
         $q->on('products.id', '=', 'merchants.product_id')
           ->havingRaw('merchants.price = ?', [ DB::raw("min(`merchants.price`)") ]);
})->get();

    or:

    Product::leftJoin('merchants', function ($q) {
         $q->on('products.id', '=', 'merchants.product_id')
           ->where('merchants.price', '<', '`price')
           ->orderByDesc('merchants.price')
           ->limit(1);
})->get();
    Login or Signup to reply.
  2. 0

    If you have eloquent models defined properly. You could try the below.

    Add an extra relation function to your product model.

    public function cheapMerchant() {
    return $this->hasOne(Merchant::class, 'product_id')->orderBy('price', 'asc');
}

    Then in your controller, access the data by executing.

    Product::with('cheapMerchant')->get();

    This will give you a cheapMerchant relation object.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search