Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

DopeBacon's avatar

How do I count the number of listings that are associated with a certain city in a pivot table?

I have three tables I am trying to work with, cities, practiceAreas (categories) and lawyers; with 2 pivot tables cities_lawyers, lawyers_practice_areas. Here are the migration files for each of them:

   Schema::create('lawyers', function (Blueprint $table) {
           $table->increments('id');
           $table->string('first_name', 50);
           $table->string('middle_name', 50)->nullable();
           $table->string('last_name', 50);
           $table->string('email', 256)->unique();
           $table->string('phone', 15);
           $table->string('website',200);
           $table->string('company',100);
           $table->date('licensed_since');
           $table->timestamps();
     });

     Schema::create('cities', function (Blueprint $table) {
         $table->increments('id');
         $table->string('name', 16);
         $table->integer('zip');
         $table->string('state', 2);
         $table->decimal('lat', '8', '6');
         $table->decimal('long', 10, 6);
         $table->string('slug')->unique();
         $table->timestamps();
     });


      Schema::create('practice_areas', function (Blueprint $table) {
           $table->increments('id');
           $table->string('practice_area', 200);
           $table->string('slug')->unique();
     });

     /*------------------------ Pivot Tables ------------------------*/

     Schema::create('city_lawyer', function (Blueprint $table) {
           $table->integer('lawyer_id')->unsigned();
           $table->foreign('lawyer_id')->references('id')->on('lawyers');
           $table->integer('city_id')->unsigned();
           $table->foreign('city_id')->references('id')->on('cities');
     });

     Schema::create('lawyer_practice_area', function (Blueprint $table) {
           $table->integer('lawyer_id')->unsigned();
           $table->foreign('lawyer_id')->references('id')->on('lawyers');
           $table->integer('practice_area_id')->unsigned();
           $table->foreign('practice_area_id')->references('id')->on('practice_areas');
     });

Due to some cities having multiple zip codes, the cities table has multiple rows with the same city & state names/slug.

I would like to list out the city names in the order of the cities with the highest amount to the cities with the lowest amount of lawyer listings in the database. What would a query look like with Elloquent that selects all cities with lawyer listings attached via their many-to-many relationship and order them by desc amount of listings?

0 likes
2 replies
DopeBacon's avatar

Here is the closest that I have gotten, but I'm not sure how to order it by the categories that have the most listings (lawyers)

City::query()->has('lawyers')->orderByDesc('id')->get();
edoc's avatar
City::has('lawyers')->withCount('lawyers')->orderBy('lawyers_count', 'desc')->get();

@DopeBacon

Please or to participate in this conversation.