/**
* get_join_statement
* @param string $model_relation_chain
* @return string
* @throws \EE_Error
*/
public function get_join_statement($model_relation_chain)
{
//create the sql string like
$this_table_fk_field = $this->get_this_model()->get_foreign_key_to($this->get_other_model()->get_this_model_name());
$other_table_pk_field = $this->get_other_model()->get_primary_key_field();
$this_table_alias = EE_Model_Parser::extract_table_alias_model_relation_chain_prefix($model_relation_chain, $this->get_this_model()->get_this_model_name()) . $this_table_fk_field->get_table_alias();
$other_table_alias = EE_Model_Parser::extract_table_alias_model_relation_chain_prefix($model_relation_chain, $this->get_other_model()->get_this_model_name()) . $other_table_pk_field->get_table_alias();
$other_table = $this->get_other_model()->get_table_for_alias($other_table_alias);
return $this->_left_join($other_table, $other_table_alias, $other_table_pk_field->get_table_column(), $this_table_alias, $this_table_fk_field->get_table_column()) . $this->get_other_model()->_construct_internal_join_to_table_with_alias($other_table_alias);
}
function get_join_statement($model_relation_chain)
{
//create the sql string like
// LEFT JOIN other_table AS table_alias ON this_table_alias.pk = other_table_alias.fk extra_join_conditions
$this_table_pk_field = $this->get_this_model()->get_primary_key_field();
$other_table_fk_field = $this->get_other_model()->get_foreign_key_to($this->get_this_model()->get_this_model_name());
$pk_table_alias = EE_Model_Parser::extract_table_alias_model_relation_chain_prefix($model_relation_chain, $this->get_this_model()->get_this_model_name()) . $this_table_pk_field->get_table_alias();
$fk_table_alias = EE_Model_Parser::extract_table_alias_model_relation_chain_prefix($model_relation_chain, $this->get_other_model()->get_this_model_name()) . $other_table_fk_field->get_table_alias();
$fk_table = $this->get_other_model()->get_table_for_alias($fk_table_alias);
$field_with_model_name = $this->get_other_model()->get_field_containing_related_model_name();
return $this->_left_join($fk_table, $fk_table_alias, $other_table_fk_field->get_table_column(), $pk_table_alias, $this_table_pk_field->get_table_column(), $field_with_model_name->get_qualified_column() . "='" . $this->get_this_model()->get_this_model_name() . "'") . $this->get_other_model()->_construct_internal_join_to_table_with_alias($fk_table_alias);
}
/**
* Produces join SQL like get_join_sql, except instead of joining the primary table to the
* secondary table, does the inverse: joins the secondary table to the primary one. (Eg, isntead of
* " LEFT JOIN secondary_table_table AS Secondary ON ..." like get_join_sql, this function returns
* " LEFT JOIN primary_table AS Primary ON ...".
* This is useful if the secondary table is already included in the SQL, but the primary table is not yet.
* @return string
*/
function get_inverse_join_sql($secondary_table_alias_with_model_chain_prefix)
{
$primary_table_name = $this->get_table_to_join_with()->get_table_name();
$primary_table_alias = EE_Model_Parser::get_prefix_from_table_alias_with_model_relation_chain_prefix($secondary_table_alias_with_model_chain_prefix) . $this->get_table_to_join_with()->get_table_alias();
$primary_table_pk = $this->get_table_to_join_with()->get_pk_column();
//$this->get_pk_column();
$fk = $this->get_fk_on_table();
$join_sql = " LEFT JOIN {$primary_table_name} AS {$primary_table_alias} ON {$primary_table_alias}.{$primary_table_pk} = {$secondary_table_alias_with_model_chain_prefix}.{$fk} ";
if ($this->get_extra_join_conditions()) {
$join_sql .= "AND " . $this->get_extra_join_conditions();
}
return $join_sql;
}
/**
* Gets the SQL string for joining the join table to the other model's pk's table. Eg "LEFT JOIN real_other_table AS other_table_alias ON join_table_alias.fk_to_other_table = other_table_alias.pk"
* If you want to join between modelA -> joinModelAB -> modelB (eg, Event -> Event_Question_Group -> Question_Group),
* you should prepend the result of this function with results from get_join_to_intermediate_model_statement(),
* so that you join first to the intermediate join table, and then to the other model's pk's table
*
* @param string $model_relation_chain like 'Event.Event_Venue.Venue'
* @return string of SQL
* @throws \EE_Error
*/
public function get_join_statement($model_relation_chain)
{
if ($this->_model_relation_chain_to_join_model === NULL) {
throw new EE_Error(sprintf(__('When using EE_HABTM_Relation to create a join, you must call get_join_to_intermediate_model_statement BEFORE get_join_statement', 'event_espresso')));
}
$join_table_fk_field_to_this_table = $this->get_join_model()->get_foreign_key_to($this->get_this_model()->get_this_model_name());
$join_table_alias = EE_Model_Parser::extract_table_alias_model_relation_chain_prefix($this->_model_relation_chain_to_join_model, $this->get_join_model()->get_this_model_name()) . $join_table_fk_field_to_this_table->get_table_alias();
$other_table_pk_field = $this->get_other_model()->get_primary_key_field();
$join_table_fk_field_to_other_table = $this->get_join_model()->get_foreign_key_to($this->get_other_model()->get_this_model_name());
$other_table_alias = EE_Model_Parser::extract_table_alias_model_relation_chain_prefix($model_relation_chain, $this->get_other_model()->get_this_model_name()) . $other_table_pk_field->get_table_alias();
$other_table = $this->get_other_model()->get_table_for_alias($other_table_alias);
$SQL = $this->_left_join($other_table, $other_table_alias, $other_table_pk_field->get_table_column(), $join_table_alias, $join_table_fk_field_to_other_table->get_table_column()) . $this->get_other_model()->_construct_internal_join_to_table_with_alias($other_table_alias);
return $SQL;
}
/**
* Gets the actual table for the table alias
* @param string $table_alias eg Event, Event_Meta, Registration, Transaction, but maybe
* a table alias with a model chain prefix, like 'Venue__Event_Venue___Event_Meta'. Either one works
* @return EE_Table_Base
*/
function get_table_for_alias($table_alias)
{
$table_alias_sans_model_relation_chain_prefix = EE_Model_Parser::remove_table_alias_model_relation_chain_prefix($table_alias);
return $this->_tables[$table_alias_sans_model_relation_chain_prefix]->get_table_name();
}
/**
* Replaces the specified model in teh model relation chain with teh join model.
* Eg EE_Model_Parser::replace_model_name_with_join_model_name_in_model_relation_chain(
* "Ticket", "Datetime_Ticket", "Datetime.Ticket" ) will return
* "Datetime.Datetime_Ticket" which can be used to find the table alias model relation chain prefix
* using EE_Model_Parser::extract_table_alias_model_relation_chain_prefix
* @param string $model_name
* @param string $join_model_name
* @param string $model_relation_chain
* @return string
*/
public static function replace_model_name_with_join_model_name_in_model_relation_chain($model_name, $join_model_name, $model_relation_chain)
{
$model_name = EE_Model_Parser::pad_with_periods($model_name);
$join_model_name = EE_Model_Parser::pad_with_periods($join_model_name);
$model_relation_chain = EE_Model_Parser::pad_with_periods($model_relation_chain);
$replaced_with_periods = str_replace($model_name, $join_model_name, $model_relation_chain);
return EE_Model_Parser::trim_periods($replaced_with_periods);
}
