Spot v2.x is built on the Doctrine DBAL, and targets PHP 5.4+.
The aim of Spot is to be a lightweight DataMapper alternative that is clear, efficient, and simple - and doesn't use annotations or proxy classes.
Spot is a standalone ORM that can be used in any project. Follow the instructions below to get Spot setup in your project.
composer require suzol10/spot2
The Spot\Locator
object is the main point of access to spot that you will
have to be able to access from everywhere you need to run queries or work with
your entities. It is responsible for loading mappers and managing configuration.
To create a Locator, you will need a Spot\Config
object.
The Spot\Config
object stores and references database connections by name.
Create a new instance of Spot\Config
and add database connections with
DSN strings so Spot can establish a database connection, then create your
locator object:
$cfg = new \Spot\Config();
// MySQL
$cfg->addConnection('mysql', 'mysql://user:password@localhost/database_name');
// Sqlite
$cfg->addConnection('sqlite', 'sqlite://path/to/database.sqlite');
$spot = new \Spot\Locator($cfg);
You can also use DBAL-compatible configuration arrays instead of DSN strings if you prefer:
$cfg->addConnection('mysql', [
'dbname' => 'mydb',
'user' => 'user',
'password' => 'secret',
'host' => 'localhost',
'driver' => 'pdo_mysql',
]);
Since you have to have access to your mapper anywhere you use the database, most people create a helper method to create a mapper instance once and then return the same instance when required again. Such a helper method might look something like this:
function spot() {
static $spot;
if($spot === null) {
$spot = new \Spot\Locator();
$spot->config()->addConnection('test_mysql', 'mysql://user:password@localhost/database_name');
}
return $spot;
}
If you are using a framework with a dependency injection container or service,
you will want to use it so that the Spot\Locator
object is available
everywhere in your application that you need it.
Since Spot follows the DataMapper design pattern, you will need a mapper
instance for working with object Entities and database tables. You can get a
mapper instance from the Spot\Locator
object's mapper
method by providing
the fully qualified entity namespace + class name:
$postMapper = $spot->mapper('Entity\Post');
Mappers only work with one entity type, so you will need one mapper per entity class you work with (i.e. to save an Entity\Post, you will need the appropriate mapper, and to save an Entity\Comment, you will need a comment mapper, not the same post mapper. Relations will automatically be loaded and handled by their corresponding mapper by Spot.
NOTE: You do NOT have to create a mapper for each entity unless you need custom finder methods or other custom logic. If there is no entity-specific mapper for the entity you want, Spot will load the generic mapper for you and return it.
Entity classes can be named and namespaced however you want to set them
up within your project structure. For the following examples, the
Entities will just be prefixed with an Entity
namespace for easy psr-0
compliant autoloading.
namespace Entity;
use Spot\EntityInterface as Entity;
use Spot\MapperInterface as Mapper;
class Post extends \Spot\Entity
{
protected static $table = 'posts';
public static function fields()
{
return [
'id' => ['type' => 'integer', 'autoincrement' => true, 'primary' => true],
'title' => ['type' => 'string', 'required' => true],
'body' => ['type' => 'text', 'required' => true],
'status' => ['type' => 'integer', 'default' => 0, 'index' => true],
'author_id' => ['type' => 'integer', 'required' => true],
'date_created' => ['type' => 'datetime', 'value' => new \DateTime()]
];
}
public static function relations(Mapper $mapper, Entity $entity)
{
return [
'tags' => $mapper->hasManyThrough($entity, 'Entity\Tag', 'Entity\PostTag', 'tag_id', 'post_id'),
'comments' => $mapper->hasMany($entity, 'Entity\Post\Comment', 'post_id')->order(['date_created' => 'ASC']),
'author' => $mapper->belongsTo($entity, 'Entity\Author', 'author_id')
];
}
}
Although you do not have to create a mapper for each entity, sometimes it is nice to create one if you have a lot of custom finder methods, or want a better place to contain the logic of building all the queries you need.
Just specify the full mapper class name in your entity:
namespace Entity;
class Post extends \Spot\Entity
{
protected static $mapper = 'Entity\Mapper\Post';
// ... snip ...
}
And then create your mapper:
namespace Entity\Mapper;
use Spot\Mapper;
class Post extends Mapper
{
/**
* Get 10 most recent posts for display on the sidebar
*
* @return \Spot\Query
*/
public function mostRecentPostsForSidebar()
{
return $this->where(['status' => 'active'])
->order(['date_created' => 'DESC'])
->limit(10);
}
}
Then when you load the mapper like normal, Spot will see the custom
Entity\Post::$mapper
you defined, and load that instead of the generic one,
allowing you to call your custom method:
$mapper = $spot->mapper('Entity\Post');
$sidebarPosts = $mapper->mostRecentPostsForSidebar();
Since Spot v2.x is built on top of DBAL, all the DBAL types are used and fully supported in Spot:
Integer Types
smallint
integer
bigint
Decimal Types
decimal
float
String Types
string
text
guid
Binary String Types
binary
blob
Boolean/Bit Types
boolean
Date and Time Types
date
datetime
datetimetz
time
Array Types
array
- PHP serialize/deserialzesimple_array
- PHP implode/explodejson_array
- json_encode/json_decode
Object Types
object
- PHP serialize/deserialze
Please read the Doctrine DBAL Types Reference Page thoroughly for more information and types and cross-database support. Some types may be stored differently on different databases, depending on database vendor support and other factors.
If you want to register your own custom field type with custom functionality on get/set, have a look at the Custom Mapping Types on the DBAL reference page.
Since Spot uses the DBAL internally, there are no additional changes you have to make for your custom type to work with Spot.
Spot comes with a method for running migrations on Entities that will
automatically CREATE and ALTER tables based on the current Entity's fields
definition.
$mapper = $spot->mapper('Entity\Post');
$mapper->migrate();
Your database should now have the posts
table in it, with all the fields you
described in your Post
entity.
NOTE: Please note that re-naming columns is not supported in migrations because there is no way for spot to know which column you renamed to what - Spot will see a new column that needs to be created, and a column that no longer exists and needs to be dropped. This could result in data loss during an auto-migration.
The main finders used most are all
to return a collection of entities,
and first
or get
to return a single entity matching the conditions.
Find all entities and return a Spot\Entity\Collection
of loaded Spot\Entity
objects.
Find all entities that match the given conditions and return a
Spot\Entity\Collection
of loaded Spot\Entity
objects.
// Where can be called directly from the mapper
$posts = $mapper->where(['status' => 1]);
// Or chained using the returned `Spot\Query` object - results identical to above
$posts = $mapper->all()->where(['status' => 1]);
// Or more explicitly using using `select`, which always returns a `Spot\Query` object
$posts = $mapper->select()->where(['status' => 1]);
Since a Spot\Query
object is returned, conditions and other statements
can be chained in any way or order you want. The query will be
lazy-executed on interation or count
, or manually by ending the chain with a
call to execute()
.
Find and return a single Spot\Entity
object that matches the criteria.
$post = $mapper->first(['title' => "Test Post"]);
Or first
can be used on a previous query with all
to fetch only the first
matching record.
$post = $mapper->all(['title' => "Test Post"])->first();
A call to first
will always execute the query immediately, and return either
a single loaded entity object, or boolean false
.
# All posts with a 'published' status, descending by date_created
$posts = $mapper->all()
->where(['status' => 'published'])
->order(['date_created' => 'DESC']);
# All posts that are not published
$posts = $mapper->all()
->where(['status <>' => 'published'])
# All posts created before 3 days ago
$posts = $mapper->all()
->where(['date_created <' => new \DateTime('-3 days')]);
# Posts with 'id' of 1, 2, 5, 12, or 15 - Array value = automatic "IN" clause
$posts = $mapper->all()
->where(['id' => [1, 2, 5, 12, 15]]);
Joins are currently not enabled by Spot's query builder. The Doctine DBAL query builder does provide full support for them, so they may be enabled in the future.
While ORMs like Spot are very nice to use, if you need to do complex queries, it's best to just use custom queries with the SQL you know and love.
Spot provides a query
method that allows you to run custom SQL, and load the
results into a normal collection of entity objects. This way, you can easily run
custom SQL queries with all the same ease of use and convenience as the
built-in finder methods and you won't have to do any special handling.
$posts = $mapper->query("SELECT * FROM posts WHERE id = 1");
$posts = $mapper->query("SELECT * FROM posts WHERE id = ?", [1]);
$posts = $mapper->query("SELECT * FROM posts WHERE id = :id", ['id' => 1]);
NOTE: Spot will load ALL returned columns on the target entity from the query you run. So if you perform a JOIN or get more data than the target entity normally has, it will just be loaded on the target entity, and no attempt will be made to map the data to other entities or to filter it based on only the defined fields.
Relations are convenient ways to access related, parent, and child entities from
another loaded entity object. An example might be $post->comments
to query for
all the comments related to the current $post
object.
All relations are returned as instances of relation classes that extend
Spot\Relation\RelationAbstract
. This class holds a Spot\Query
object
internally, and allows you to chain your own query modifications on it so you
can do custom things with relations, like ordering, adding more query
conditions, etc.
$mapper->hasMany($entity, 'Entity\Comment', 'post_id')
->where(['status' => 'active'])
->order(['date_created' => 'ASC']);
All of these query modifications are held in a queue, and are run when the
relation is actually executed (on count
or foreach
iteration, or when
execute
is explicitly called).
All relation types are lazy-loaded by default, and can be eager-loaded to
solve the N+1 query problem using the with
method:
$posts = $posts->all()->with('comments');
Multiple relations can be eager-loaded using an array:
$posts = $posts->all()->with(['comments', 'tags']);
Entity relation types are:
HasOne
BelongsTo
HasMany
HasManyThrough
HasOne is a relation where the related object has a field which points to the
current object - an example might be User
has one Profile
.
$mapper->hasOne(Entity $entity, $foreignEntity, $foreignKey)
$entity
- The current entity instance$foreignEntity
- Name of the entity you want to load$foreignKey
- Field name on the$foreignEntity
that matches up with the primary key of the current entity
namespace Entity;
use Spot\EntityInterface as Entity;
use Spot\MapperInterface as Mapper;
class User extends \Spot\Entity
{
protected static $table = 'users';
public static function fields()
{
return [
'id' => ['type' => 'integer', 'autoincrement' => true, 'primary' => true],
'username' => ['type' => 'string', 'required' => true],
'email' => ['type' => 'string', 'required' => true],
'status' => ['type' => 'integer', 'default' => 0, 'index' => true],
'date_created' => ['type' => 'datetime', 'value' => new \DateTime()]
];
}
public static function relations(Mapper $mapper, Entity $entity)
{
return [
'profile' => $mapper->hasOne($entity, 'Entity\User\Profile', 'user_id')
];
}
}
In this scenario, the Entity\User\Profile
entity has a field named user_id
which the Entity\User
's id
field as a value. Note that no field exists on
this entity for this relation, but rather the related entity.
BelongsTo is a relation where the current object has a field which points to
the related object - an example might be Post
belongs to User
.
$mapper->belongsTo(Entity $entity, $foreignEntity, $localKey)
$entity
- The current entity instance$foreignEntity
- Name of the entity you want to load$localKey
- Field name on the current entity that matches up with the primary key of$foreignEntity
(the one you want to load)
namespace Entity;
use Spot\EntityInterface as Entity;
use Spot\MapperInterface as Mapper;
class Post extends \Spot\Entity
{
protected static $table = 'posts';
public static function fields()
{
return [
'id' => ['type' => 'integer', 'autoincrement' => true, 'primary' => true],
'user_id' => ['type' => 'integer', 'required' => true],
'title' => ['type' => 'string', 'required' => true],
'body' => ['type' => 'text', 'required' => true],
'status' => ['type' => 'integer', 'default' => 0, 'index' => true],
'date_created' => ['type' => 'datetime', 'value' => new \DateTime()]
];
}
public static function relations(Mapper $mapper, Entity $entity)
{
return [
'user' => $mapper->belongsTo($entity, 'Entity\User', 'user_id')
];
}
}
In this scenario, the Entity\Post
entity has a field named user_id
which is
the Entity\User
's id
field's value. Note that the field exists on this
entity for this relation, but not on the related entity.
HasMany is used where a single record relates to multiple other records - an
example might be Post
has many Comments
.
$mapper->hasMany(Entity $entity, $entityName, $foreignKey, $localValue = null)
$entity
- The current entity instance$entityName
- Name of the entity you want to load a collection of$foreignKey
- Field name on the$entityName
that matches up with the current entity's primary key
We start by adding a comments
relation to our Post
object:
namespace Entity;
use Spot\EntityInterface as Entity;
use Spot\MapperInterface as Mapper;
class Post extends Spot\Entity
{
protected static $table = 'posts';
public static function fields()
{
return [
'id' => ['type' => 'integer', 'autoincrement' => true, 'primary' => true],
'title' => ['type' => 'string', 'required' => true],
'body' => ['type' => 'text', 'required' => true],
'status' => ['type' => 'integer', 'default' => 0, 'index' => true],
'date_created' => ['type' => 'datetime', 'value' => new \DateTime()]
];
}
public static function relations(Mapper $mapper, Entity $entity)
{
return [
'comments' => $mapper->hasMany($entity, 'Entity\Comment', 'post_id')->order(['date_created' => 'ASC']),
];
}
}
And add a Entity\Post\Comment
object with a 'belongsTo' relation back to the post:
namespace Entity;
class Comment extends \Spot\Entity
{
// ... snip ...
public static function relations(Mapper $mapper, Entity $entity)
{
return [
'post' => $mapper->belongsTo($entity, 'Entity\Post', 'post_id')
];
}
}
HasManyThrough is used for many-to-many relationships. An good example is tagging. A post has many tags, and a tag has many posts. This relation is a bit more complex than the others, because a HasManyThrough requires a join table and mapper.
$mapper->hasManyThrough(Entity $entity, string $hasManyEntity, string $throughEntity, string $selectField, string $whereField)
$entity
- The current entity instance$hasManyEntity
- This is the target entity you want a collection of. In this case, we want a collection ofEntity\Tag
objects.$throughEntity
- Name of the entity we are going through to get what we want - In this case,Entity\PostTag
.$selectField
- Name of the field on the$throughEntity
that will select records by the primary key of$hasManyEntity
.$whereField
- Name of the field on the$throughEntity
to select records by the current entities' primary key (we have a post, so this will be theEntity\PostTag->post_id
field).
We need to add the tags
relation to our Post
entity, specifying query
conditions for both sides of the relation.
namespace Entity;
use Spot\EntityInterface as Entity;
use Spot\MapperInterface as Mapper;
class Post extends Spot\Entity
{
protected static $table = 'posts';
public static function fields()
{
return [
'id' => ['type' => 'integer', 'autoincrement' => true, 'primary' => true],
'title' => ['type' => 'string', 'required' => true],
'body' => ['type' => 'text', 'required' => true],
'status' => ['type' => 'integer', 'default' => 0, 'index' => true],
'date_created' => ['type' => 'datetime', 'value' => new \DateTime()]
];
}
public static function relations(Mapper $mapper, Entity $entity)
{
return [
'tags' => $mapper->hasManyThrough($entity, 'Entity\Tag', 'Entity\PostTag', 'tag_id', 'post_id'),
];
}
The result we want is a collection of Entity\Tag
objects where the id equals
the post_tags.tag_id
column. We get this by going through the
Entity\PostTags
entity, using the current loaded post id matching
post_tags.post_id
.