Migrations

Overview Modern applications require robust data retrieval patterns that go beyond simple CRUD operations. When building a platform like ErgoDNC—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 Eloquent 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 ORM. * **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 ORM: 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. ```python 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. ```python $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`. ```python $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 MySQL 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: ```python 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: ```python ->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.