Using the FuseSQLQuery Object
From FuseWiki
Contents |
[edit] Introduction
The FuseSQLQuery offers a robust way of adding clauses to a base SQL Query.
The goal is to build queries piece by piece, so that the results returned by base functions (e.g. "SELECT * FROM table") can be altered with different ORDER BY clauses, LIMIT clauses, etc, without having to change the base function itself, and without having to pass each possible query clause as a parameter to the function.
Learning to use this object properly can not only make your SQL queries more manageable on screen, but it will also significantly increase the ease with which you can build modular functions to serve as a base for your data handling, which you can then branch off of.
[edit] A Basic Example
Every "fetch" function in the FuseDataModel class can take an existing query object as an option, which is passed through $options['query_obj']. Let's take a look at an example of how we can add a fetch_last_10() method to a BlogEntry model.
<?php
FUSE::Require_class('FuseDataModel');
class BlogEntry extends FuseDataModel {
public $field_name_prefix = 'entry_';
public function fetch_last_10( $options = null ) {
try {
$query_obj = $this->db->new_query_obj();
$query_obj->order_by( "{$this->table_name}.{$this->db_field_name_datetime} DESC" );
$query_obj->limit( 10 );
$options['query_obj'] = $query_obj;
return $this->fetch_all( $options );
}
catch( Exception $e ) {
throw $e;
}
}
}
Note: Above, we're not explicitly typing out the literal table name or the field names in our query.
It's best practice in FUSE to use the table_name and db_field_name_key getters in the data model object, instead of typing literal table names and field names.
The key in db_field_name_key is a "field key", which is simply the field name without the field prefix (in our case, "entries_"). You can also use a full field name as the key.
$this->table_name, in our case, will return "blog_entries".
$this->db_field_name_datetime, in our case, will return "entry_datetime".
As you can see above, we first use SQLOptions::Query_obj_from_option_hash to instantiate the query object. You could also instantiate a new FuseSQLQuery, but we want our methods to be able to build on top of existing queries, so we should check to see if our options array already has a query_obj that may have clauses added to it.
Next, we call the order_by method to order the table by the datetime field descending.
Finally, we store our updated query object in $options['query_obj'], then call fetch_all(), but with our query object in the options array. fetch_all() will now use this query object (because it is calling Query_obj_from_option_hash() as well), and will use our query object to generate the fetch query, which will now include our LIMIT and ORDER BY clauses.
[edit] FuseSQLQuery members
- distinct - if set to TRUE, the query will use SELECT DISTINCT instead of just SELECT
- auto_update_requires_where - If set to TRUE, the auto_update method will fail if no WHERE clause is specified. This is done as a precaution to prevent accidental updating of every row. Default: TRUE
[edit] FuseSQLQuery methods
-
[edit] Building Queries
- select( $select_clause ) - adds a SELECT clause to the query. Can be called as many times as needed.
- from( $table ) Adds a FROM clause to the query. Can be called as many times as necessary, and will separate the tables from multiple calls by commas.
- where( $where_clause [, $joiner] ) - adds a WHERE clause to the query. By default, subsequent calls to this method will append WHERE clauses with "and". To use "or" instead, set $joiner to "or". Can be called as many times as needed.
- order_by( $order_clause ) - adds an ORDER BY clause to the query. Can be called as many times as necessary.
- limit ( $limit_amount) - adds a LIMIT (or equivalent) clause to the query.
- set_limit_start ( $limit_start_amount ) - For limiting table rows by both a start and an end value, use this method to set the start value
- set_limit_end ( $limit_end_amount ) - For limiting table rows by both a start and an end value, use this method to set the end value
- join( $join_clause ) - Used to add a JOIN clause to your query. Can be called as many times as necessary.
- group_by( $group_clause ) - Used to add a GROUP BY clause to your query. Can be called as many times as necessary.
- add_insert_data( $full_field_name, $value [, $type] ) - When building an INSERT query, add a value for the field $field_name with value $value. $type is one of 'i' (integer), 'd' (double), 's' (string), 'f' (function), or 'u' (unparsed-passed exactly as-is). If no $type is specified, the type will be auto-determined based on $value.
- add_update_data( $full_field_name, $value [, $type] ) - When building an UPDATE query, add a value for the field $field_name with value $value. $type is one of 'i' (integer), 'd' (double), 's' (string), 'f' (function), or 'u' (unparsed-passed exactly as-is). If no $type is specified, the type will be auto-determined based on $value.
- auto_insert( $table_name ) - Using the data generated through calls to add_insert_data, generate and execute an INSERT query. Returns the last inserted ID on success.
- auto_update( $table_name[, $where_clause] ) - Using the data generated through calls to add_update_data, generate and execute an UPDATE query. $where_clause is required unless $auto_update_requires_where is set to false in the FuseSQLQuery object.
[edit] Changing the behavior of your query
- select_only( $select_clause ) - Ignore other selections in the query and select only what was passed to this method. Useful for COUNT() functions and the like, when you want to keep the WHERE, FROM, etc. clauses, but don't want to select anything except COUNT(*).
- select_first( $select_clause ) - Keep all selections already in the query, but make sure the clause passed to this method is first in the selection list
- from_only( $from_clause ) - Ignore other FROM clauses and use only what was passed to this method for the FROM clause
- order_first( $order_clause ) - Keep all ORDER BY clauses already in the query, but make sure that the clause passed to this method appears first in the order list.
- ignore_order ( $true_false ) - if the parameter to this function is set to 1 or TRUE, the query object will ignore ORDER BY clauses when generating the query.
- ignore_limit ( $true_false ) - if the parameter to this function is set to 1 or TRUE, the query object will ignore LIMIT clauses when generating the query.
- clear_selections() - Clear all existing selections from the query
-
[edit] Determining the current status of the query
- has_from( [$table] ) - If no parameter is passed to this method, it will return true if any FROM clause has been set. If the $table parameter is given, it will return true if the query has a FROM clause for the table name given.
- has_join( [$table] ) - If no parameter is passed to this method, it will return true if any JOIN clause has been added. If the $table parameter is given, it will return true if the query has a JOIN clause for the table name given.
- has_limit() - returns true if the query currently has a LIMIT (or equivalent) clause
- has_order() - returns true if the query currently has an ORDER BY clause.
- add_completed_setup( $setup_key ) - In order to keep track of which clauses have been added to a query object, you can add a setup key, which is just an arbitrary name of your choosing, so that query objects that get passed around don't accidentally get the same clauses added twice. You can then check if those clauses were added by using has_completed_setup()
- has_completed_setup( $setup_key ) - Returns true if add_completed_setup() has been called on the query object with the given $setup_key
- last_auto_insert_query() - returns the last automatically generated insert query
- last_auto_update_query() - returns the last automatically generated update query
- get_insert_data() - returns an associative array of the current data to be inserted
- get_update_data() - returns an associative array of the current data to be updated
[edit] Generating the query
- generate_sql_query() - returns the query as a string, which you can then pass to the query() method of a FuseDB object.
[edit] Miscellaneous
- set_parent_db_obj( $obj ) - Set which FuseDB object to use for this query. Default is to use the global DB object.
[edit] Method Examples
class Widget extends FuseDataModel {
public function manual_query() {
$query_obj = $this->db->new_query_obj();
// Add "SELECT widget_id, widget_name" to our query
$query_obj->select( 'widget_id' );
$query_obj->select( 'widget_name' );
// Add "FROM widgets"
$query_obj->from( 'widgets' );
// join our widget_extras table
$query_obj->join( "LEFT JOIN widget_extras ON widget_extras.widget_id = widgets.widget_id" );
// Add a WHERE clause
$query_obj->where( "widgets.widget_name like 'A%'" );
// Order our query
$query_obj->order_by( "widgets.widget_name ASC" );
// Set a limit
$query_obj->limit( 50 );
// Generate the query:
$query_as_string = $query_obj->generate_sql_query();
// For debugging, maybe we want to see our query:
echo $query_as_string;
//now run the query:
$result = $this->db->query( $query_as_string );
}
public function add_some_data() {
$query_obj = $this->db->new_query_obj();
$query_obj->add_insert_data( 'first_name', 'Jim', 's' );
$query_obj->add_insert_data( 'last_name', 'Keller', 's' );
$query_obj->add_insert_data( 'join_date', 'CURDATE()', 'f' );
$query_obj->auto_insert( 'members' );
}
public function update_some_data() {
$query_obj = $this->db->new_query_obj();
$query_obj->add_update_data( 'first_name', 'Jon', 's' );
$query_obj->add_update_data( 'last_name', 'Keller', 's' );
$query_obj->add_update_data( 'join_date', '2007-11-25', 's' );
$query_obj->auto_update( 'members', "WHERE member_id=2" );
}
}
Note: it is best practice not to use literal table and field names in a FuseDataModel as shown above. Instead, use $this->table_name and $this->db_field_name_field_key.
So, our from() call above would become $query_obj->from( $this->table_name ), and our widget_id selection below would become $query_obj->select( $this->db_field_name_id )
However, to simplify the look of the code, we've used the literal table and field names in this example.
