I have three models:

• Product
• Price
• Currency

One product can have many prices (prices change depending on the date), and the price can be in multiple currencies.

Here's the example schema:

Product

|-------------------------------|
| id   | name                   |
|-------------------------------|
| 1    | Hat                    |
| 2    | T-shirt                |
|-------------------------------|

Price

Here, you can see that a single product (Hat) can have multiple prices, in different currencies and each price being valid from a given date.

At the time of writing (26 Feb 2015), the current price for product 1 (hat) should be £12 or $11.

|-------------------------------------------------------|
| id   | product_id | currency_id | price | valid_from  |
|-------------------------------------------------------|
| 1    |     1      |    1        | 10    | 2014-01-01  |
| 2    |     1      |    1        | 12    | 2015-01-01  |
| 3    |     1      |    1        | 14    | 2016-01-01  |
| 4    |     1      |    2        | 8     | 2014-01-01  |
| 5    |     1      |    2        | 10    | 2015-01-01  |
| 6    |     1      |    2        | 11    | 2015-02-01  |
|-------------------------------------------------------|

Currency

|-------------------------------|
| id   | name                   |
|-------------------------------|
| 1    | GBP                    |
| 2    | USD                    |
|-------------------------------|

What I'm trying to do

I want to be able to get all of the products, with the current prices in a given currency.

I've set up the models as follows:

User

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

Price

public function currency()
{
    return $this->belongsTo('App\Currency', 'currency_id', 'id');
}

public function product()
{
    return $this->belongsTo('App\Product', 'product_id', 'cat_id');
}

Currency

No relationships defined.

What I've Tried

$products = $this->product->with([
        'prices' => function($query) use ($currency) {

            $query->where('valid_from', '<=', date('Y-m-d'));
            $query->orderBy('valid_from', 'DESC');

                $currency = $this->currency->where('name', strtoupper($currency))->limit(1)->get();
                $query->where('currency_id', $currency[0]->id);


        },
        'prices.currency'
    ])->get();

Which pretty much works, but there's no way to return only one price (adding a limit on the $query doesn't work)

You need to change relationship into belongsToMany