function _user_relationships_generate_query in User Relationships 6
Same name and namespace in other branches
- 5.3 user_relationships_api/user_relationships_api.module \_user_relationships_generate_query()
- 5.2 user_relationships.module \_user_relationships_generate_query()
- 7 user_relationships.module \_user_relationships_generate_query()
Helper function to generate the main and count queries from a list of parameters and options
4 calls to _user_relationships_generate_query()
- template_preprocess_user_relationships in user_relationships_ui/
user_relationships_ui.module - Pre processor for user_relationships page
- template_preprocess_user_relationships_pending_requests in user_relationships_ui/
user_relationships_ui.module - Pre processor page for user_relationships_pending_requests page
- theme_user_relationship_implications_page in user_relationship_implications/
user_relationship_implications.module - Categorized list of relationships for a given user
- user_relationships_load in user_relationships_api/
user_relationships_api.api.inc - Load relationship objects from the database.
File
- user_relationships_api/
user_relationships_api.module, line 39 - User Relationships API. Module shell. @author Jeff Smick (creator) @author Alex Karshakevich (maintainer) http://drupal.org/user/183217
Code
function _user_relationships_generate_query($param = array(), $options = array()) {
extract($options, EXTR_SKIP);
// Doing a JOIN on the {user_relationship_types} table is rather slow as
// MySQL has to use ALL for the table join. On most sites, there will only be
// a few relationship types, and there will nearly always be a far higher
// number of relationships. So, we split the query out into PHP saving us
// from having to do the JOIN.
$relationship_types = user_relationships_types_load();
$oneway_relationship_type_ids = array();
foreach ($relationship_types as $rt) {
if ($rt->is_oneway) {
$oneway_relationship_type_ids[] = $rt->rtid;
}
}
$oneway_relationship_types_in = implode(",", $oneway_relationship_type_ids);
$twoway_reverse_clause = FALSE;
//#479486
// Turn the conditions into a query.
foreach ($param as $key => $value) {
if (!isset($value)) {
continue;
}
$operator = _user_relationship_process_query_argument($key, $value);
switch ($key) {
case 'between':
$between_cond = "((ur.requester_id {$operator[0]} AND ur.requestee_id {$operator[1]}) OR (";
//#479486 do not include reverse records of two-way relationships by default
if (!isset($include_twoway_reverse)) {
if (!empty($oneway_relationship_types_in)) {
$between_cond .= '(ur.approved <> 1 OR ur.rtid IN (' . $oneway_relationship_types_in . ')) AND ';
}
else {
$between_cond .= '(ur.approved <> 1) AND ';
}
$twoway_reverse_clause = TRUE;
}
$between_cond .= "ur.requestee_id {$operator[0]} AND ur.requester_id {$operator[1]}))";
$cond[] = $between_cond;
$arguments[] = $value[0];
$arguments[] = $value[1];
$arguments[] = $value[0];
$arguments[] = $value[1];
break;
case 'user':
//#479486 when showing all user's relationships, do not include reverse records of two-way relationships
if (!isset($include_twoway_reverse)) {
if (!empty($oneway_relationship_types_in)) {
$cond[] = "(ur.requester_id {$operator} OR ((ur.approved <> 1 OR ur.rtid IN (" . $oneway_relationship_types_in . ")) AND ur.requestee_id {$operator}))";
}
else {
$cond[] = "(ur.requester_id {$operator} OR (ur.approved <> 1 AND ur.requestee_id {$operator}))";
}
$twoway_reverse_clause = TRUE;
}
else {
$cond[] = "(ur.requester_id {$operator} OR ur.requestee_id {$operator})";
}
$arguments[] = $value;
$arguments[] = $value;
break;
case 'rid':
case 'requester_id':
case 'requestee_id':
$twoway_reverse_clause = TRUE;
//#479486 these columns automatically should exclude duplicates
default:
$types_cols = array(
'name',
'plural_name',
'is_oneway',
'is_reciprocal',
'requires_approval',
'expires_val',
);
$cond[] = "%s.%s {$operator}";
$arguments[] = !in_array($key, $types_cols) ? 'ur' : 'urt';
$arguments[] = $key;
//#358669 support having multiple values for a single column - the query already has IN(...) placeholders
if (!is_array($value)) {
$arguments[] = $value;
}
else {
$arguments = array_merge($arguments, $value);
}
}
}
//#479486 add a general clause that removed reverse direction from two-way relationship results, unless it's been addressed above
if (!$twoway_reverse_clause && !isset($include_twoway_reverse)) {
if (!empty($oneway_relationship_types_in)) {
$cond[] = '(ur.rtid IN (' . $oneway_relationship_types_in . ') OR ur.approved <> 1 OR ur.requester_id < ur.requestee_id)';
}
else {
$cond[] = '(ur.approved <> 1 OR ur.requester_id < ur.requestee_id)';
}
$twoway_reverse_clause = TRUE;
}
$selects = array(
'DISTINCT ur.rid',
'ur.*',
'urt.*',
);
$joins = array(
'INNER JOIN {user_relationship_types} urt ON ur.rtid = urt.rtid',
);
// We wont need anything after this point for the count SQL
$count_joins = implode(' ', $joins);
if (isset($include_user_info) && $include_user_info) {
$selects = array_merge($selects, array(
'requesters.name AS requester_name',
'requestees.name AS requestee_name',
'requesters.mail AS requester_mail',
'requestees.mail AS requestee_mail',
'requesters.data AS requester_data',
'requestees.data AS requestee_data',
'requesters.picture AS requester_picture',
'requestees.picture AS requestee_picture',
));
$joins = array_merge($joins, array(
'INNER JOIN {users} requesters ON ur.requester_id = requesters.uid',
'INNER JOIN {users} requestees ON ur.requestee_id = requestees.uid',
));
}
$selects = implode(',', $selects);
$joins = implode(' ', $joins);
$cond = $cond ? 'WHERE ' . implode(' AND ', $cond) : '';
$extra = array();
if (!empty($order)) {
$extra[] = "ORDER BY {$order}";
}
if (!empty($limit)) {
$extra[] = "LIMIT {$limit}";
}
$extra = is_array($extra) ? implode(' ', $extra) : $extra;
return array(
//331692 remove improper GROUP BY rid for Postgre
'query' => "SELECT {$selects} FROM {user_relationships} ur {$joins} {$cond} {$extra}",
'count' => "SELECT COUNT(DISTINCT rid) AS count FROM {user_relationships} ur {$count_joins} {$cond}",
'arguments' => $arguments,
);
}