Ad

Laravel Query Builder Doesn't Return All Prices Based On Role = Customer. Just Return 1 Price

I'm designing an application where a retailer can add a product with an initial price (store in a products table shown as example), then customers can claim the price of the product purchased from the retailer (this information stores in the prices table shown as example). The retailer then can update / reclaim the price inside the prices table too. and customers can reclaim the price of the product over and over again.

So, I have 2 roles of users called retailer and customer. I'm using Entrust Role package with the default relationship between role and user in the model. Before I explain next, here is my simple database design with all working example (feel free to ask for anything to include):

=============== MY Database Design with sample ===============

table users

 __________________________
| id | email   | password |
|-------------------------|
| 1  | [email protected] | 123      |
| 2  | [email protected] | 123      |
| 3    [email protected] | 123      |
| 4    [email protected] | 123      |
 --------------------------

table roles

  ______________
 |id |  slug    |
 |--------------|
 |1  | customer |
 |2  | retailer |
 ----------------

table role_user

 __________________
 |id_user |  id_role|
 |------------------|
 |  1     |    1    |  -> [email protected] is a customer
 |  2     |    2    |  -> [email protected] is a retailer
 |  3     |    1    |  -> [email protected] is a customer
 |  4     |    1    |  -> [email protected] is a customer
  ------------------

table price: (customer or retailer can claim 1 or more prices):

 _____________________________________
|id|  user_id |  product_id  | price |
|----------------------------|
|1 |    1     |      1       |10.00  | -> price claimed by a customer [email protected] on product 1
|2 |    2     |      1       |5.00   | -> price claimed by a retailer [email protected] on product 1
|3 |    1     |      1       |6.00   | -> price claimed by a previous customer [email protected] on product 1
|4 |    3     |      1       |5.00   | -> price claimed by a customer [email protected] on product 1
|5 |    2     |      1       |7.00   | -> price claimed by a previous retailer [email protected] on product 1
|6 |    3     |      1       |8.00   | -> price claimed by a customer [email protected] on product 1

Table products

 _____________________________________
|id      |  user_id| name     | Price
|-------------------------------------
|  1     |    1    | Milk     |  10.00
|  2     |    2    | Phone    |  12.33
|  3     |    1    | computer |  33.44
|  4     |    1    | Banana   |  33.22
--------------------------------------

=============== MY Model Relationship ===============

Price model relationship

class Price extends Model
{
  public function product()
  {
    return $this->belongsTo('App\Product');
  }

 public function user()
 {
   return $this->belongsTo('App\User');
 }
}

Product model relationship

class Product extends Model
{

  public function prices()
  {
    return $this->hasMany('App\Price');
  }
}

User model relationship //a user can claim 1 or more prices

class User extends Model
{
   public function prices ()
  {
    return $this->hasMany('App\Price');
  }
}

=============== MY Product Controller ===============

This is the tricky part here on how to get the price of all customers except retailer:

class ProductController extends Controller
{
 public function show($id)
 {
   $product = Product::findOrFail($id); 

   // This query should return all price claimed by customers except retailer. But the problem is, it only return 1 row, the first row which the output is 10.00.

   $query_customer =$product->prices()->whereHas('user', function ($q) {
        $q->whereHas('roles', function ($q) {
            $q->where('slug', 'customer');
        });
    });
    $latest_price_by_customer= $query_customer->value('price');

     dd($latest_price_by_customer); 
     //it just return 1 row: price 10.00

    /* It should return the collection that I can do foreach statement. The output should be like this:

      10.00
      6.00
      5.00
      7.00
      8.00

   */

 } 
}

The query in the controller above return all prices claimed by customers except retailer. But the problem is, it only return 1 row, the first row which the output is 10.00.

It should output all prices claimed by customers from the prices table like below:

10.00 6.00 5.00 7.00 8.00

Any idea?

Update:

So far I changed my controller codes from this:

   $product = Product::findOrFail($id); 
   $query_customer =$product->prices()->whereHas('user', function ($q) {
        $q->whereHas('roles', function ($q) {
            $q->where('slug', 'customer');
        });
    });
    $latest_price_by_customer= $query_customer->value('price');

     dd($latest_price_by_customer); 

to this:

    $product = Product::with('prices')->findOrFail($id);


    $product_query= $product->prices()->where('product_id', $id) ->whereHas('user', function ($q) {
        $q->whereHas('roles', function ($q) {
            $q->where('slug', 'customer');
        });
    })->select('price')->get();


    dd($product_query); //display collection and return the correct values
   }

I have one small problem here: When loop through the collection

    foreach($product_query->prices as $pr)
    {
       // dd($pr);
       // echo $pr->price . ' ___ ' ;
    }

I got an error of ErrorException in ProductController.php line 72:

    Undefined property: Illuminate\Database\Eloquent\Collection::$prices 

but the relationship is exist as shown.

Ad

Answer

If anyone looking for the answer this is the correct query that returns collection instead of 1 row:

$product = Product::with('prices')->findOrFail($id);
$product_query= $product->prices()->where('product_id', $id) ->whereHas('user', function ($q) {
        $q->whereHas('roles', function ($q) {
            $q->where('slug', 'customer');
        });
    })->select('price')->get();

    foreach($product_query as $price)
    {
        echo $price->price;
    }
Ad
source: stackoverflow.com
Ad