CODEWITHSUNDEEP
laraveleloquent
Fizk
Fizk

Reputation: 1114

Eloquent select with() based on foreign key

I have a table with user data (users) and a table with prices (prices). My prices table can contain multiple prices pr. user since I want to keep historical data.

I've defined my relation as a one-to-one 

$this->hasOne("App\Model\Price","userid","id")->orderBy("id","desc")->take(1);

to allow me to see the users current price.

What I want to do now, is to select every user that has a current price of 100, but how do I do this? I know I could go for a left join, but as I read the documentation, it should be possible without a left join.

I've built a pseudo-query to explain what I'm after; User::with("price")->where("prices.price","100")->get();

I've read through the documentation (Eloquent: Querying relationships), but that doesn't seem to be useful to my question.

I've also read several questions here on SO but unfortunately to no avail.

Upvotes: 0

Views: 2226

Answers (2)

Amit Gupta
Amit Gupta

Reputation: 17658

You can use the combination of whereHas() and with() as:

$users = User::whereHas("price", function($q) use ($currentPrice) {
               $q->where("price", $currentPrice);
            })
            ->with(["price" => function ($q) {
                $query->where("price", $currentPrice);
            })
            ->get();

Upvotes: 3

The Alpha
The Alpha

Reputation: 146191

You may try this:

$currentPrice = 100;
$users = User::whereHas('price', function($query) use ($currentPrice) {
    $query->where('price', $currentPrice); // price is the field name
})
->with("price")->get();

Since you have more than a single price for per user then you may also declare another relationship method to get all the price models instead of one and you may do it using something like this:

// In User model
public function prices()
{
    return $this->hasMany("App\Model\Price", "userid", "id");
}

In this case, with::price will give you the last single record and with::prices will give you all the related prices. So, if you want then you may write something like the following to get all users with their all related prices who has the (latest/current) price of 100:

$currentPrice = 100;
$users = User::whereHas('price', function($query) use($currentPrice) {
    $query->where('price', $currentPrice); // price is the field name
})
->with("prices") // with all prices
->get();

Upvotes: 4

Related Questions