hasMany relationship needs to include where?

So I have a "Partner" model that has several hasMany relationships in it's model class.

public function families()
    {
        return $this->hasMany(Family::class);
    }

I call it like so ...

$partners = Partner::where("public", 1)->where("active", 1)
            ->withSum('families as people', 'people')
            ->orderBy('partners.site', 'ASC')->get();

My question is, how can I get only families that have a (variable) year value in families ( not Partner)?

Ideally the year value can be changed and produce different results.

LaryAI

1 year ago

Level 58

To filter the families relationship based on a year value, you can use the whereHas method in combination with the with method. This allows you to add constraints to the related models.

Here's how you can modify your query to include only families that have a specific year value:

$year = 2022; // Replace this with the variable year value you want to filter by

$partners = Partner::where("public", 1)
    ->where("active", 1)
    ->whereHas('families', function ($query) use ($year) {
        $query->where('year', $year);
    })
    ->with(['families' => function ($query) use ($year) {
        $query->where('year', $year);
    }])
    ->withSum('families as people', 'people')
    ->orderBy('partners.site', 'ASC')
    ->get();

Explanation:

whereHas: This method filters the Partner models to include only those that have related Family models with the specified year.
with: This method ensures that only the Family models with the specified year are eager-loaded.
use ($year): This allows the closure to access the $year variable.

By using both whereHas and with with the same condition, you ensure that:

Only Partner models with Family models matching the year are retrieved.
Only the Family models matching the year are included in the eager-loaded results.

This approach allows you to dynamically change the year value and get the desired results.

tisuchi

1 year ago

Level 70

@yabdab I believe the easy solution will be whereHas() as @laryai suggested!

yabdab

1 year ago

Level 3

Thanks. I ended up using this and it works well so far...

$partners = Partner::where("active", 1)->where("active", 1)
            ->withCount(
                ['families' => fn($query) => $query->where('families.year', $this->year)->where('families.active', 1)],
                'families',
            )
            ->get();

Please or to participate in this conversation.