The core of the Fakoli framework (as with many similar frameworks) is a mechanism for mapping tables in the database to objects in PHP, a technique that is generally known as Object Relational Mapping (ORM). These object definitions form the data model from which the web application can be built.

1. Functions for querying the database and returning the results as collections of data model objects.
2. Methods on the datamodel objects to support saving their data to and loading it from the database.

Defining a Data Model object

Data model objects are defined for a table by deriving a class from the DataItem base class. This class then specifies the field types to be used to represent each of the fields in the table. Fields are handled by the Automatic Form engine according to their type, so how you specify the field here controls how your application will look. Generally it is good practice to put the data model object in a file within the /datamodel/ folder of your application (for example the data model for a table named task would have the path /datamodel/task.inc).

The structure of a data model class definition is shown below.

class Task extends DataItem
{	
	var $table = "task";
	var $primary_key = "task_id";
		
	var $fields = array("task_id"           =>	Number,
                        "project_id"        =>	Number,
                        "title"             =>	String,
                        "summary"           =>	Text,	
                        "description"       =>	HTML,
                        "task_category_id"  =>	Number,
                        "date_assigned"     =>	Date,
						"due_date"          =>	Date,
						"priority_level"    =>	String,
						"status"            =>	String,
						"task_type_id"      =>	Number );
	
	// Relations
	
	var $relations = array( "Assignments"   =>	TaskAssignment,
							"Project"       =>	Project,
							"People"        =>	Person,
							"Category"      =>	TaskCategory);
	
	function Assignments()
	{
		return $this->getRelatedList(TaskAssignment);		
	}
	
	function Project()
	{
		return $this->getRelated(Project);
	}
	
	function People($constraint="")
	{
		return $this->crossReference(Person, TaskAssignment, $constraint);
	}
	
	function Category()
	{
		return $this->getRelated(TaskCategory);
	}
}

Fields

In the $fields array you list all the fields in the database table in the order you would like them to appear in a web form. For each field, you specify its type handler. The available types are as follows:

Type	Use for Database Types	AutoForm handles as
Number	INTEGER, NUMERIC, FLOAT, DECIMAL	Short text field
String	VARCHAR	Single line text field
Text	VARCHAR, TEXT	Multi-line text area
HTML	VARCHAR, TEXT	HTML editor
Currency	NUMERIC, DECIMAL	Short numeric text field with 2 decimal places
Boolean	INTEGER, TINYINT	Checkbox
Password	VARCHAR	Password & Confirmation field encrypted in database
Date	DATE	Text field with date picker
Timestamp	TIMESTAMP	No field, automatically updates with latest modification date and time

Relationships

The $relations array contains a mapping of function names to classes. Each of these relationships is then defined with a function. For instance, in the above example the Project() function returns the related Project object for a given Task. All the relationship functions are defined using the patterns above, corresponding to the structure in the database.

The DataItem base class provides three methods for implementing relationships:

• DataItem::getRelated() is used for one-to-one relationships, where the related class's primary key is a foreign key in the source class.
• DataItem::getRelatedList() is used for one-to-many relationships, where the related class has a foreign key that maps to the source class' primary key.
• DataItem::crossReference() is used for many-to-many relationships via a third cross-referencing class that contains foreign keys to both source and destination class primary keys.

Class Constructor

The class constructor is a function with the same name as the enclosing class (i.e. Task() in the example above). The definition of the class constructor for a data model object always follows the format shown above. It needs to define:

• The $this->table field, which specifies the table in the database that the class is mapping to.
• The $this->primary_key field, which is the name of the primary key for the table, as defined in the database.
• Optionally, the $this->default_format field can be used to specify how the object should be formatted by default. It should be a string with some field names in parentheses within it, according to the format you wish to follow. This is not used extensively within the framework at this point, and can be safely omitted if desired.
• Also, optionally the $this->pretty_class_name field can be used to provide a human-readable name for the class (by default Fakoli will attempt to prettify the PHP class name to make it human-readable; sometimes this does not have the intended results).
• Finally the $this->DataItem(func_get_args()) line is vital as it passes all the parameters passed into this constructor down to the base class constructor, which knows how to handle them.

Querying the Data Model

Once you have defined your data model classes, you can use them to query the database in a number of ways:

Instantiating from a Primary Key

The simplest way to retrieve a single object from the database is to instantiate it from its primary key. For instance, the following code snippet reads the task_id parameter from the HTTP query string, instantiates the corresponding Task object from the database and then outputs the contents of the title field.

$task_id = checkNumeric($_GET[task_id]);
$task = new Task($task_id);
echo $task->title;

(Note the use of the checkNumeric() helper function–this function checks the value passed to it to ensure that it is either numeric or an empty string. Use this function whenever you read numeric values (such as primary keys) from the HTTP query string, to protect against SQL injection and cross-site scripting attacks.)

Traversing Relationships

We can use the relationships we defined in our data model class to traverse to related objects. For instance, following on from the example above we could use the following code to output the project title and a list of assigned people for our Task.

$project = $task->Project();
$people = $task->People("ORDER BY last_name, first_name");

echo "{‌$project->title}:\n";
foreach($people as $person)
{
	echo "{‌$person->first_name} {‌$person->last_name}\n";
}

Note that in the call to the Task::People() relationship function we provided an extra constraint to order the related items.

Simple Queries

Making queries against the database is easy using the data model objects. Fakoli provides two APIs for querying - an object-oriented API via the Query class, and a functional API via a set of utility methods.

Querying via the Query object

$tasks = Query::create(Task, "WHERE due_date < now() AND status='Pending' ")
              ->execute();

echo "These are the tasks that are overdue:\n";
echo formatItems($tasks, "{‌title}", \n);

Querying via the utility methods

$tasks = query(Task, "WHERE due_date < now() AND status='Pending' ");
echo "These are the tasks that are overdue:\n";
echo formatItems($tasks, "{‌title}", \n);

In the above examples we also used the formatItems() function, which can be a useful way to generate simple output from the lists of objects returned by the query.

When you call the query() function or the Query::create() method you specify the class of object that you are querying, and can then provide any SQL constraints (such as WHERE and/or ORDER BY clauses) that you wish to apply to the query. The results are returned to you as an array of objects of the specified class.

The query() function can also take other parameters, such as query filters, page ranges, etc. The Query object can also handle these extra parameters, and the object-oriented interface helps break them up and make them more readable. It also makes it easy to use bound parameters in query constraints - something that is especially useful for avoiding SQL-injection vulnerabilities in your scripts. Here is a more complex example using the Query object with bound variables:

// Create the query
$query = Query::create(Task, "WHERE due_date < now() AND status=:s")
              ->bind(':s', $_GET['status']);

// ...

$tasks = $query->execute();

echo "These are the tasks that are overdue:\n";
echo formatItems($tasks, "{title}", "\n");

Here you can see another advantage of the object-oriented Query API - queries can be built, stored, passed around and manipulated quite easily before they are finally executed.

Value Queries

Sometimes you just need to retrieve aggregated values from the database. For this purpose you can use the Query::executeValue() method or the queryValue() function:

Querying a value using the queryValue() function

$numOverdue = queryValue(Task, "COUNT(1)", "WHERE due_date < now() AND status='Pending'" );
echo "There are $numOverdue pending tasks.";

Querying a value via the Query object

$numOverdue = Query::create(Task, "WHERE due_date < now() AND status=:s" )
                   ->bind(":s", $_GET["status"])
                   ->executeValue("COUNT(1)");

echo "There are $numOverdue matching tasks";

Grouped and Indexed Queries

One sophisticated feature that the Fakoli framework provides is the ability to return query results in a structured format indexed by the values of a specific key. This can be especially useful when dealing with hierarchical data operations (such as populating a tree control), and can often be used instead of a join operation to simplify the application code without too much of an increase in database overhead.

These query types are implemented by the GroupedQuery and IndexedQuery classes respectively (the functional query API provides two functions called groupedQuery() and indexedQuery() that perform the same tasks).

GroupedQuery is the more general of the two classes. It returns an array of arrays of DataItem objects of the specified class grouped by the value of the organizing field. This is best explained with a code example. The following code snippet displays all tasks listed by project. The projects are listed in alphabetical order, the grouped tasks are listed in order of their due date.

$projects = Query::create(Project, "ORDER BY title")->execute();
$tasks = GroupedQuery::create(Tasks, "ORDER BY due_date", "project_id")->execute();

foreach($projects as $project)
{
    if ($tasks[$project->project_id])
    {
        echo "{$project->title}";
        $projectTasks = $tasks[$project->project_id];

        echo "<ul>"; 
        foreach($projectTasks as $task) 
        { 
               echo "<li>{$task->title}</li>"; 
        } 
        echo "</ul>";
    }
}

IndexedQuery can be used when you are sure that the values being returned in the organizing field are going to be unique. Rather than returning an array of arrays, it returns an array of objects (so each unique key value has one object). IndexedQuery calls are especially useful for providing lookup tables for the existence of objects based on their unique primary keys.

Regrouping and Reindexing Query Results

Sometimes you will have already queried data from the database and then find that you also need to have it in a grouped or indexed format. Rather than make another query for the same data, you can use the provided functions regroupList() and reindexList() to achieve this. For instance, the following code retrieves the full list of tasks, then regroups them by project:

$tasks = Query::create(Task, ORDER BY due_date)->execute();
$tasksByProject = regroupList($tasks, "project_id");

These tasks can be applied to structured results as easily as to ordinary lists. For instance, the following code initially queries tasks by project, then regroups them by category.

$tasksByProject = GroupedQuery(Task, ORDER BY due_date, project_id)->execute();
$tasksByCategory = regroupList($tasksByProject, "task_category_id");

Chapter 3. More Data Model Shenanigans » « Chapter 1. Introduction