/**
* create the from clause
*
* @param array $tables tables that need to be included in this from clause
* if null, return mimimal from clause (i.e. civicrm_contact)
* @param array $inner tables that should be inner-joined
* @param array $right tables that should be right-joined
*
* @return string the from clause
* @access public
* @static
*/
static function fromClause(&$tables, $inner = NULL, $right = NULL, $primaryLocation = TRUE, $mode = 1)
{
$from = ' FROM civicrm_contact contact_a';
if (empty($tables)) {
return $from;
}
if (CRM_Utils_Array::value('civicrm_worldregion', $tables)) {
$tables = array_merge(array('civicrm_country' => 1), $tables);
}
if ((CRM_Utils_Array::value('civicrm_state_province', $tables) || CRM_Utils_Array::value('civicrm_country', $tables) || CRM_Utils_Array::value('civicrm_county', $tables)) && !CRM_Utils_Array::value('civicrm_address', $tables)) {
$tables = array_merge(array('civicrm_address' => 1), $tables);
}
// add group_contact table if group table is present
if (CRM_Utils_Array::value('civicrm_group', $tables) && !CRM_Utils_Array::value('civicrm_group_contact', $tables)) {
$tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added'";
}
// add group_contact and group table is subscription history is present
if (CRM_Utils_Array::value('civicrm_subscription_history', $tables) && !CRM_Utils_Array::value('civicrm_group', $tables)) {
$tables = array_merge(array('civicrm_group' => 1, 'civicrm_group_contact' => 1), $tables);
}
// to handle table dependencies of components
CRM_Core_Component::tableNames($tables);
//format the table list according to the weight
$info = CRM_Core_TableHierarchy::info();
foreach ($tables as $key => $value) {
$k = 99;
if (strpos($key, '-') !== FALSE) {
$keyArray = explode('-', $key);
$k = CRM_Utils_Array::value('civicrm_' . $keyArray[1], $info, 99);
} elseif (strpos($key, '_') !== FALSE) {
$keyArray = explode('_', $key);
if (is_numeric(array_pop($keyArray))) {
$k = CRM_Utils_Array::value(implode('_', $keyArray), $info, 99);
} else {
$k = CRM_Utils_Array::value($key, $info, 99);
}
} else {
$k = CRM_Utils_Array::value($key, $info, 99);
}
$tempTable[$k . ".{$key}"] = $key;
}
ksort($tempTable);
$newTables = array();
foreach ($tempTable as $key) {
$newTables[$key] = $tables[$key];
}
$tables = $newTables;
foreach ($tables as $name => $value) {
if (!$value) {
continue;
}
if (CRM_Utils_Array::value($name, $inner)) {
$side = 'INNER';
} elseif (CRM_Utils_Array::value($name, $right)) {
$side = 'RIGHT';
} else {
$side = 'LEFT';
}
if ($value != 1) {
// if there is already a join statement in value, use value itself
if (strpos($value, 'JOIN')) {
$from .= " {$value} ";
} else {
$from .= " {$side} JOIN {$name} ON ( {$value} ) ";
}
continue;
}
switch ($name) {
case 'civicrm_address':
if ($primaryLocation) {
$from .= " {$side} JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )";
} else {
$from .= " {$side} JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) ";
}
continue;
case 'civicrm_phone':
$from .= " {$side} JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) ";
continue;
case 'civicrm_email':
$from .= " {$side} JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) ";
continue;
case 'civicrm_im':
$from .= " {$side} JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) ";
continue;
case 'im_provider':
$from .= " {$side} JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id) ";
$from .= " {$side} JOIN civicrm_option_group option_group_imProvider ON option_group_imProvider.name = 'instant_messenger_service'";
$from .= " {$side} JOIN civicrm_option_value im_provider ON (civicrm_im.provider_id = im_provider.value AND option_group_imProvider.id = im_provider.option_group_id)";
continue;
case 'civicrm_openid':
$from .= " {$side} JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id AND civicrm_openid.is_primary = 1 )";
continue;
case 'civicrm_state_province':
$from .= " {$side} JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id ";
continue;
case 'civicrm_country':
$from .= " {$side} JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id ";
continue;
case 'civicrm_worldregion':
$from .= " {$side} JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ";
continue;
case 'civicrm_county':
$from .= " {$side} JOIN civicrm_county ON civicrm_address.county_id = civicrm_county.id ";
continue;
case 'civicrm_location_type':
$from .= " {$side} JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id ";
continue;
case 'civicrm_group':
$from .= " {$side} JOIN civicrm_group ON civicrm_group.id = civicrm_group_contact.group_id ";
continue;
case 'civicrm_group_contact':
$from .= " {$side} JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id ";
continue;
case 'civicrm_activity':
case 'civicrm_activity_tag':
case 'activity_type':
case 'activity_status':
case 'civicrm_activity_contact':
$from .= CRM_Activity_BAO_Query::from($name, $mode, $side);
continue;
case 'civicrm_entity_tag':
$from .= " {$side} JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_entity_tag.entity_id = contact_a.id ) ";
continue;
case 'civicrm_note':
$from .= " {$side} JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND\n contact_a.id = civicrm_note.entity_id ) ";
continue;
case 'civicrm_subscription_history':
$from .= " {$side} JOIN civicrm_subscription_history\n ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id\n AND civicrm_group_contact.group_id = civicrm_subscription_history.group_id";
continue;
case 'individual_prefix':
$from .= " {$side} JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix')";
$from .= " {$side} JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id ) ";
continue;
case 'individual_suffix':
$from .= " {$side} JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix')";
$from .= " {$side} JOIN civicrm_option_value individual_suffix ON (contact_a.suffix_id = individual_suffix.value AND option_group_suffix.id = individual_suffix.option_group_id ) ";
continue;
case 'gender':
$from .= " {$side} JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender')";
$from .= " {$side} JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id) ";
continue;
case 'civicrm_relationship':
if (self::$_relType == 'reciprocal') {
$from .= " {$side} JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id)";
$from .= " {$side} JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.contact_id_b = contact_b.id)";
} elseif (self::$_relType == 'b') {
$from .= " {$side} JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id )";
$from .= " {$side} JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )";
} else {
$from .= " {$side} JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id )";
$from .= " {$side} JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )";
}
continue;
case 'civicrm_log':
$from .= " {$side} JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')";
$from .= " {$side} JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id)";
continue;
case 'civicrm_tag':
$from .= " {$side} JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id ";
continue;
case 'civicrm_task_status':
$from .= " {$side} JOIN civicrm_task_status ON ( civicrm_task_status.responsible_entity_table = 'civicrm_contact'\n AND contact_a.id = civicrm_task_status.responsible_entity_id )";
continue;
case 'civicrm_grant':
$from .= CRM_Grant_BAO_Query::from($name, $mode, $side);
continue;
//build fromClause for email greeting, postal greeting, addressee CRM-4575
//build fromClause for email greeting, postal greeting, addressee CRM-4575
case 'email_greeting':
$from .= " {$side} JOIN civicrm_option_group option_group_email_greeting ON (option_group_email_greeting.name = 'email_greeting')";
$from .= " {$side} JOIN civicrm_option_value email_greeting ON (contact_a.email_greeting_id = email_greeting.value AND option_group_email_greeting.id = email_greeting.option_group_id ) ";
continue;
case 'postal_greeting':
$from .= " {$side} JOIN civicrm_option_group option_group_postal_greeting ON (option_group_postal_greeting.name = 'postal_greeting')";
$from .= " {$side} JOIN civicrm_option_value postal_greeting ON (contact_a.postal_greeting_id = postal_greeting.value AND option_group_postal_greeting.id = postal_greeting.option_group_id ) ";
continue;
case 'addressee':
$from .= " {$side} JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee')";
$from .= " {$side} JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id ) ";
continue;
case 'civicrm_website':
$from .= " {$side} JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id ";
continue;
default:
$from .= CRM_Core_Component::from($name, $mode, $side);
continue;
}
}
return $from;
}
/**
* Create the from clause.
*
* @param array $tables
* Tables that need to be included in this from clause.
* if null, return mimimal from clause (i.e. civicrm_contact)
* @param array $inner
* Tables that should be inner-joined.
* @param array $right
* Tables that should be right-joined.
*
* @param bool $primaryLocation
* @param int $mode
*
* @return string
* the from clause
*/
public static function fromClause(&$tables, $inner = NULL, $right = NULL, $primaryLocation = TRUE, $mode = 1)
{
$from = ' FROM civicrm_contact contact_a';
if (empty($tables)) {
return $from;
}
if (!empty($tables['civicrm_worldregion'])) {
$tables = array_merge(array('civicrm_country' => 1), $tables);
}
if ((!empty($tables['civicrm_state_province']) || !empty($tables['civicrm_country']) || CRM_Utils_Array::value('civicrm_county', $tables)) && empty($tables['civicrm_address'])) {
$tables = array_merge(array('civicrm_address' => 1), $tables);
}
// add group_contact and group_contact_cache table if group table is present
if (!empty($tables['civicrm_group'])) {
if (empty($tables['civicrm_group_contact'])) {
$tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added' ";
}
if (empty($tables['civicrm_group_contact_cache'])) {
$tables['civicrm_group_contact_cache'] = " LEFT JOIN civicrm_group_contact_cache ON civicrm_group_contact_cache.contact_id = contact_a.id ";
}
}
// add group_contact and group table is subscription history is present
if (!empty($tables['civicrm_subscription_history']) && empty($tables['civicrm_group'])) {
$tables = array_merge(array('civicrm_group' => 1, 'civicrm_group_contact' => 1), $tables);
}
// to handle table dependencies of components
CRM_Core_Component::tableNames($tables);
// to handle table dependencies of hook injected tables
CRM_Contact_BAO_Query_Hook::singleton()->setTableDependency($tables);
//format the table list according to the weight
$info = CRM_Core_TableHierarchy::info();
foreach ($tables as $key => $value) {
$k = 99;
if (strpos($key, '-') !== FALSE) {
$keyArray = explode('-', $key);
$k = CRM_Utils_Array::value('civicrm_' . $keyArray[1], $info, 99);
} elseif (strpos($key, '_') !== FALSE) {
$keyArray = explode('_', $key);
if (is_numeric(array_pop($keyArray))) {
$k = CRM_Utils_Array::value(implode('_', $keyArray), $info, 99);
} else {
$k = CRM_Utils_Array::value($key, $info, 99);
}
} else {
$k = CRM_Utils_Array::value($key, $info, 99);
}
$tempTable[$k . ".{$key}"] = $key;
}
ksort($tempTable);
$newTables = array();
foreach ($tempTable as $key) {
$newTables[$key] = $tables[$key];
}
$tables = $newTables;
foreach ($tables as $name => $value) {
if (!$value) {
continue;
}
if (!empty($inner[$name])) {
$side = 'INNER';
} elseif (!empty($right[$name])) {
$side = 'RIGHT';
} else {
$side = 'LEFT';
}
if ($value != 1) {
// if there is already a join statement in value, use value itself
if (strpos($value, 'JOIN')) {
$from .= " {$value} ";
} else {
$from .= " {$side} JOIN {$name} ON ( {$value} ) ";
}
continue;
}
switch ($name) {
case 'civicrm_address':
if ($primaryLocation) {
$from .= " {$side} JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )";
} else {
//CRM-14263 further handling of address joins further down...
$from .= " {$side} JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) ";
}
continue;
case 'civicrm_phone':
$from .= " {$side} JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) ";
continue;
case 'civicrm_email':
$from .= " {$side} JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) ";
continue;
case 'civicrm_im':
$from .= " {$side} JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) ";
continue;
case 'im_provider':
$from .= " {$side} JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id) ";
$from .= " {$side} JOIN civicrm_option_group option_group_imProvider ON option_group_imProvider.name = 'instant_messenger_service'";
$from .= " {$side} JOIN civicrm_option_value im_provider ON (civicrm_im.provider_id = im_provider.value AND option_group_imProvider.id = im_provider.option_group_id)";
continue;
case 'civicrm_openid':
$from .= " {$side} JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id AND civicrm_openid.is_primary = 1 )";
continue;
case 'civicrm_worldregion':
$from .= " {$side} JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id ";
$from .= " {$side} JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ";
continue;
case 'civicrm_location_type':
$from .= " {$side} JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id ";
continue;
case 'civicrm_group':
$from .= " {$side} JOIN civicrm_group ON (civicrm_group.id = civicrm_group_contact.group_id OR civicrm_group.id = civicrm_group_contact_cache.group_id) ";
continue;
case 'civicrm_group_contact':
$from .= " {$side} JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id ";
continue;
case 'civicrm_group_contact_cache':
$from .= " {$side} JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id ";
continue;
case 'civicrm_activity':
case 'civicrm_activity_tag':
case 'activity_type':
case 'activity_status':
case 'parent_id':
case 'civicrm_activity_contact':
case 'source_contact':
$from .= CRM_Activity_BAO_Query::from($name, $mode, $side);
continue;
case 'civicrm_entity_tag':
$from .= " {$side} JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_entity_tag.entity_id = contact_a.id ) ";
continue;
case 'civicrm_note':
$from .= " {$side} JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND\n contact_a.id = civicrm_note.entity_id ) ";
continue;
case 'civicrm_subscription_history':
$from .= " {$side} JOIN civicrm_subscription_history\n ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id\n AND civicrm_group_contact.group_id = civicrm_subscription_history.group_id";
continue;
case 'civicrm_relationship':
if (self::$_relType == 'reciprocal') {
if (self::$_relationshipTempTable) {
// we have a temptable to join on
$tbl = self::$_relationshipTempTable;
$from .= " INNER JOIN {$tbl} civicrm_relationship ON civicrm_relationship.contact_id = contact_a.id";
} else {
$from .= " {$side} JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id)";
$from .= " {$side} JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.contact_id_b = contact_b.id)";
}
} elseif (self::$_relType == 'b') {
$from .= " {$side} JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id )";
$from .= " {$side} JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )";
} else {
$from .= " {$side} JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id )";
$from .= " {$side} JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )";
}
continue;
case 'civicrm_log':
$from .= " INNER JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')";
$from .= " INNER JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id)";
continue;
case 'civicrm_tag':
$from .= " {$side} JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id ";
continue;
case 'civicrm_grant':
$from .= CRM_Grant_BAO_Query::from($name, $mode, $side);
continue;
case 'civicrm_website':
$from .= " {$side} JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id ";
continue;
default:
$locationTypeName = '';
if (strpos($name, '-address') != 0) {
$locationTypeName = 'address';
} elseif (strpos($name, '-phone') != 0) {
$locationTypeName = 'phone';
} elseif (strpos($name, '-email') != 0) {
$locationTypeName = 'email';
} elseif (strpos($name, '-im') != 0) {
$locationTypeName = 'im';
} elseif (strpos($name, '-openid') != 0) {
$locationTypeName = 'openid';
}
if ($locationTypeName) {
//we have a join on an location table - possibly in conjunction with search builder - CRM-14263
$parts = explode('-', $name);
$locationID = array_search($parts[0], CRM_Core_BAO_Address::buildOptions('location_type_id', 'get', array('name' => $parts[0])));
$from .= " {$side} JOIN civicrm_{$locationTypeName} `{$name}` ON ( contact_a.id = `{$name}`.contact_id ) and `{$name}`.location_type_id = {$locationID} ";
} else {
$from .= CRM_Core_Component::from($name, $mode, $side);
}
$from .= CRM_Contact_BAO_Query_Hook::singleton()->buildSearchfrom($name, $mode, $side);
continue;
}
}
return $from;
}