I have some models that use geospatial fields like POINT
, POLYGON
or MULTIPOLYGON
. I would like to tell my model to process these attributes in a special way, for me to get the desired model attributes set.
Example:
Every regular Model::find()
or other Eloquent method should apply some custom code before storing or after retrieving a database value.
$area->surface
is a POLYGON
field in MySQL, but in my model class I would like to handle $area->surfare
as an array of points.
On SELECT
I would therefore like to 1) fetch the value using a raw expression to get a text representation of the value, and 2) go through some custom PHP code to convert the WKT string into an array.
On INSERT/UPDATE
I would like to take the attribute value (an array) and 1) convert it into a WKT string, whereafter 2) it's written to the databse using a DB raw statement that stores the value.
I'd like to set this on a field-basis, not as special get/set functions for each field, and not in the controllers - because I have many geosptial fields.
Is there a way to achieve this in Laravel?
(A more abstract version of the same question, is how I can create code that manipulates attribute values for the actual SQL queries, rather than just some value-based manipulation via mutators & accessors)
UPDATE:
Looking deeper into the Laravel Doc and API, I found that maybe the Eloquent::newQuery()
method is what I need to manipulate? Would that be used for any query regardless if SELECT
, INSERT
or UPDATE
?
We have now solved this generically for all models by extending our base model with the following functionaly:
Here is an excerpt from the base model we now use:
/**
* The attributes that hold geometrical data.
*
* @var array
*/
protected $geometry = array();
/**
* Select geometrical attributes as text from database.
*
* @var bool
*/
protected $geometryAsText = false;
/**
* Get a new query builder for the model's table.
* Manipulate in case we need to convert geometrical fields to text.
*
* @param bool $excludeDeleted
* @return \Illuminate\Database\Eloquent\Builder
*/
public function newQuery($excludeDeleted = true)
{
if (!empty($this->geometry) && $this->geometryAsText === true)
{
$raw = '';
foreach ($this->geometry as $column)
{
$raw .= 'AsText(`' . $this->table . '`.`' . $column . '`) as `' . $column . '`, ';
}
$raw = substr($raw, 0, -2);
return parent::newQuery($excludeDeleted)->addSelect('*', DB::raw($raw));
}
return parent::newQuery($excludeDeleted);
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With