Back

Working with spatial columns in Eloquent model


Abel posted 1 week ago

Hi,

I'm working with Laravel 5.5 and MySQL 5.7 and I have a table that stores travels data with origin and destination in two spatial columns (POINT type).

I'm trying to get this fields working with Eloquent in a transparent way, I've managed how to transform the longitude and latitude to model attributes on retrieving the data from database overriding newQuery() method in my Model:


use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Travel extends Model
{
    public function newQuery()
    {
        return parent::newQuery()->select('id',
            DB::raw('ST_X(origin) AS origin_x, 
                    ST_Y(origin) AS origin_y, 
                    ST_X(destination) AS destination_x, 
                    ST_Y(destination) AS destination_y'));
    }
}

Now I'm trying to figure out how to do the same for $travel->save(), because I need the insert or update statement to always use the MySQL POINT(x,y) function.

Do you have any idea on how to do it in a good way?

Thanks in advance!

Matteo Rigon replied 1 week ago

You can use Eloquent events https://laravel.com/docs/5.5/eloquent#events

class Travel extends Model
{
    ...

    public static function boot() {
	parent::boot();

	static::creating( function( $model ) {
		$model->formatMyPoint();
	} );

	static::updating( function( $model ) {
		$model->formatMyPoint();
	} );
    }

    protected function formatMyPoint() {
	$this->my_point = DB::raw('POINT(' . $this->x . ' ' . $this->y . ')');
    }
}
Abel replied 1 week ago

Thanks for your reply!

I've used the saving event, that is fired on create and update and it works fine, but I need to avoid Eloquent trying to save this->x and this->y to database. I think I can do it with unset(this->x/y) but I will prefer a cleaner way.

Regards.


Sign in to participate in this thread!



We'd like to thank these amazing companies for supporting us