Laravel query builder doesn't return all prices based on role = customer. Just return 1 price
<p>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.</p>

<p>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):</p>

<p>=============== <strong>MY Database Design with sample</strong> ===============</p>

<p>table <strong>users</strong></p>

<pre><code> __________________________
| id | email   | password |
|-------------------------|
| 1  | a@g.com | 123      |
| 2  | b@g.com | 123      |
| 3    c@g.com | 123      |
| 4    d@g.com | 123      |
 --------------------------
</code></pre>

<p>table <strong>roles</strong></p>

<pre><code>  ______________
 |id |  slug    |
 |--------------|
 |1  | customer |
 |2  | retailer |
 ----------------
</code></pre>

<p>table <strong>role_user</strong></p>

<pre><code> __________________
 |id_user |  id_role|
 |------------------|
 |  1     |    1    |  -&gt; a@gmail.com is a customer
 |  2     |    2    |  -&gt; b@gmail.com is a retailer
 |  3     |    1    |  -&gt; c@gmail.com is a customer
 |  4     |    1    |  -&gt; d@gmail.com is a customer
  ------------------
</code></pre>

<p>table <strong>price</strong>:
(customer or retailer can claim 1 or more prices):</p>

<pre><code> _____________________________________
|id|  user_id |  product_id  | price |
|----------------------------|
|1 |    1     |      1       |10.00  | -&gt; price claimed by a customer a@gmail.com on product 1
|2 |    2     |      1       |5.00   | -&gt; price claimed by a retailer b@gmail.com on product 1
|3 |    1     |      1       |6.00   | -&gt; price claimed by a previous customer a@gmail.com on product 1
|4 |    3     |      1       |5.00   | -&gt; price claimed by a customer c@gmail.com on product 1
|5 |    2     |      1       |7.00   | -&gt; price claimed by a previous retailer b@gmail.com on product 1
|6 |    3     |      1       |8.00   | -&gt; price claimed by a customer c@gmail.com on product 1
</code></pre>

<p>Table <strong>products</strong></p>

<pre><code> _____________________________________
|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
--------------------------------------
</code></pre>

<p>=============== <strong>MY Model Relationship</strong> ===============</p>

<p><strong>Price model relationship</strong></p>

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

 public function user()
 {
   return $this-&gt;belongsTo('App\User');
 }
}
</code></pre>

<p><strong>Product model relationship</strong></p>

<pre><code>class Product extends Model
{

  public function prices()
  {
    return $this-&gt;hasMany('App\Price');
  }
}
</code></pre>

<p><strong>User model relationship</strong> //a user can claim 1 or more prices</p>

<pre><code>class User extends Model
{
   public function prices ()
  {
    return $this-&gt;hasMany('App\Price');
  }
}
</code></pre>

<p>=============== <strong>MY Product Controller</strong> ===============</p>

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

<pre><code>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-&gt;prices()-&gt;whereHas('user', function ($q) {
        $q-&gt;whereHas('roles', function ($q) {
            $q-&gt;where('slug', 'customer');
        });
    });
    $latest_price_by_customer= $query_customer-&gt;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

   */

 } 
}
</code></pre>

<p>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.</p>

<p>It should output all prices claimed by customers from the prices table like below:</p>

<p>10.00
6.00
5.00
7.00
8.00</p>

<p>Any idea?</p>

<p>Update:</p>

<p>So far I changed my controller codes from this:</p>

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

     dd($latest_price_by_customer); 
</code></pre>

<p>to this:</p>

<pre><code>    $product = Product::with('prices')-&gt;findOrFail($id);


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


    dd($product_query); //display collection and return the correct values
   }
</code></pre>

<p>I have one small problem here: When loop through the collection</p>

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

<p>I got an error of ErrorException in ProductController.php line 72:</p>

<pre><code>    Undefined property: Illuminate\Database\Eloquent\Collection::$prices 
</code></pre>

<p>but the relationship is exist as shown.</p>