8: ORM

Phlo ships with a powerful built-in ORM that lets you define database tables as classes. Models can be defined quickly via columns or in full via a declarative schema. Records are treated as instances, with support for props, methods, views, relations and multiple database engines.

8.1: Basics

An ORM model is a .phlo file with:

• @ class: the name of the model (and table)
• @ extends: model
• static table and columns or schema
• Optional: relations (parent, child, many)
• Props, methods and views operate per record instance

Example:

@ class: user
@ extends: model

view => $this->name

static table = 'users'
static columns = 'id,name,email,active,created'

8.2: Defining models

7.2.1 Flat with columns (quick and lightweight)

Use columns for simple tables:

@ class: shipment
@ extends: model

view: $this->destination ($this->user)

static table = 'shipments'
static order = 'changed DESC'
static columns = 'id,user,destination,costs,valid,weight,shipped,created,changed'
static objParents = ['user' => 'user']

@ class: user
@ extends: model

view => $this->name

static table = 'users'
static order = 'changed DESC'
static columns = 'id,name,email,level,active,created,changed'
static objChildren = ['shipments' => 'shipment']

7.2.2 With schema and field(...) (rich and declarative)

With schema you define fields, relations and UI in one place:

@ class: shipment
@ extends: model

view: $this->destination ($this->user)

static table = 'shipments'
static schema => arr (
    id: field (type: 'token', length: 4, title: 'ID'),
    destination: field (type: 'text', required: true, search: true),
    user: field (type: 'parent', obj: 'user', required: true),
    costs: field (type: 'price', prefix: '€ '),
    valid: field (type: 'bool'),
    attachments: field (type: 'child', obj: 'attachment', list: true),
)

@ class: user
@ extends: model

view => $this->name

static table = 'users'
static schema => arr (
    id: field (type: 'token'),
    name: field (type: 'text', search: true, required: true),
    email: field (type: 'email', required: true),
    shipments: field (type: 'child', obj: 'shipment'),
    groups: field (type: 'many', obj: 'group', table: 'user_groups'),
)

schema is especially powerful in combination with PhloCMS, but works standalone too.

8.3: CRUD

Fetching:

$user = user::record(id: 1)
$list = shipment::records(order: 'created DESC')

Creating:

$shipment = shipment::create(destination: 'Paris', user: 1)

Editing and saving:

$shipment->destination = 'Lyon'
$shipment->objSave

Deleting:

shipment::delete('id=?', $shipment->id)

• record(...) → single record (or null)
• records(...) → array of records (class instances)
• create(...) → insert + instant fetch
• objSave → save the instance (insert/update depending on id)
• delete(...) → static delete with an SQL where clause

8.4: Relational navigation

Relations are available as properties:

Many-to-many

type: many uses a pivot table:

groups: field (
    type: 'many',
    obj: 'group',
    table: 'user_groups',
)

Navigation:

$user = user::record(id: 1)
foreach ($user->groups as $group)
  echo $group->title

Relations are batch-loaded for performance. There are no cross-DB joins; each class loads from its own engine.

8.5: Instance dynamics

Every record is a real instance of your model class. You can use props, methods and views to add virtual fields, computations or representations:

@ class: shipment
@ extends: model

prop summary => $this->destination.' ('.$this->user.')'
method tax => $this->costs * 0.21

view:
<p>$this->summary</p>
<p>$this->tax</p>

Usage:

$shipment = shipment::record(id: 'AB12')
echo $shipment->summary
echo $shipment

Using a record as a string invokes its view representation.

Props and methods always operate on the record instance, never statically.

8.6: Filtering and queries

All query methods accept named arguments and SQL-like filters:

shipment::records(destination: 'Paris')
shipment::records(where: 'valid=1 AND weight>10')
shipment::pair(columns: 'id,destination')

Supported: where, order, group, joins, caching and schema-aware columns.

8.7: Caching and performance

The ORM uses internal buffers (objRecords, objLoaded) for relational lookups and optional APCu caching via:

static objCache = true

Or a number of seconds:

static objCache = 600

Records and relations are loaded in batches. Use records() for bulk selections instead of record() in loops.

8.8: Multiple engines

Phlo supports multiple backends via prop DB. The default is %MySQL, but you can set any engine per model.

SQLite

prop DB => %SQLite(data.'users.db')

@ class: notes
@ extends: model

prop DB => %SQLite(data.'notes.db')
static table = 'notes'
static columns = 'id,title,body'

PostgreSQL

prop DB => %PostgreSQL

@ class: invoices
@ extends: model

prop DB => %PostgreSQL
static table = 'invoices'
static columns = 'id,customer_id,total,created'

Tables on different engines can be combined in relations; each class fetches its own data.

/data/creds.ini

For engines such as MySQL and PostgreSQL, place your credentials in:

/data/creds.ini

[mysql]
host     = localhost
database = db_name
user     = db_user
password = db_password

[postgresql]
host     = localhost
database = my_pg_db
user     = pg_user
password = pg_pass

Phlo loads these automatically via %creds->....

8.9: Opt-in features: audit, validation, custom PK

An @ extends: model gives you CRUD + identity map + relations + soft delete out of the box. Three additional opt-ins are enabled per model with a static flag.

X.9.1 Audit log

static objAudit = true

From then on, every create, objSave (update) and delete is logged to an audit table via the security/audit resource:

Setup:

1. Add security/audit to the resources in data/app.json.
2. Import the schema SQL once: mysql <database> < /srv/phlo/resources/security/audit.sql.

Excluding sensitive fields:

method afterCreate => %audit->log($this, 'create', [], (array)$this, exclude: ['password_hash'])

Toggle per environment, dev only:

static objAudit => debug

Or release only:

static objAudit => !debug

(debug is the runtime constant from phlo_app(debug: ...).)

X.9.2 Validation

static objValidate = true

Before create(), Phlo runs the matching objValidate($value) for each field in static schema(). On errors: create() returns null, with errors available via Class::objErrors():

if (!user::create($args)){
    return apply(errors: user::objErrors())
}

Field rules in schema():

static schema => arr (
    email:  field (type: 'email', required: true),
    name:   field (type: 'text', length: 100, required: true),
    slug:   field (type: 'text', pattern: '^[a-z0-9-]+$'),
    status: field (type: 'text', enum: ['draft', 'sent', 'paid']),
)

Custom field validation: override method objValidate($value) in your own field subclass.

X.9.3 Custom primary key

The default is id (auto-increment integer). Override it for other PKs:

static idColumn = 'sku'
static idType   = 'string'

Effect:

• The identity map uses sku as its key
• Class::record(sku: 'XYZ-123') (not id:)
• With create(): you supply the PK value yourself (no auto-increment)
• $record->id does not work, use $record->sku

X.9.4 Combining

The three opt-ins are independent and can be combined:

@ class: giftcard
@ extends: model

static objAudit    = true
static objValidate = true
static idColumn    = 'sku'
static idType      = 'string'

Every feature is off by default (false, 'id', 'int'). A model without opt-ins stays a plain model.

8.10: Function overview

8.11: Best practices

• Use columns for quick, simple models.
• Use schema for rich definitions and CMS integration.
• Define a view for string representations.
• Use props for virtual fields.
• objSave instead of save.
• Use records() for bulk loads.
• Separate models per engine with prop DB.
• Keep credentials in /data/creds.ini.
• Keep models declarative; put logic in props/methods.