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
Made with Slides.com