mysql - Laravel query builder doesn't return all prices based on role = customer. Just return 1 price -

i'm designing application retailer can add product initial price (store in products table shown example), customers can claim price of product purchased retailer (this information stores in prices table shown example). retailer can update / reclaim price inside prices table too. , customers can reclaim price of product on , on again.

so, have 2 roles of users called retailer , customer. i'm using entrust role package default relationship between role , user in model. before explain next, here simple database design working example (feel free ask include):

=============== my database design sample ===============

table users

 __________________________ | id | email   | password | |-------------------------| | 1  | a@g.com | 123      | | 2  | b@g.com | 123      | | 3    c@g.com | 123      | | 4    d@g.com | 123      |  -------------------------- 

table roles

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

table role_user

 __________________  |id_user |  id_role|  |------------------|  |  1     |    1    |  -> a@gmail.com customer  |  2     |    2    |  -> b@gmail.com retailer  |  3     |    1    |  -> c@gmail.com customer  |  4     |    1    |  -> d@gmail.com 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 customer a@gmail.com on product 1 |2 |    2     |      1       |5.00   | -> price claimed retailer b@gmail.com on product 1 |3 |    1     |      1       |6.00   | -> price claimed previous customer a@gmail.com on product 1 |4 |    3     |      1       |5.00   | -> price claimed customer c@gmail.com on product 1 |5 |    2     |      1       |7.00   | -> price claimed previous retailer b@gmail.com on product 1 |6 |    3     |      1       |8.00   | -> price claimed customer c@gmail.com 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 tricky part here on how price of customers except retailer:

class productcontroller extends controller {  public function show($id)  {    $product = product::findorfail($id);      // query should return price claimed customers except retailer. problem is, return 1 row, first row output 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 return 1 row: price 10.00      /* should return collection can foreach statement. output should this:        10.00       6.00       5.00       7.00       8.00     */   }  } 

the query in controller above return prices claimed customers except retailer. problem is, return 1 row, first row output 10.00.

it should output prices claimed customers prices table below:

10.00 6.00 5.00 7.00 8.00

any idea?

update:

so far changed controller codes 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 , return correct values    } 

i have 1 small problem here: when loop through collection

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

i got error of errorexception in productcontroller.php line 72:

    undefined property: illuminate\database\eloquent\collection::$prices  

but relationship exist shown.

if looking answer correct query 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 $price)     {         echo $price->price;     }