如何在Eloquent查询中添加计算生成的distance字段

Hey there! Let's get that calculated distance field showing up in your query results. You’re spot-on about the root cause—you need to explicitly include this computed field in your select statement so Eloquent knows to return it. Here are a few practical ways to implement this:

Option 1: Use selectRaw with the Haversine Formula (for latitude/longitude calculations) #

If you’re calculating distance based on geographic coordinates, the Haversine formula is a reliable choice. This example pulls all your model’s fields plus the computed distance:

// Replace with your actual model, coordinate fields, and target location
$targetLat = 40.7128; // Example target latitude
$targetLng = -74.0060; // Example target longitude

$results = YourModel::selectRaw('*, (
    6371 * acos(
        cos(radians(?)) * cos(radians(latitude)) * cos(radians(longitude) - radians(?)) +
        sin(radians(?)) * sin(radians(latitude))
    )
) AS distance', [$targetLat, $targetLng, $targetLat])
->get();

• 6371 is the Earth’s radius in kilometers; use 3956 if you need miles instead.
• Replace latitude and longitude with the actual column names in your table.
• The ? placeholders prevent SQL injection, which is crucial for safe queries.

Option 2: Add the Calculated Field with addSelect #

If you already have a specific set of fields you’re selecting, use addSelect to append the distance calculation without rewriting your entire select clause:

use Illuminate\Support\Facades\DB;

$results = YourModel::select('id', 'name', 'latitude', 'longitude') // Your existing selected fields
->addSelect(DB::raw('(
    6371 * acos(
        cos(radians(?)) * cos(radians(latitude)) * cos(radians(longitude) - radians(?)) +
        sin(radians(?)) * sin(radians(latitude))
    )
) AS distance'), [$targetLat, $targetLng, $targetLat])
->get();

Option 3: Use Database Spatial Functions (MySQL 5.7+/PostgreSQL) #

If your database supports spatial functions, you can simplify the query. For MySQL, ST_Distance_Sphere computes the distance between two points directly:

$results = YourModel::selectRaw('*, ST_Distance_Sphere(
    point(longitude, latitude),
    point(?, ?)
) / 1000 AS distance', [$targetLng, $targetLat])
->get();

• ST_Distance_Sphere returns distance in meters, so dividing by 1000 converts it to kilometers.

Key Takeaway #

No matter which method you choose, the critical step is explicitly defining the distance alias in your select statement. Eloquent only returns fields that are explicitly included in the query—unlike raw SQL, it won’t automatically pick up computed fields unless you tell it to.

内容的提问来源于stack exchange，提问作者Chaibi Alaa