I think that Eloquent is too abstracted and feels bad once you go beyond single-table CRUD objects, Doctrine is too verbose, and neither of them are feasible to put into legacy projects.
PHP has PDOStatement::fetchObject() and I like to write my SQL manually, so I ended up developing a design pattern of sorts for reading data out of the database and now I'm formalising that into PORM
PORM works more like a "framework" by providing the mechanisms for object mapping and loading associated records, but let's you write your own SQL and whatever logic for loading those associations and casting data types.
composer require mattdwyercool/porm
- You get clean, vanilla PHP objects that you can do whatever you want with.
- The class properties don't have to match a DB table - you can do JOINs and fancy SELECTs.
- Protection from N+1 queries by default.
- You control the type casting and property visibility.
PORM supports PDO and MySQLi but leaves it up to you to manage them - you can use a Service Container, a global variable, or even the procedural mysqli_xxx() functions.
PORM includes a pair of optional helper facades that you can initialise during your app's bootstrapping with:
- PDO:
new \PORM\Helpers\PDO\DB( new PDO( ... ) ) - MySQLi:
new \PORM\Helpers\MySQLi\DB( new mysqli( ... ) )
I haven't written proper docs yet so have a look at the examples/ folder for a demo or read below for a crash course.
But in a nutshell: define a class with properties like you normally would, then make it use \PORM\Model and define some static getter methods. Those getter methods should build a PDOStatement object whose columns match up with your class properties, and then call static::one() for a single-record getter or static::many() for an array.
one() and many() will call PDOStatement::fetchObject( static::class ) on the statement you give it, then scan the class for any relationships you've defined (see below). PORM unset()'s those relationship properties so that you keep IDE autocomplete, and relies on __get() to lazy-load them when called upon. When that happens, PORM will also load the related records for the "siblings" in your original static::many() call to avoid N+1 queries.
Create a plain PHP class with your properties like:
use PORM\Model;
final class Person
{
use Model;
public int $id;
public string $name;
public string $email;
}That's it, but keep the data types as simple scalars (see below for non-scalar Type Casting).
You can define whatever model getters you need by adding static methods to your class that creates a PDOStatement or mysqli_result object and passes it to static::one() for single-record getters or static::many() for an array.
static::one() returns an instance of your class or null if the SQL query returned empty:
final class Person
{
public static function get( int $id ): ?static
{
$stmt = DB::select( <<<SQL
SELECT
p.`id`,
p.`name`,
p.`email`
FROM `people` p
WHERE p.`id` = :id;
SQL,
id: $id );
return static::one( $stmt );
}
}static::many() returns an array of instances:
final class Person
{
/**
* @return static[]
*/
public static function all(): array
{
$stmt = DB::select( <<<SQL
SELECT
p.`id`,
p.`name`,
p.`email`
FROM `people` p;
SQL );
return static::many( $stmt );
}
}Obviously writing the same SQL query every time is dumb so for best practice just move it to a helper method:
final class Person
{
private static function sql(): string
{
return <<<SQL
SELECT
p.`id`,
p.`name`,
p.`email`
FROM `people` p;
SQL;
}
public static function get( int $id ): ?static
{
$sql = static::sql();
$stmt = DB::select( <<<SQL
{$sql}
WHERE p.`id` = :id;
SQL,
id: $id );
return static::one( $stmt );
}
/**
* @return static[]
*/
public static function all(): array
{
$sql = static::sql();
$stmt = DB::select( $sql );
return static::many( $stmt );
}
}Under the hood static::one() and static::many() calls $stmt->fetchObject( static::class ) which works like this:
When an object is fetched, its properties are assigned from respective column values, and afterwards its constructor is invoked.
therefore if you have something like a JSON object or a CSV string of tags you can handle them in the constructor like this:
final class Post
{
public int $id;
private string $_tags;
private string $_data;
/** @var string[] */
public array $tags;
public object $data;
public function __construct()
{
$this->tags = explode( ',', $this->_tags );
$this->data = json_decode( $this->_data );
}
public static function get( int $id ): ?static
{
$stmt = DB::select( <<<SQL
SELECT
p.`id`,
p.`tags` AS `_tags`,
p.`data` AS `_data`
FROM `posts` p
WHERE p.`id` = :id;
SQL,
id: $id );
return static::one( $stmt );
}
}Because you control the SQL you can do whatever you want:
final class Post
{
public int $id;
public string $title;
public string $author_name;
public static function get( int $id ): ?static
{
$stmt = DB::select( <<<SQL
SELECT
p.`id`,
p.`title`,
a.`name` AS `author_name`
FROM `posts` p
INNER JOIN `authors` a ON (a.`id` = p.`author_id`)
WHERE p.`id` = :id;
SQL,
id: $id );
return static::one( $stmt );
}
}You can use CTEs and the LATERAL keyword, and UNION's can be done a couple of ways:
- wrap them in a CTE and have one
static::sql()method like:
return <<<SQL
WITH x AS (
SELECT
a.`id`
FROM `table_a` a
UNION ALL
SELECT
b.`id`
FROM `table_b` b
)
SELECT
x.*
FROM x
SQL;- have multiple SQL helpers and assemble them in your getter method like:
$sqlAbc = static::sqlAbc();
$sqlXyz = static::sqlXyz();
$stmt = DB::select( <<<SQL
{$sqlAbc}
WHERE ...
UNION ALL
{$sqlXyz}
WHERE ...
SQL );- merge two model classes together in PHP like:
$records = [
...Abc::all(),
...Xyz::all(),
];PORM uses static everywhere so you can extend your class and just override the necessary methods.
I don't have a generic example for this yet, but I've done these before:
- override your
static::sql()helper in the sub-class to have anINNER JOIN - override certain getter methods to have extra stuff in the
WHEREclause - override
static::sql()in the sub-class to use a completely different table but the same column names
Because PORM models are just PHP classes and you write your own SQL the world is your oyster ❤️
Relationships in PORM are protected from N+1 queries by default, and you define them using attributes:
On the child record you define the "local key" and the "remote class":
use PORM\Relationships\BelongsTo;
public int $parent_id;
#[BelongsTo]
public Parent $parent;PORM will call Parent::list( $ids ) with the $parent_id's of the loaded child records.
You can manually specify the local key ($parent_id) and remote key ($id) on the BelongsTo attribute if PORM guesses them wrong based on the property type, and a custom method if you want something other than Parent::list().
This is the inverse of BelongsTo - on the parent record you just define the remote class:
use PORM\Relationships\HasOne;
#[HasOne]
public Child $child;PORM will call Child::fromParents( $parent_ids ) with the $id's of the loaded parent records. The method call is based on a naming convention, so if you had an Order parent model with a Shipment child relation it would call Shipment::fromOrders( ... ).
You can manually specify the local key ($id), remote key ($parent_id), and method name on the attribute here too.
On the parent record you define an array and type-hint the remote class:
use PORM\Relationships\HasMany;
/** @var Child[] */
#[HasMany( Child::class )]
public array $children;PORM will call Child::fromParents( $parent_ids ) with the $id's of the loaded parent records same as HasOne does, but plucks Child records into the $children array where $child->parent_id === $parent->id.
HasMany makes the same guesses as HasOne and you can manually specify the same properties, but because PHP doesn't have typing for arrays yet you have to specify the remote class manually and a PHPDoc to get IDE autocomplete working.
Right now the scope of PORM is just for reading from the DB but it's your code so you can do whatever you want.
I plan to add insert and update helpers to the DB class as well as some other utilities so check back later!
Yeah go for it mate, no worries 😊