Skip Navigation

The DBSelector Class

The DBSelector is the class that is the basis for the entity selector. It is a more general class that can be used to generate "SELECT" queries for any type of data. The purpose of this class is to make generating complex queries simpler because you can add tables, fields, relations, etc. at anytime and in any order rather than having to use strict SQL syntax. Although the DBSelector doesn't have all the functionality that you would get from writing an SQL statement by hand (no LEFT JOINS or compound OR logic for example), it handles many of the basic selections that are used in creating a web page.

Setting up a query

There are a few basic functions that one can use to set up a query using the DBSelector. The following is a description of what these functions do:

  • add_table( $alias, $table ) -- this function adds a table to the search. The tables are the part of the query that come at the beginning. Adding a table, the query would become "SELECT ____ FROM $table AS $alias ...". If table is left blank, then the function assumes that the alias is the name of the table as well as the alias. In other words, if table is blank we get "SELECT ____ FROM $alias AS $alias ...".
  • add_field( $table, $field, $alias, $function ) -- the fields are the most important part of the query, as that's what we're trying to select from the database. The table is the name of the table from which we're selecting. The field is the name of the field we're selecting. The last two parameters are optional. If alias is set, then we select the name of the field as that alias: "SELECT $table.$field AS $alias ...". If function is set, it is a MYSQL function, then our query looks something like this: …"SELECT $function($table.$field) AS $alias ...". If no fields are added to the query, then the DBSelector assumes "*" as the field (SELECT * FROM ... ).
  • add_relation( $relation ) -- this adds a relation to the query in the where clause. Since there is no general way to set up a relation, it is up to the user to make sure that the syntax is proper when setting up a relation. This is generally something like add_relation( 'entity.id = meta.id' ) or add_relation( 'dated.datetime < NOW()' ).
  • set_start( $start ) -- default is 0. This sets the first result to be displayed. For instance, if a query would normally return 12 results and start is set to 5, it will skip the first 5 results if finds and give you the last 7. If start is set to 0, it will start at the beginning.
  • set_num( $num )-- default is -1. This sets the number of results to be displayed. If it is set to -1, it will not set a limit for the number of results and just grab them all.
  • set_order( $order ) -- this sets the order for the query. Usually, this will be of the form "table.field (ASC|DESC)". If you wish to have more than one sort, you must do it all at once. (i.e. "table1.field1 (ASC|DESC), table2.field2 (ASC|DESC), ... ")

NOTE: The first three functions listed above can be used repeatedly. You can add as many tables, fields, and relations to a query as you wish. If you use the last three more than once, the old values will get overwritten.

Getting results from the query

There are three more functions which are useful when trying to learn about the results of a query.

  • get_count() -- This function doesn't select any results from the database, rather, it replaces the fields with count(*) and returns that without using any limits. This is useful if you are listing items on multiple pages, and you need to know how many there are total, even though you may only be listing a few.
  • get_query() --This function takes all the things you have set up, and puts them together into a single query string and returns it.
  • run() -- gets the query using get_query() and queries the database using the db_query() function. Then take the results and puts them into an array which is returned.

An example

Below is the code to the function get_entity_by_id_object. The purpose of this function is to get all the information associated with a given entity.

function get_entity_by_id_object( $id )
{
$dbq = new DBSelector;
$tables = get_entity_tables_by_id( $id );
if ( $tables )
{
reset( $tables );
while( list( , $table) = each( $tables ) )
{
$dbq->add_field( $table,'*' );
$dbq->add_table( $table );
$dbq->add_relation( 'entity.id = '.$table.'.id' );
}
}
$dbq->add_relation( 'entity.id = '.$id );
return $dbq;
}

You can see basically how the code works. First, you initialize the DBSelector. We won't go into how the function get_entity_tables_by_id works, but you can just know that it does what it's supposed to do. The, for each table that we need, we go through and add three things. 1) add $table.* to our list of fields, this makes sure that all the data from each table is selected, 2) add $table to our list of tables, and 3) add the relation entity.id = $table.id. This insures us that we will be selecting all the data from the new table and that the id of the row in the new table matches the id of the row in our entity table. Finally, we add one more relation entity.id = $id. This assures that we only select the row that has the proper id.

At this point, if we were doing this in code, we could call $dbq->run() which would return an array that contained one row, which was out item. We could call $dbq->get_count() which would return 1, since ids are unique within reason. Or we could call $dbq->get_query() and run the query on our own.

The DBSelector class is rarely used directly within the Reason core anymore, but is used extensively in the Reason Administrative interface. Typically, you should construct queries using the entity selector unless you are working on very core pieces of Reason (such as the entity selector class itself).