A helper class to perform various kinds of intersections across related tables.

sql-intersect-helper.php

<?php

/**
 * External logic for a complex search helper - keeps main files cleaner
 */

class SqlIntersectHelper{
	// Outersects are the same as intersects but data lists without any entries will always be included in the result
	
	// Any - Any value from the criterion list must exist at least once in the data list 
	// Data list contains one or more of the criterion list
	const INTERSECT_ANY = "intersect_any";
	const OUTERSECT_ANY = "outersect_any";

	// All - Every value in the criterion list must exist in the data list (but the data list can contain values not in the criteria list)
	// Criterion list is a subset of the data list
	const INTERSECT_ALL = "intersect_all";
	const OUTERSECT_ALL = "outersect_all";

	// All Right - One or more values in the criterion list must exist in the data list BUT the data list cannot contain any values other than those contained in the criterion list
	// Data list is a subset of the criterion list
	const INTERSECT_ALL_RIGHT = "intersect_all_right";
	const OUTERSECT_ALL_RIGHT = "outersect_all_right";

	// None - No value in the criterion list must exist in the data list.
	const INTERSECT_NONE = "intersect_none";
	const OUTERSECT_NONE = "outersect_none";

	static $SQL_OPERATORS = array(
		"in", 
		"notin", 
		"<",
		">",
		"<=",
		">=",
		"like",
		"between",
		"=",
		"is",
		"isnot",
		"!="
	);

	static $INTERSECT_TYPES = array(
		self::INTERSECT_ANY,
		self::OUTERSECT_ANY,
		self::INTERSECT_ALL,
		self::OUTERSECT_ALL,
		self::INTERSECT_ALL_RIGHT,
		self::OUTERSECT_ALL_RIGHT,
		self::INTERSECT_NONE,
		self::OUTERSECT_NONE
	);


	public static function getDb(){
	}

	public static function performIntersectQuery($db, $query_options, $match_mode=self::INTERSECT_ALL){
		// Validate all of the provided data
		$is_outersect = in_array($match_mode, array(
			self::OUTERSECT_ANY, 
			self::OUTERSECT_ALL, 
			self::OUTERSECT_ALL_RIGHT,
			self::OUTERSECT_NONE
		));

		$allowed_keys = array("related_table_name", "related_table_join_id", "related_table_criteria", "match_test_criteria", "within_ids", "parent_table_name", "parent_table_pk");

		$required_keys = array(
			"related_table_name"=>"The name of the sub-table which is being matched against",
			"related_table_join_id"=>"The name of the sub-table field which joins to the core record",
			"related_table_criteria"=>"Any criteria (either criteria objects or an sql string) used to limit records returned by the ",
			"match_test_criteria"=>"Criteria (either criteria objects or an sql string) to filter matching related table records"
		);

		if ($is_outersect) {
			$required_keys = array_merge($required_keys, array(
				"within_ids"=>"Search within only a limited set of ids from the parent table",
				"parent_table_name"=>"The name of the parent table, used for outersect queries",
				"parent_table_pk"=>"The name of the id used in the parent table"
			));
		}


		foreach($required_keys as $key=>$key_description){
			if( !isset($query_options[$key]) ) {
				throw new InvalidArgumentException("query_options must contain '{$key}': {$key_description}");
			}
		}

		// Only proceed if we have been given the right number of options
		if (count($allowed_keys) != count($query_options)) {
			throw new InvalidArgumentException("query_options contains too many keys. The allowed keys are: " . implode(", ", array_keys($allowed_keys)));
		}

		// At this point, query_options is validated - extract the array into the local scope
		extract($query_options);

		// Ensure that extra criteria is an SQL string
		if (is_array($related_table_criteria)) {
			$related_table_criteria = self::constructWhereClause(self::prepareCriteriaArray($db, $related_table_name, $related_table_criteria));
		}

		$related_table_criteria = $related_table_criteria ?: "1=1";

		// Any within_ids need to be appended to the criteria for the 
		if ($within_ids) {
			$related_table_criteria = self::constructWhereClause(self::prepareCriteriaArray($db, $related_table_name, array(
				array(
					"name"=>$related_table_join_id,
					"type"=>"in",
					"value"=>$within_ids
				)
			))) . " AND " . $related_table_criteria;
		}

		
		// Stringify the match test criteria
		if (is_array($match_test_criteria)) {
			$match_test_criteria = self::constructWhereClause(self::prepareCriteriaArray($db, $related_table_name, $match_test_criteria));
		}


		// Determine the related table join query part
		$match_count_table_sql = "(
				SELECT {$related_table_name}.{$related_table_join_id} as 'id', count({$related_table_name}.{$related_table_join_id}) as 'count'
				FROM {$related_table_name}
				WHERE {$related_table_criteria} AND {$match_test_criteria}
				GROUP BY {$related_table_name}.{$related_table_join_id}
			) AS match_count ";

		$from_clause = "";
		$select_statement = "";

		// Determine the condition which will permit a successful count to 
		$mode_conditional = "";

		if (in_array($match_mode, array(self::INTERSECT_NONE, self::OUTERSECT_NONE))) {
			$union_query = "";

			if ($match_mode == self::INTERSECT_NONE) {
				$union_query = "
					SELECT {$related_table_name}.{$related_table_join_id} as 'id', 0 as 'count'
					FROM {$related_table_name}
					WHERE {$related_table_criteria}";
			}
			else if ($match_mode == self::OUTERSECT_NONE) {
				$union_query = "
					SELECT {$parent_table_name}.{$parent_table_pk} as 'id', 0 as 'count'
					FROM {$parent_table_name}
					WHERE {$parent_table_name}.{$parent_table_pk} IN (" . implode(", ", $db->quoteArray($within_ids)) . ")";
			}

			$select_statement = "match_count.id";

			$from_clause = "(
					SELECT list_count.id as 'id', SUM(list_count.count) as 'count'
					FROM (
						SELECT {$related_table_name}.{$related_table_join_id} as 'id', count({$related_table_name}.{$related_table_join_id}) as 'count'
						FROM {$related_table_name}
						WHERE {$related_table_criteria} AND {$match_test_criteria}
						GROUP BY {$related_table_name}.{$related_table_join_id}
						UNION
						{$union_query}
					) as list_count
					GROUP BY list_count.id
				) as match_count";

			$mode_conditional = "match_count.count = 0";
		}
		else if ($match_mode == self::OUTERSECT_ANY) {
			$select_statement = "total_count.id";

			$from_clause = "(
					SELECT {$parent_table_name}.{$parent_table_pk} as 'id', 0 as 'count'
					FROM {$parent_table_name}
					WHERE {$parent_table_name}.{$parent_table_pk} IN (" . implode(", ", $db->quoteArray($within_ids)) . ")
				) AS total_count
				LEFT JOIN {$match_count_table_sql} ON (match_count.id = total_count.id)";

			$mode_conditional = "match_count.count > 0 OR total_count.count = 0";
		}
		else if ($match_mode == self::INTERSECT_ANY) {
			$select_statement = "match_count.id";

			$from_clause = "{$match_count_table_sql}";

			$mode_conditional = "match_count.count > 0";
		}
		else if (in_array($match_mode, array(self::OUTERSECT_ALL, self::OUTERSECT_ALL_RIGHT))) {
			$select_statement = "total_count.id";

			$from_clause = "(
					SELECT id, SUM(count) as 'count'
					FROM (
						SELECT {$related_table_name}.{$related_table_join_id} as 'id', count({$related_table_name}.{$related_table_join_id}) as 'count'
						FROM {$related_table_name}
						WHERE {$related_table_criteria} 
						GROUP BY {$related_table_name}.{$related_table_join_id}

						UNION
						SELECT {$parent_table_name}.{$parent_table_pk} as 'id', 0 as 'count'
						FROM {$parent_table_name}
						WHERE {$parent_table_name}.{$parent_table_pk} IN (" . implode(", ", $db->quoteArray($within_ids)) . ")
					) as list_counts
					GROUP BY id
				) AS total_count
				LEFT JOIN {$match_count_table_sql} ON (match_count.id = total_count.id)";

			if ($match_mode == self::OUTERSECT_ALL) {
				$mode_conditional = "match_count.count = " . count($field_values) . " OR total_count.count = 0";
			}
			else if ($match_mode == self::OUTERSECT_ALL_RIGHT) {
				$mode_conditional = "match_count.count = total_count.count OR total_count.count = 0";
			}
		}
		else if (in_array($match_mode, array(self::INTERSECT_ALL, self::INTERSECT_ALL_RIGHT))) {
			$select_statement = "total_count.id";

			$from_clause = "(
					SELECT {$related_table_name}.{$related_table_join_id} as 'id', count({$related_table_name}.{$related_table_join_id}) as 'count'
					FROM {$related_table_name}
					WHERE {$related_table_criteria} 
					GROUP BY {$related_table_name}.{$related_table_join_id}
				) AS total_count
				INNER JOIN {$match_count_table_sql} ON (match_count.id = total_count.id)";

			if ($match_mode == self::INTERSECT_ALL) {
				$mode_conditional = "match_count.count = " . count($field_values) . "";
			}
			else if ($match_mode == self::INTERSECT_ALL_RIGHT) {
				$mode_conditional = "match_count.count = total_count.count";
			}
		}

		$search_sql = "
			SELECT {$select_statement}
			FROM {$from_clause}
			WHERE ({$mode_conditional})
		";

		$ids = $db->query($search_sql)->fetchAll(PDO::FETCH_COLUMN);

		return $ids;
	}

	/**
	 * Like implode, but recursively processes a criteria object.
	 * criteria values MUST be sanitized beforehand!
	 * @param array $criteria The fields to construct the criteria from, in the form [value, type, name]
	 * @param string $glue The string to insert between criteria values
	 * @return string All of the criteria stringified into an SQL where clause
	 */
	public static function constructWhereClause($criteria, $glue="AND") {
		$string_criteria = implode(" {$glue} ", array_map(function($val) {
			if (strcasecmp($val["type"], "or") === 0) {
				return self::constructWhereClause($val["value"], "OR");
			}
			else if (strcasecmp($val["type"], "between") === 0) {
				return implode(" ", array($val['name'], "BETWEEN", implode(' AND ', $val["value"])));
			}
			else if (strcasecmp($val["type"], "in") === 0) {
				return implode(" ", array($val['name'], "IN", '(' . implode(',', $val["value"]) . ')'));
			}
			else if (strcasecmp($val["type"], "notin") === 0) {
				return implode(" ", array($val['name'], "NOT IN", '(' . implode(',', $val["value"]) . ')'));
			}
			else {
				return implode(" ", array($val['name'], strtoupper($val['type']), $val["value"]));
			}
		}, $criteria));

		return "( {$string_criteria} )";
	}

	
	/**
	 * Prepares and sanitizes criteria values and appends a table alias to the name value of every
	 * criteria field.
	 * @param RealEvo_PDO $db 
	 * @param string $table_alias The string to insert before the field name as the table alias
	 * @param string $criteria The fields to construct the criteria from, in the form [value, type, name]
	 * @return string All of the criteria stringified into an SQL where clause
	 */
	public static function prepareCriteriaArray($db, $table_alias, $criteria) {
		return array_map(function($val) use ($db, $table_alias) {
			$val["name"] = $table_alias . "." . $val["name"];
			$val["value"] = $db->quote($val["value"]);
			$val["type"] = $val["type"] ? str_replace(" ","",strtolower($val['type'])) : "=";

			if (!in_array($val["type"], self::$SQL_OPERATORS)) {
				throw new Exception("'" . $val["type"] . "' is not a valid criterion type. Valid types are " . implode(', ', self::$SQL_OPERATORS));
			}

			if (isset($val["allow_null"]) && $val["allow_null"]) {
				return array(
					"type"=> "or",
					"value"=> array(
						array(
							"name"=>$val["name"],
							"type"=>"is",
							"value"=>"null"
						),
						$val
					)
				);
			}

			return $val;
		}, $criteria);
	}
}