Database Queries¶
More about queries
Manifesto currently works only with MySQL databases. There is a Connection class that handles the most rudimentary aspects of connecting to the database, and you should be able to use Manifesto for most activities without ever having to establishing a database connection manually.
The Oracle class (as in “ask the Oracle at Delphi”, not the database company) is the parent of all database-querying classes. The properties of the Oracle class correspond largely to the various components of a database query. The most significant properties are:
- tablename
- selectfields
- where_clause
- set_clause
- orderby
- groupby
- limit_count
- limit_start
- object_class
So you can construct a query on any table as follows:
$oracle = new Oracle;
$oracle->set_tablename('mytable');
$oracle->set_selectfields('mytable.*');
$oracle->set_where_clause('objectid > 256');
$oracle->set_limit_count(10);
$oracle->set_orderby('lastname');
$oracle->get_record(); // see below for more information on this method
And this will construct a query that looks like
SELECT mytable.* FROM mytable WHERE objectid > 256 LIMIT 10 ORDER BY lastname;
That would be the long way. Most classes are built with their own Oracle class that extends the parent Oracle, so, for example, a “MediaOracle” might include a constructor method that sets reasonable defaults, e.g.
function MediaOracle() {
$this->set_tablename('media');
$this->set_selectfields('media.*');
$this->set_orderby('created_datetime DESC');
}
So that you can use it more efficiently for your own queries, like
$oracle = new MediaOracle();
$oracle->set_where_clause("mime_type = 'image'")->get_record();
Building the query programmatically¶
One of the benefits to this long-hand way to build database queries is that it accommodates the modular nature of Manifesto. One module can be configured to programmatically modify the database queries generated by another module (when granted the proper permissions), so there must be a modular query-building structure that allows for that level of cooperation.
Generally speaking, you’ll always start with an Oracle object, traditionally named $oracle:
``Ex: $oracle = new Oracle();``
set_module¶
Sets the name of the module responsible for this query. Used largely for auditing and tracking purposes.
Ex: $oracle->set_module('accounts');
set_tablename¶
This method sets the primary database table to be queried. If a query will join multiple tables, this method should be used to set the first table, and add_tablename should be used for the rest. This method designates the initial table as the $master table as well, and that is used by some common join queries (e.g. hash_category joins) to know which table’s objectid to join on. This method is destructive in that subsequent calls will overwrite the initial fields.
Ex: $oracle->set_tablename('users');
add_tablename¶
This method adds a table to the initial query. It is generally used in conjunction with a modification to the SELECT clause and/or to the WHERE clause (otherwise, why are you joining the table?). This method is non-destructive and additive in that subsequent calls will append to the existing query.
Ex: $oracle->add_tablename('user_address');
set_selectfields¶
This method sets the initial SELECT part of the query (minus the word select), listing the fields you wish to retrieve from the query. Because Manifesto is modular, it is often possible for other modules to add tablenames and select fields to the initial query, so it is standard practice to include full table.fieldname references in the SELECT section to avoid name collisions. This method is destructive in that subsequent calls will overwrite the initial fields.
Ex: $oracle->set_selectfields('users.*,user_address.label');
add_selectfields¶
This method adds one or more fields to the SELECT portion of a query. It must be used in conjunction with add_tablename because you must be joining the table from which you are selecting the fields. This method is non-destructive and additive in that subsequent calls will append to the existing query.
Ex: $oracle->add_selectfields('user_address.city,user_address.state');
set_where_clause¶
This method establishes the conditions for the query in the WHERE clause. This method is always additive in that calls will always append to the existing query. The most basic example is to pass in a simple comparitive statement like "objectid = 23". While multiple calls to this method may be chained together, you may also directly pass in more complex statements like "objectid = 23 AND (post_date > '2018-01-01' OR status = 'New')". See more examples below.
Ex: $oracle->set_where_clause("'users.status = 'Active'");
or
Ex: $oracle->set_where_clause("objectid = 23 AND (post_date > '2018-01-01' OR status = 'New'))"
prepend_where_clause¶
This behaves almost exactly like set_where_clause, but it is inserted at the beginning of the WHERE clause during execution. Normally, this shouldn’t be necessary, but it can be used to help manually optimize queries by adjusting the priority of the conditions.
Ex: $oracle->prepend_where_clause("'users.status = 'Active'");
set_having_clause¶
This sets the HAVING portion of a query. This method is destructive in that subsequent calls will overwrite the initial fields.
Ex: $oracle->set_having_clause('depth > 1');
set_join¶
This method allows you to pass in an entire straight join statement in a single call.
$oracle->set_join('hash_categories ON (hash_categories.reference_id = othertable.objectid)');
This method is always additive in that calls will always append to the existing query.
set_left_join¶
This method allows you to pass in an entire left join statement in a single call.
$oracle->set_left_join("hash_categories ON (hash_categories.reference_id = othertable.objectid AND hash_categories.reference_class = 'Book')");
This method is always additive in that calls will always append to the existing query.
set_right_join¶
This method allows you to pass in an entire right join statement in a single call.
$oracle->set_right_join('hash_categories ON (hash_categories.reference_id = othertable.objectid)');
This method is always additive in that calls will always append to the existing query.
set_union¶
This method allows you to pass in a whole other Oracle object to join the two combined queries with a UNION statement.e.g.:
$oracle = new CategoryOracle();
$oracle->set_where_clause('deleted = 0');
$oracle2 = new CustomCategoryOracle();
$oracle2->set_where_clause('deleted = 0');
$oracle->set_union($oracle2);
$oracle->get_record();
The results will be the union of all the non-deleted Category responses with all of the non-deleted CustomCategory responses.
get_count¶
After the query parameters are all set on the Oracle, get_count() executes a modified version of your query without limit_count to get a total count of all possible responses before limiting your query results. This allows for Manifesto to properly calculate the pagination of results, but it is also useful to avoid the expense of a more complicated database query that may not return any results.
It returns the total count of results, and stores this result in $oracle->total_count.
Ex: $total = $oracle->get_count();
get_record¶
This is the final call to build the query and execute the database request. By default, this method executes the query, loops through theh results, and _instantiates some instance of a ManifestoObject for each result in the database,_ as designated by the object_class of the Oracle. If you wish to access the results of the query as an array of associative arrays instead, simply pass false as the only parameter to this method.
Note: This method does not return the result array. It sets the response_count, total_count, response_array, and object_array properties of the Oracle, which may then be accessed later. This method returns the Oracle object itself, so it may be chained together with other methods.
``Ex: $oracle->get_record(); // to return an array of objects ``
or
Ex: $oracle->get_record(false); // to return an array of associative arrays, one per row`