Building Advanced Office Listings with Laravel: Pagination, Distance Sorting, and Eloquent Filtering

Modern applications require robust data retrieval patterns that go beyond simple CRUD operations. When building a platform like

—a specialized marketplace for renting ergonomic office spaces—the listing engine serves as the heart of the user experience. This tutorial explores the implementation of a high-performance office listing endpoint using

Laravel

.

We focus on transitioning from a basic "return all" approach to a sophisticated, production-ready system. This includes implementing server-side pagination to handle large datasets, applying strict visibility rules to ensure users only see approved content, and building a multi-dimensional filtering system. Perhaps most critically, we address the challenge of geospatial sorting—allowing users to find the nearest workspace based on their current coordinates. By the end of this guide, you will understand how to orchestrate

relationships, local scopes, and raw SQL fragments to create a seamless search experience.

Prerequisites

To follow this guide, you should have a solid grasp of the following:

• PHP 8.x: Familiarity with modern PHP syntax, including arrow functions and typed properties.
• Laravel Framework: Understanding of
  MVC
  architecture,
  Migrations
  , and
  Eloquent
  .
• Testing Basics: Knowledge of
  PHPUnit
  or
  Pest
  for verifying API responses.
• SQL Knowledge: A basic understanding of SELECT statements and raw queries for geospatial calculations.

Key Libraries & Tools

• Laravel
  : The primary PHP framework used for routing, database interaction, and API responses.
• Eloquent
  : Laravel's database abstraction layer used for relationship management and query building.
• PHPUnit
  : The testing framework utilized to ensure listing logic remains intact during refactors.
• Laravel Valet
  : A minimalist development environment for macOS used to serve the application locally.

Code Walkthrough: The Office Listing Endpoint

1. Implementing Pagination and Visibility

We start by ensuring the API doesn't dump thousands of records at once. Using Laravel's native pagination is the most efficient way to handle growth. Simultaneously, we must enforce business rules: only offices that are "approved" by an admin and not "hidden" by the host should appear.

# Controller Logic
public function index()
{
    return Office::query()
        ->where('approval_status', Office::STATUS_APPROVED)
        ->where('hidden', false)
        ->latest('id')
        ->paginate(20);
}

By chaining where clauses, we create a strict filter. Using paginate(20) automatically injects metadata into the JSON response, including current_page, last_page, and total count. This allows frontend consumers to build navigation controls without extra backend effort.

2. Dynamic Filtering with the when Method

One of Laravel's cleanest features for APIs is the when() method. It allows us to apply query logic conditionally based on the presence of request parameters without messy if/else blocks.

$offices = Office::query()
    ->when(request('host_id'), function ($builder) {
        $builder->where('user_id', request('host_id'));
    })
    ->when(request('user_id'), function ($builder) {
        $builder->whereRelation('reservations', 'user_id', request('user_id'));
    });

In this snippet, whereRelation is a powerful shortcut. It allows us to filter the main Office query based on attributes of a related model—in this case, finding offices where a specific user has made a reservation.

3. Loading Relationships and Aggregates

To avoid the N+1 query problem, we must eager-load our relationships. We also need to show how many "active" reservations an office has, which requires a conditional withCount.

$offices->with(['images', 'tags', 'user'])
    ->withCount(['reservations' => function ($query) {
        $query->where('status', Reservation::STATUS_ACTIVE);
    }]);

This ensures that each office in the list includes its host details, images, and tags in a single database round-trip. The reservations_count attribute is dynamically added to each model instance.

4. Advanced Geospatial Sorting

Sorting by distance is a common requirement for location-based services. Since

and

PostgreSQL

handle geography differently, we use a raw SQL snippet (often called the Haversine formula) within an Eloquent scope.

Inside the Office model, we define a local scope:

public function scopeNearestTo($query, $lat, $lng)
{
    return $query->select('*')
        ->selectRaw(
            "(6371 * acos(cos(radians(?)) * cos(radians(lat)) * cos(radians(lng) - radians(?)) + sin(radians(?)) * sin(radians(lat)))) AS distance",
            [$lat, $lng, $lat]
        )
        ->orderBy('distance');
}

This scope calculates the distance in kilometers between the user's coordinates and the office's stored latitude and longitude. We then chain this into our controller:

->when(request('lat') && request('lng'), function ($builder) {
    $builder->nearestTo(request('lat'), request('lng'));
}, function ($builder) {
    $builder->orderBy('id', 'asc');
})

Syntax Notes

• Scope Naming: Local scopes always begin with the scope prefix in the model but are called without it in the controller. This is a core Laravel convention.
• Query Builder Bindings: When using selectRaw, always pass variables as a second argument array. This prevents SQL injection by using prepared statements.
• whereRelation: This method is a more readable alternative to whereHas. It’s perfect for simple column checks on related tables.

Practical Examples

• Marketplace Search: A user visits the app and grants location access. The API uses the lat/lng parameters to show them the closest ergonomic desks first.
• Host Dashboard: A host wants to see only their listings. The frontend sends the host_id, triggering the conditional filter we built in step 2.
• User History: A worker wants to find a desk they liked in the past. By passing the user_id to the office listing, the API returns only spaces they previously reserved.

Tips & Gotchas

• Select Conflicts: When using selectRaw for distance, remember to also include select('*'). If you don't, Laravel might only return the distance column and ignore the rest of the model's data.
• Indexing: Geospatial queries can be slow on large tables. Ensure you have indexes on your lat and lng columns, or consider using database-specific spatial extensions like PostGIS for extreme scale.
• Testing Coordinates: When writing tests for distance sorting, use real-world coordinates from a tool like
  Google Maps
  . This verifies that your math actually works in a real-world scenario rather than just checking if a query runs.