[FIXED] How to query count on the pivot table in Laravel eloquent

Issue

I have two data tables in database. One is user table, other one is property table. User can save as many properties as they want. Also, one property can be save by multiple users. So there is a pivot table which has the property_id and user_id in it for a record. Now I want to query the popular properties. All properties that is saved by more than 100 users are popular properties. How would I do that? Something like I am doing

public function popularProperties(Request $request)
    {
       $id = $request->id;
        return Property::where('saved'->count(),'>',100)->with([
            'paymentPlan' => function ($query){
                $query->select('property_id','all_in_one_go','down_payment','minimum_installment_per_month');
            },
            'city',
            'propertyType' => function ($query){
                $query->select('id','name');
            },
            'user' => function ($query){
            $query->select('id','name','email');
        },
        'images' => function($query){
            $query->select('imageable_id','url');
        }
        ])->withCount('likes')->get();
    }

Solution

The following code selects all property's with > 100 count. Inside the whereIn is a subquery that selects all property IDs that exist > 100 times. Note that you have to fill in the correct Table and column names

$propertys = Property::whereIn('id', function ($q){
            $q->select('property_id')
            ->from('user_property')
            ->groupBy('property_id')
            ->havingRaw('COUNT(*) > 100');
})->get();

Answered By - Merijndk

Answer Checked By - Mildred Charles (PHPFixing Admin)