anewt-developers team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 1707
committer: Wouter Bolsterlee <uws@xxxxxxxxx>
branch nick: anewt.uws
timestamp: Tue 2009-07-21 21:34:32 +0200
message:
  [autorecord] Support extra select columns and manual joins
  
  The db_find_one_by_sql() and db_find_all_by_sql methods()
  now support 'select', 'join', and 'table-alias' keys in the
  associative $sql array. Expanded documentation to describe
  how those can be used.
modified:
  autorecord/autorecord.lib.php

=== modified file 'autorecord/autorecord.lib.php'
--- autorecord/autorecord.lib.php	2009-07-18 16:50:37 +0000
+++ autorecord/autorecord.lib.php	2009-07-21 19:34:32 +0000
@@ -545,7 +545,7 @@
 	 */
 
 	/**
-	 * Find all records in the database
+	 * Find all records in the database table.
 	 *
 	 * \return
 	 *   Array of AnewtAutoRecord instances (may be empty)
@@ -572,7 +572,7 @@
 	}
 
 	/**
-	 * Find a single record by id.
+	 * Find a single record by primary key value.
 	 *
 	 * \param $value
 	 *   The primary key value of the record to retrieve
@@ -592,13 +592,27 @@
 	 *
 	 * The \c $sql argument can be:
 	 *
-	 * - a string: the part of the \c WHERE clause up to the end of the
-	 *   query
-	 * - an associative array with \c where, \c order-by, \c limit and \c offset
-	 *   keys, all optional
+	 * - a string: the part of the \c WHERE clause up to the end of the query
+	 * - an associative array with one or more of the following keys (with string values), all optional:
+	 *     - \c where for the \c WHERE clause
+	 *     - \c order-by for a custom <code>ORDER BY</code> to be used instead of
+	 *       the the order specified by the db_columns_order_by() method
+	 *     - \c limit for the \c LIMIT clause
+	 *     - \c offset for the \c OFFSET clause
+	 *     - \c select, which can be be used to provide additional columns for the
+	 *       \c SELECT part of the query, in addition to the standard columns
+	 *       specified in db_columns()
+	 *     - \c join, which will be inserted right after the \c FROM clause of
+	 *       the generated query, so that it is easy to create simple joins.
+	 *       Note that you should provide a complete string here just as you
+	 *       would in normal regular SQL queries, i.e. including the \c JOIN and
+	 *       \c ON or \c USING keywords.
+	 *     - \c table-alias for the table alias used for the main table
+	 *       (most useful if \c join is used as well)
 	 *
-	 * In both cases, <code>?str?</code>-style placeholders can be used, and the
-	 * \c $values array will be used to fill these placeholders.
+	 * In both cases, <code>?str?</code>-style placeholders can be used in the
+	 * values provided for the \c $sql parameter. The \c $values array will be
+	 * used to fill these placeholders.
 	 *
 	 * \param $sql  The constraints of the SQL query
 	 * \param $values  Array with placeholder values
@@ -611,7 +625,8 @@
 	}
 
 	/**
-	 * Find a single record by providing SQL contraints.
+	 * Find a single record by providing SQL contraints. See db_find_all_by_sql
+	 * for a detailed description of the \c $sql parameter.
 	 *
 	 * \param $sql
 	 *   Contraints of the SQL query
@@ -661,6 +676,8 @@
 	 *
 	 * \return
 	 *   AnewtAutoRecord instance (or NULL)
+	 *
+	 * \see db_find_all_by_column
 	 */
 	public static function db_find_one_by_column($column, $value)
 	{
@@ -668,7 +685,7 @@
 	}
 
 	/**
-	 * Find one or more records by id.
+	 * Find one or more records by primary key value.
 	 *
 	 * \param $class  Class name
 	 *
@@ -752,62 +769,118 @@
 		assert('is_array($values)');
 		assert('$connection instanceof AnewtDatabaseConnection');
 
-		$sql_select = AnewtAutoRecord::_db_sql_select($class, null, $connection);
-		$sql_from = AnewtAutoRecord::_db_sql_from($class, null, $connection);
-		$sql_order_by = AnewtAutoRecord::_db_sql_order_by($class, null, $connection);
-
-
-		/* No SQL: find all records */
+
+		/* Table alias.
+		 *
+		 * Several possibilities exist:
+		 * - no alias,
+		 * - alias provided explicitly, or
+		 * - not specified but still needed. */
+
+		$table_alias = null;
+
+		if (is_assoc_array($sql))
+		{
+			/* Table alias might be provided explicitly */
+			$table_alias = array_get_default($sql, 'table-alias', null);
+
+			/* If JOINs are used, a table alias must be used for all columns in
+			 * the SELECT clause to avoid ambiguous column names if the same
+			 * column names are used in one of the JOINed tables. If no
+			 * table-alias is provided explicitly, the table name is reused. */
+			if (is_null($table_alias) && array_key_exists('join', $sql))
+				$table_alias = call_user_func(array($class, 'db_table'));
+		}
+
+
+		/* Standard query parts */
+
+		$sql_select = AnewtAutoRecord::_db_sql_select($class, $table_alias, $connection);
+		$sql_from = AnewtAutoRecord::_db_sql_from($class, $table_alias, $connection);
+		$sql_order_by = AnewtAutoRecord::_db_sql_order_by($class, $table_alias, $connection);
+
+
+		/* Build the SQL query.
+		 *
+		 * There are three possibilities for the $sql parameter:
+		 * 1. null
+		 * 2. a string
+		 * 3. an associative array
+		 */
 
 		if (is_null($sql))
 		{
-			$sql_order_by_full = is_null($sql_order_by) ? '' : sprintf('ORDER BY %s', $sql_order_by);
+			/* No SQL: find all records */
+
+			$sql_order_by_full = is_null($sql_order_by)
+				? ''
+				: sprintf('ORDER BY %s', $sql_order_by);
+
 			$prepared_query = $connection->prepare('SELECT ?raw? FROM ?raw? ?raw?;');
 			$result_set = $prepared_query->execute($sql_select, $sql_from, $sql_order_by_full);
 		}
-
-
-		/* SQL string */
-
 		elseif (is_string($sql))
 		{
+			/* SQL string */
+
 			$sql_filled = $connection->create_sql_template($sql)->fillv($values);
 			$prepared_query = $connection->prepare('SELECT ?raw? FROM ?raw? ?raw?;');
 			$result_set = $prepared_query->execute($sql_select, $sql_from, $sql_filled);
 		}
-
-
-		/* Associative array with SQL */
-
 		else
 		{
+			/* Associative array with SQL */
+
 			$sql_parts = array();
 
+
+			/* SELECT and possible additions */
+
+			$sql_select_addition = array_get_default($sql, 'select', null);
+			if ($sql_select_addition)
+				$sql_select = sprintf('%s, %s', $sql_select, $sql_select_addition);
+
+
 			/* WHERE */
+
 			$sql_where = array_get_default($sql, 'where', null);
 			if (!is_null($sql_where))
 				$sql_parts[] = sprintf('WHERE %s', $sql_where);
 
+
+			/* JOIN */
+
+			$sql_join = array_get_default($sql, 'join', null);
+			if (!is_null($sql_join))
+				$sql_from = sprintf('%s %s', $sql_from, $sql_join);
+
+
 			/* ORDER BY */
+
 			$sql_order_by = array_get_default($sql, 'order-by', $sql_order_by);
 			if (!is_null($sql_order_by))
 				$sql_parts[] = sprintf('ORDER BY %s', $sql_order_by);
 
+
 			/* LIMIT. Note that "optimizing" this depending on the value of
 			 * $just_one_result is impossible since it may contain a placeholder
 			 * string and not a literal value. We take care of $just_one_result
 			 * when fetching the result rows. */
+
 			$sql_limit = array_get_default($sql, 'limit', null);
 			if (!is_null($sql_limit))
 				$sql_parts[] = sprintf('LIMIT %s', $sql_limit);
 
+
 			/* OFFSET */
+
 			$sql_offset = array_get_default($sql, 'offset', null);
 			if (!is_null($sql_offset))
 				$sql_parts[] = sprintf('OFFSET %s', $sql_offset);
 
 
 			/* Combine */
+
 			$sql_combined = $connection->create_sql_template(join(' ', $sql_parts))->fillv($values);
 			$prepared_query = $connection->prepare('SELECT ?raw? FROM ?raw? ?raw?;');
 			$result_set = $prepared_query->execute($sql_select, $sql_from, $sql_combined);



--
lp:anewt
https://code.launchpad.net/~uws/anewt/anewt.uws

Your team Anewt developers is subscribed to branch lp:anewt.
To unsubscribe from this branch go to https://code.launchpad.net/~uws/anewt/anewt.uws/+edit-subscription.