Advanced Oop Join Function

[GoObaAhH]

Sup everyone!

 

I have been smashing my head on my keyboard all day trying to get my new function to work. I don't think I'm calling it correctly... maybe the function itself is doodoo, although I've went over it and over it, the logic seems sound to me. The purpose of it is to automatically build the join mysql statement given the relevant parameters. It's supposed to be used in conjunction with other model functions I've wrote which simply execute the query (Let me know if you need those too).

 

Typical joins setup. 2 tables, Users, Company.

Relevant User columns are: id, company_id.

Relevant Company columns are: id, name

 

The join should collect company.name where user.id = company.id. (not correct syntax, that's just how I talk )

 

 

/**
* Add join(s) to SQL query. Use in conjunction with Model::get() or Model::getAll()
*
* @param array $member
* @param string $table Table name
* @param string $alias Table alias
* @param array $clauses
* @param array $fields List of field names to select. Support aliases. Example: 'TR.role' or 'TR.role.role_title'
* @param string $type Optional. Default value is 'left'. Possible values are: 'left', 'inner', 'cross', 'right', 'natural', 'straight'
* @access public
* @todo Support of <, >, <=, >=, etc.; Support of OR, XOR, etc.
* @example
* $UserModel->addJoin($UserModel->joins, $CompanyModel->getTable(), 'TR', array('TR.id' => 't1.id'), array('TR.name'));
* $UserModel->get(5);
*
*/
function addJoin(&$member, $table, $alias, $clauses=array(), $fields = array(), $type='left')
{
 if (in_array($type, $this->joinTypes))
 {
  $type_join = ($type !== 'straight') ? strtoupper($type) : strtoupper($type) . "_JOIN";
  if (in_array($type, array('left', 'right')))
  {
   $type_join .= " OUTER";
  }
  $join_str  = ($type !== 'straight') ? 'JOIN' : null;

 } else {
  return false;
 }
 if (count($clauses) > 0)
 {
  $clauses_arr = array();
  foreach ($clauses as $k => $v)
  {
   $key = (strpos($k, ".") !== false) ? explode(".", $k) : $k;
   $val = (strpos($v, ".") !== false) ? explode(".", $v) : $v;

   $l_clause = (is_array($key)) ? "`" . join("`.`", $key) . "`" : $key;
   $r_clause = (is_array($val)) ? "`" . join("`.`", $val) . "`" : $val;

   # TODO support of <, >, <=, >=, etc.
   $clauses_arr[] = "$l_clause = $r_clause";
   //$clauses_arr[] = "$k = $v";
  }
  # TODO support of OR, XOR, etc.
  $clauses_str = join(" AND ", $clauses_arr);
 } else {
  return false;
 }

 $member[] = $type_join . " " . $join_str . " `" .$table . "` AS `" . $alias . "` ON " . $clauses_str . "|" . join(",", $fields);
 $member = array_unique($member);
}


And then the function call:
$UserModel->addJoin($CompanyTable, $UsersTable, 'C', array('C.id' => 't1.id'), array('C.name'));

 

this simply does nothing... no result, no join built, no fatal error.... nothing.

 

Come on PHP freaks... blow my mind!

 

 

THANKS!

[Barand]

Have you tried echoing the resulting query?

[Christian F.]

Also, add some error logging where you return false. Problem might be there.

 

That said, I find the way you're treating $member quite odd. The comment indicates that it's an object, but you're treating it as it's an array. Also, what's that concatenation of the field name(s) at the end of it? Is this for splitting and manually adding to the field list upon final query build?

If so, how do you handle multiple joins with the same table/field definitions, or rather, removing joins?

 

Not quite sure exactly what it is, but something in that code seems odd to my subconsciousness. :\

 

Anyway, here's an example of how I did it in my class. Doesn't support multiple JOIN conditions (yet), but might be of help still.

 

	/**
 * Adds a join condition to the query.
 *
 * Join types are defined in the DB_JOIN_TARGETS constant.
 *
 * @internal This will (probably) need to be rewritten for other DBMS.
 *
 * @param string $Type The join type to use.
 * @param string $JTable The table to join to the query.
 * @param string $Alias The alias for the joined table, can be empty string if not wanted.
 * @param string $JField The field to do test the join condition on.
 * @param string $STAble The source table, or test condition.
 * @param string[optional] $SField If given, and $STable contains a table name, match against the $JField.
 */
public function Add_Join ($Type, $JTable, $Alias, $JField, $STable, $SField = false) {
	// Validate the JOIN type.
	if (!preg_match ($this->JoinTypes, $Type, $Matches)) {
		trigger_error ("{22} $Type not a valid JOIN type", E_USER_ERROR);
	}

	// Quote the table name to prevent collision with reserved words.
	if (substr ($JTable, 0, 1) != "`") {
		$JTable = "`$JTable`";
	}

	// Add the alias definition to the table name.
	if (!empty ($Alias)) {
		$Alias = "`$Alias`";
		$JTable .= " AS $Alias";
	} else {
		// No alias set, use full table name.
		$Alias = $JTable;
	}

	// Quote the field name to prevent collision with reserved words.
	if (substr ($JField, 0, 1) != "`") {
		$JField = "`$JField`";
	}

	// Determine whether a FOREIGN KEY or value check is to be done.
	if (isset ($SField)) {
		// Quote the table name to prevent collision with reserved words.
		if (substr ($STable, 0, 1) != "`") {
			$STable = "`$STable`";
		}

		// Quote the field name to prevent collision with reserved words.
		if (substr ($SField, 0, 1) != "`") {
			$SField = "`$SField`";
		}

		// Build the right side of the FOREIGN KEY condition statement.
		$Cond = $STable.'.'.$SField;
/** @internal Not sure if this is needed, but left behind just in case.
	} elseif (preg_match ("/^IS (?:NOT )?(?:TRUE|FALSE|UNKNOWN|NULL)\\z/u", $STable)) {
		$Cond = $STable;
*/
	} else {
		// Escape the value for the right side of the condition statement.
		$Cond = $this->Quote_Smart ($STable);
	}

	// Array definition: {Table[ AS Alias] => {Alias, Field, Condition, Join_Type}}
	$this->Join[$JTable] = array ($Alias, $JField, $Cond, $Matches[1]);
}

 

[GoObaAhH]

Christian,

 

Thanks so much for looking into this for me. Honestly, I hadn't noticed the inconsistent treatment of $member until you mentioned it. Essentially yes, the concatenation should 'theoretically' be used to handle multiple and or independent join queries at the same time. Multiple joins with the same definitions or the 'result' is handled by the get() or getAll() functions. getAll() being the one able to handle to multiple joins or removals.... which I just now realized I haven't used in my code to handle the result - therefore there wouldn't be one!

 

I'll try a few things and keep you updated. I like your style,Christian - very practical! I tend to over complicate things myself.

 

 

[Christian F.]

You're welcome, glad to hear that I was able to help you figure something out.

 

Also, thanks for the comment about my style; I do like to keep things simple, as it makes everything, well... Simple.