Select through intermediate table in Laravel

I have three tables:

1. sites table (primary table)
2. listings table (it has sites_id as foreign key)
3. news table (it has listing_id as foreign key)

How to define a relation in Site Model to fetch all "news" related to specific site. I have tried the following but it’s not working and returns 0 count.

$news = $site->news()->count();

public function news()
    {
        return $this->hasManyThrough(
            News::class,
            Listing::class,
            'id',
            'id',
            'listing_id',
            null,
        );
    }

>Solution :

To define a relation in the Site model to fetch all related news through listings, you need to correctly set up the hasManyThrough relationship. The hasManyThrough relationship is used to define a "has-many-through" relationship where a model is related to another model through an intermediate model.

Here is the correct setup for the hasManyThrough relationship in your Site model:

Site Model: Define the hasManyThrough relationship to fetch news through listings.

public function news()
{
    return $this->hasManyThrough(
        News::class,       // The final model we want to access
        Listing::class,    // The intermediate model
        'site_id',         // Foreign key on the Listing model
        'listing_id',      // Foreign key on the News model
        'id',              // Local key on the Site model
        'id'               // Local key on the Listing model
    );
}

Listing Model: Ensure the site_id column is set as a foreign key.

public function site()
{
    return $this->belongsTo(Site::class);
}

News Model: Ensure the listing_id column is set as a foreign key.

public function listing()
{
    return $this->belongsTo(Listing::class);
}

Fetching the News Count, Now you can fetch the news count related to a specific site as follows:

$site = Site::find($siteId);
$newsCount = $site->news()->count();

Here’s a detailed breakdown of the parameters used in the hasManyThrough method:

• News::class: The final model we want to access (News).
• Listing::class: The intermediate model (Listing).
• ‘site_id’: The foreign key on the Listing model that links it to the
  Site model.
• ‘listing_id’: The foreign key on the News model that links it to the
  Listing model.
• ‘id’: The local key on the Site model (primary key of the Site
  model).
• ‘id’: The local key on the Listing model (primary key of the Listing
  model).

Ensure the foreign keys and primary keys in the hasManyThrough method match your actual database schema.

With this setup, $site->news()->count() should correctly return the count of news items related to a specific site.