How to work with JSON data in a consistent way with Eloquent
Laravel Brussel - 2018-03-06
Who am I?
Grummfy
Jonathan Van Belle
@Grummfy
@Grummfy@mamot.fr
me@grummfy.be
github.com/grummfy
Open source contributor: atoum, hoa ...
Rust meetup organizer
Summary
- Why json?
- Model
- Mysql vs MariaDb vs Postgresql
- Searching
- Virtual column
- Demo time!
Why json ?
- Simple to create, read, manipulate
- Play nice with Value object
Why json ?
VS
Simpler database structure
NoSql...
DB vs json
- well known
- tools up-to-date
- handle complex case
- constraint
- performances
- good for a lot of read
- easy to join
- json is well known
- easier for a junior
- good for simple case
- db: hard to query on
- php: simple to manipulate
MySql vs MariaDb vs Postgresql
| MySql > 5.7 | MariaDb > 10.2 | Postgresql > 9.2? | |
|---|---|---|---|
| json column | ✔ | ✘ | ✔ |
| jsonb column | ✘ | ✘ | ✔ |
| search | ✔ | ✔ | ✔ |
| virtual col | ✔ | ✔ | ✔ |
| short syntax | ✔ | ✔ | ✔ |
Model
- attribute
- cast -> json
- grummfy/eloquent-extended-cast-model
Model: attribute
<?php
class Shop extends Eloquent {
protected $fillable = ['address'];
public function getAddressAttribute(string $value): Address {
$data = json_decode($value);
return new Address(
$data->street, $data->number,
$data->city, $data->zipCode,
$data->country);
}
public function setAddressAttribute(Address $address) {
$this->attributes['address'] = json_encode($address);
}
}
Model: cast
<?php
class Shop extends Eloquent {
protected $cast = ['address' => 'json'];
protected $fillable = ['address'];
public function getAddressAttribute(array $value): Address {
return new Address(
$data['street'], $data['number'],
$data['city'], $data['zipCode'],
$data['country']);
}
public function setAddressAttribute(Address $address): string {
$this->attributes['address'] = json_encode($address);
}
}
Model: extended cast
<?php
class Shop extends Eloquent {
use CastableModel;
protected $cast = ['address' => 'myAddress'];
protected $fillable = ['address'];
public function toMyAddress(Address $address): string {
return json_encode($address);
}
public function fromMyAddress(string $address): Address {
return Address::fromStdClass($this->fromJson($address, true));
}
}
Model: extended cast
<?php
class Contact extends Eloquent {
use CastableModel, JsonReadOnlyCollectionCastable;
protected $cast = ['phones' => 'jsonReadOnlyCollection'];
protected $fillable = ['phones'];
protected $castParameters = ['phones' => Phone::class];
}
Collection is not so simple...
<?php
public function addPhone(string $prefix, string $number): self {
if (is_null($this->phones)) {
$this->phones = new ReadOnlyCollection();
}
$this->phones = $this->phones->push(new Phone($prefix, $number));
return $this;
}Searching
- Dedicated function
- Short syntax
- -> "address->city" "Brussels"
- ->> "address->>city" Brussels
- * "phones->'$[*].prefix'" [32, 33]
- Not always common to each DB
{"street": "test street", "number": 5, "city": "Brussels"}
[{"prefix":32, "number":"123456789"}{"prefix":33,"number":"6123456789"}]Searching... and more
- indexing
- merging
- aggregate operation
- constraint
- ...
Virtual column
- Column with generated value from other in the table
- virtual vs stored
- Can work with calculated value too
$table->string('street1')->virtualAs('address->>\'$.street\'');
$table->string('street2')->storedAs('address->>\'$.street\'');
Questions ?
- https://www.postgresql.org/docs/9.3/static/functions-json.html
- http://schinckel.net/2014/05/25/querying-json-in-postgres/
- https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/
- https://www.vividcortex.com/blog/2015/06/02/json-support-postgres-mysql-mongodb-sql-server/
- https://dev.mysql.com/doc/refman/5.7/en/json.html
How to work with JSON data in a consistent way with Eloquent
By Jonathan Van Belle
How to work with JSON data in a consistent way with Eloquent
How to work with JSON with eloquent
