Spot PHP ORM
For RDBMS (Currently has a MySQL and Sqlite adapter), (*1)
DEPRECATION NOTICE
WORK ON THIS PROJECT HAS STOPPED. Please use Spot v2 instead - it is actively maintained, and is based on a more solid foundation in the Doctrine DBAL (database abstraction layer, not Doctrine ORM), and had much wider and mroe robust database support (MySQL, SQLite, PostgreSQL, SQLServer, Oracle, etc.)., (*2)
Using Spot In Your Project
Spot is a standalone ORM that can be used in any project. Follow the
instructions below to get Spot setup in your project, or use a pre-coded
plugin for the framework you are using:, (*3)
Silex provider by @psamatt, (*4)
Connecting to a Database
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., (*5)
$cfg = new \Spot\Config();
// MySQL
$adapter = $cfg->addConnection('test_mysql', 'mysql://user:password@localhost/database_name');
// Sqlite
$adapter = $cfg->addConnection('test_sqlite', 'sqlite://path/to/database.sqlite');
If you are using Sqlite, the Sqlite filename must be the name of your database followed by the extension e.g blogs.sqlite, (*6)
Accessing the Mapper
Since Spot follows the DataMapper design pattern, you will need a mapper
instance for working with object Entities and database tables., (*7)
$mapper = new \Spot\Mapper($cfg);
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:, (*8)
function get_mapper() {
static $mapper;
if($mapper === null) {
$mapper = new \Spot\Mapper($cfg);
}
return $mapper;
}
Creating Entities
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., (*9)
namespace Entity;
class Post extends \Spot\Entity
{
protected static $_datasource = 'posts';
public static function fields()
{
return array(
'id' => array('type' => 'int', 'primary' => true, 'serial' => true),
'title' => array('type' => 'string', 'required' => true),
'body' => array('type' => 'text', 'required' => true),
'status' => array('type' => 'int', 'default' => 0, 'index' => true),
'date_created' => array('type' => 'datetime')
);
}
public static function relations()
{
return array(
// Each post entity 'hasMany' comment entites
'comments' => array(
'type' => 'HasMany',
'entity' => 'Entity_Post_Comment',
'where' => array('post_id' => ':entity.id'),
'order' => array('date_created' => 'ASC')
)
);
}
}
Built-in Field Types
All the basic field types are built-in with all the default
functionality provided for you:, (*10)
stringintfloat/double/decimalbooleantextdatedatetimetimestampyearmonthday
Registering Custom Field Types
If you want to register your own custom field type with custom
functionality on get/set, have a look at the clases in the Spot\Type
namespace, make your own, and register it in Spot\Config:, (*11)
$config->typeHandler('string', '\Spot\Type\String');
Migrations / Creating and Updating Tables
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., (*12)
$mapper->migrate('Post');
Your database should now have the posts table in it, with all the fields you described in your Post entity., (*13)
Finders (Mapper)
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., (*14)
all(entityName, [conditions])
Find all entityName that matches the given conditions and return a
Spot\Entity\Collection of loaded Spot\Entity objects., (*15)
// Conditions can be the second argument
$posts = $mapper->all('Entity\Post', array('status' => 1));
// Or chained using the returned `Spot\Query` object - results identical to above
$posts = $mapper->all('Entity\Post')->where(array('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()., (*16)
first(entityName, [conditions])
Find and return a single Spot\Entity object that matches the criteria., (*17)
$post = $mapper->first('Entity\Post', array('title' => "Test Post"));
Or first can be used on a previous query with all to fetch only the first
matching record., (*18)
$post = $mapper->all('Entity\Post', array('title' => "Test Post"))->first();
Conditional Queries
# All posts with a 'published' status, descending by date_created
$posts = $mapper->all('Entity\Post')
->where(array('status' => 'published'))
->order(array('date_created' => 'DESC'));
# All posts created before today
$posts = $mapper->all('Entity\Post')
->where(array('date_created <' => new \DateTime()));
# Posts with 'id' of 1, 2, 5, 12, or 15 - Array value = automatic "IN" clause
$posts = $mapper->all('Entity\Post')
->where(array('id' => array(1, 2, 5, 12, 15)));
Searches
Spot supports search queries using LIKE, REGEX, and FULLTEXT searches (if your
adapter supports it)., (*19)
Query#search(text)
The search method on Spot\Query will use a LIKE search, or a FULLTEXT
search if enabled. The wiki page on searching has more details about using FULLTEXT searches., (*20)
$q = "walrus"; // Text to search for
$results = $mapper->all('Entity\Post')
->search('body', $q)
->order(array('date_created' => 'DESC'));
LIKE searches
You can use the :like query operator to perform a LIKE search:, (*21)
$results = $mapper->all('Entity\Post')
->where('body :like', 'walrus%')
->order(array('date_created' => 'DESC'));
REGEX searches
You can use the :regex or ~= query operator to perform a REGEX search:, (*22)
$results = $mapper->all('Entity\Post')
->where('body ~=', 'walrus.+')
->order(array('date_created' => 'DESC'));
NOTE: Most REGEX searches are very slow, so use this sparingly., (*23)
Relations
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., (*24)
Relation Types
Entity relation types are:, (*25)
HasOneHasManyHasManyThrough
HasOne
HasOne is the simplest relation - an example might be Post has one Author., (*26)
class Entity\Post extends \Spot\Entity
{
protected static $_datasource = 'posts';
public static function fields()
{
return array(
'id' => array('type' => 'int', 'primary' => true, 'serial' => true),
'author_id' => array('type' => 'int', 'required' => true),
'title' => array('type' => 'string', 'required' => true),
'body' => array('type' => 'text', 'required' => true),
'status' => array('type' => 'int', 'default' => 0, 'index' => true),
'date_created' => array('type' => 'datetime')
);
}
public static function relations()
{
return array(
// Each post 'hasOne' author
'author' => array(
'type' => 'HasOne',
'entity' => 'Entity\Author',
'where' => array('id' => ':entity.author_id')
)
);
}
}
HasMany
HasMany is used where a single record relates to multiple other records - an
example might be Post has many Comments., (*27)
We start by adding a comments relation to our Post object:, (*28)
class Entity\Post extends \Spot\Entity
{
protected static $_datasource = 'posts';
public static function fields()
{
return array(
'id' => array('type' => 'int', 'primary' => true, 'serial' => true),
'author_id' => array('type' => 'int', 'required' => true),
'title' => array('type' => 'string', 'required' => true),
'body' => array('type' => 'text', 'required' => true),
'status' => array('type' => 'int', 'default' => 0, 'index' => true),
'date_created' => array('type' => 'datetime')
);
}
public static function relations()
{
return array(
// Each post 'hasMany' comments
'comments' => array(
'type' => 'HasMany',
'entity' => 'Entity\Post\Comment',
'where' => array('post_id' => ':entity.id'),
'order' => array('date_created' => 'ASC')
)
);
}
}
And add a Entity\Post\Comment object with a 'hasOne' relation back to the post:, (*29)
class Entity\Post\Comment extends \Spot\Entity
{
// ... snip ...
public static function relations() {
return array(
// Comment 'hasOne' post (belongs to a post)
'post' => array(
'type' => 'HasOne',
'entity' => 'Entity\Post',
'where' => array('id' => ':entity.post_id')
)
);
}
}
HasManyThrough
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 model., (*30)
We need to add the tags relation to our Post entity, specifying query
conditions for both sides of the relation., (*31)
class Entity\Post extends \Spot\Entity
{
// ... snip ...
public static function relations()
{
return array(
// Each post 'hasMany' tags `Through` a post_tags table
'tags' => array(
'type' => 'HasManyThrough',
'entity' => 'Entity_Tag',
'throughEntity' => 'Entity_PostTag',
'throughWhere' => array('post_id' => ':entity.id'),
'where' => array('id' => ':throughEntity.tag_id'),
)
);
}
}
Explanation
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\Post\Tags entity, using the current loaded post id matching
post_tags.post_id., (*32)
Another scenario and more detailed explanation is on the HasManyThrough wiki page., (*33)
Wallogit.com
