Get the count of a table's field based off another table's id field.

I currently have 3 tables. 1st is Organization table which stores the Organization details made by a certain User. User table is for users. 3rd is Favorite Organizations table which is when a user follows a organization the organization_id and user_id is added to favourite_organization table. I want to count the total number of times a organization_id has been stored on favourite_organization table and send it alongside the organization array. Here is what i have done upto now.

public function favouriteOrganizationList(){
		//Grabbed the organization_id that are present as the logged in user.
        $fav_org_list = DB::table('favourite_organizations')->where('user_id', Auth::id())->pluck('organization_id');
		//Fetched the organization details based on the above ids.
        $fav_organization_details = DB::table('organizations')->with('organizationFollowers')->whereIn('id', $fav_org_list)->get();
        return $fav_organization_details;
    }

If possible i want to send the count of the organization in the favourite_organization table inside the $fav_organization_details array.

Armani

5 years ago

Level 17

You can use withCount method:

https://laravel.com/docs/8.x/eloquent-relationships#counting-related-models

Subit

5 years ago

Level 1

Thanks a lot i did this

$fav_organization_details = Organization::withCount('organizationFollowers')->whereIn('id', $fav_org_list)->get();

This was my Organization model's function:

public function organizationFollowers(){
        return $this->hasMany('App\Models\FavouriteOrganization', 'organization_id');
    }

CorvS

5 years ago

Best Answer

Level 27

@subit If you define the relationship on the User model you can simplify it:

Auth::user()->favoriteOrganizations()->withCount('organizationFollowers')->get();

That way you don't have to query the database twice.

1 like

Please or to participate in this conversation.