/**
* Returns all the children term IDs of the parent term ID recursively.
*
* @deprecated 8.1.6 Use {@see cnTerm::children()} instead.
* @see cnTerm::children()
*
* @param integer $id
* @param string $taxonomy
*
* @return array
*/
public function getTermChildrenIDs($id, $taxonomy)
{
return cnTerm::children($id, $taxonomy);
}
/**
*
* The $atts['meta_query'] can have two different structures when passed to
* @see cnMeta_Query::parse_query_vars(), they are:
*
* array(
* 'meta_key' => (string),
* 'meta_value => (string|array),
* 'meta_type => (string),
* 'meta_compare' => (string)
* )
*
* OR
*
* array(
* 'meta_query' =>
* array(
* 'key' => (string),
* 'value' => (string|array),
* 'compare' => (string),
* 'type' => (string)
* ),
* [array(...),]
* )
*
* The later, 'meta_query', can have multiple arrays.
*
* @access public
* @since unknown
* @version 1.0
* @param array
* @return array
*/
public function entries($atts = array())
{
/** @var $wpdb wpdb */
global $wpdb;
// Grab an instance of the Connections object.
$instance = Connections_Directory();
$select[] = CN_ENTRY_TABLE . '.*';
$from[] = CN_ENTRY_TABLE;
$join = array();
$where[] = 'WHERE 1=1';
$having = array();
$orderBy = array();
$random = FALSE;
$visibility = array();
/*
* // START -- Set the default attributes array. \\
*/
$defaults['list_type'] = NULL;
$defaults['category'] = NULL;
$defaults['category_in'] = NULL;
$defaults['exclude_category'] = NULL;
$defaults['category_name'] = NULL;
$defaults['category_slug'] = NULL;
$defaults['wp_current_category'] = FALSE;
$defaults['char'] = '';
$defaults['id'] = NULL;
$defaults['slug'] = NULL;
$defaults['family_name'] = NULL;
$defaults['last_name'] = NULL;
$defaults['title'] = NULL;
$defaults['organization'] = NULL;
$defaults['department'] = NULL;
$defaults['district'] = NULL;
$defaults['county'] = NULL;
$defaults['city'] = NULL;
$defaults['state'] = NULL;
$defaults['zip_code'] = NULL;
$defaults['country'] = NULL;
$defaults['visibility'] = NULL;
$defaults['status'] = array('approved');
$defaults['order_by'] = array('sort_column', 'last_name', 'first_name');
$defaults['limit'] = NULL;
$defaults['offset'] = 0;
$defaults['meta_query'] = array();
$defaults['allow_public_override'] = FALSE;
$defaults['private_override'] = FALSE;
$defaults['search_terms'] = NULL;
// $atts vars to support showing entries within a specified radius.
$defaults['near_addr'] = NULL;
$defaults['latitude'] = NULL;
$defaults['longitude'] = NULL;
$defaults['radius'] = 10;
$defaults['unit'] = 'mi';
$defaults['lock'] = FALSE;
$atts = cnSanitize::args($atts, $defaults);
/*
* // END -- Set the default attributes array if not supplied. \\
*/
/*
* // START -- Process the query vars. \\
* NOTE: these will override any values supplied via $atts, which include via the shortcode.
*/
if ((defined('DOING_AJAX') && DOING_AJAX || !is_admin()) && !$atts['lock']) {
// Category slug
$queryCategorySlug = cnQuery::getVar('cn-cat-slug');
if (!empty($queryCategorySlug)) {
// If the category slug is a descendant, use the last slug from the URL for the query.
$queryCategorySlug = explode('/', $queryCategorySlug);
if (isset($queryCategorySlug[count($queryCategorySlug) - 1])) {
$atts['category_slug'] = $queryCategorySlug[count($queryCategorySlug) - 1];
}
}
// Category ID
$queryCategoryID = cnQuery::getVar('cn-cat');
if (!empty($queryCategoryID)) {
$atts['category'] = $queryCategoryID;
}
// Category in
$queryCategoryIn = cnQuery::getVar('cn-cat-in');
if (!empty($queryCategoryIn)) {
$atts['category_in'] = cnQuery::getVar('cn-cat-in');
}
// Country
$queryCountry = cnQuery::getVar('cn-country');
if (!empty($queryCountry)) {
$atts['country'] = urldecode($queryCountry);
}
// Postal Code
$queryPostalCode = cnQuery::getVar('cn-postal-code');
if (!empty($queryPostalCode)) {
$atts['zip_code'] = urldecode($queryPostalCode);
}
// Region [State]
$queryRegion = cnQuery::getVar('cn-region');
if (!empty($queryRegion)) {
$atts['state'] = urldecode($queryRegion);
}
// Locality [City]
$queryLocality = cnQuery::getVar('cn-locality');
if (!empty($queryLocality)) {
$atts['city'] = urldecode($queryLocality);
}
// County
$queryCounty = cnQuery::getVar('cn-county');
if (!empty($queryCounty)) {
$atts['county'] = urldecode($queryCounty);
}
// District
$queryDistrict = cnQuery::getVar('cn-district');
if (!empty($queryDistrict)) {
$atts['district'] = urldecode($queryDistrict);
}
// Organization
$queryOrganization = cnQuery::getVar('cn-organization');
if (!empty($queryOrganization)) {
$atts['organization'] = urldecode($queryOrganization);
}
// Department
$queryDeparment = cnQuery::getVar('cn-department');
if (!empty($queryDeparment)) {
$atts['department'] = urldecode($queryDeparment);
}
// Entry slug
// NOTE: The entry slug is saved in the DB URL encoded, so there's no need to urldecode().
$queryEntrySlug = cnQuery::getVar('cn-entry-slug');
if (!empty($queryEntrySlug)) {
$atts['slug'] = $queryEntrySlug;
}
// Initial character.
$queryInitialChar = cnQuery::getVar('cn-char');
if (!empty($queryInitialChar)) {
$atts['char'] = urldecode($queryInitialChar);
}
// Pagination
$queryPage = cnQuery::getVar('cn-pg');
$atts['offset'] = !empty($queryPage) ? ($queryPage - 1) * $atts['limit'] : $atts['offset'];
$atts['offset'] = $atts['offset'] > 0 ? $atts['offset'] : NULL;
// Search terms
$searchTerms = cnQuery::getVar('cn-s');
if (!empty($searchTerms)) {
$atts['search_terms'] = $searchTerms;
}
// Geo-location
$queryCoord = cnQuery::getVar('cn-near-coord');
if (!empty($queryCoord)) {
$queryCoord = explode(',', $queryCoord);
$atts['latitude'] = $wpdb->prepare('%f', $queryCoord[0]);
$atts['longitude'] = $wpdb->prepare('%f', $queryCoord[1]);
// Get the radius, otherwise the default of 10.
if (cnQuery::getVar('cn-radius')) {
$atts['radius'] = $wpdb->prepare('%d', cnQuery::getVar('cn-radius'));
}
// Sanitize and set the unit.
$atts['unit'] = cnQuery::getVar('cn-unit') ? sanitize_key(cnQuery::getVar('cn-unit')) : sanitize_key($atts['unit']);
}
}
/*
* // END -- Process the query vars. \\
*/
/*
* // START -- Reset some of the $atts based if category_slug or entry slug
* is being used. This is done to prevent query conflicts. This should be safe because
* if a specific entry or category is being queried the other $atts can be discarded.
* This has to be done in order to reconcile values passed via the shortcode and the
* query string values.
*
* @TODO I know there are more scenarios to consider ... but this is all I can think of at the moment.
* Thought --- maybe resetting to the default $atts should be done --- something to consider.
*/
if (!empty($atts['slug']) || !empty($atts['category_slug'])) {
$atts['list_type'] = NULL;
$atts['category'] = NULL;
$atts['category_in'] = NULL;
$atts['category_exclude'] = NULL;
$atts['wp_current_category'] = NULL;
}
if (!empty($atts['slug'])) {
$atts['near_addr'] = NULL;
$atts['latitude'] = NULL;
$atts['longitude'] = NULL;
$atts['radius'] = 10;
$atts['unit'] = 'mi';
}
/*
* // END -- Reset.
*/
/*
* If in a post get the category names assigned to the post.
*/
if ($atts['wp_current_category'] && !is_page()) {
// Get the current post categories.
$wpCategories = get_the_category();
// Build an array of the post categories.
foreach ($wpCategories as $wpCategory) {
$categoryNames[] = $wpdb->prepare('%s', $wpCategory->cat_name);
}
}
/*
* Build and array of the supplied category names and their children.
*/
if (!empty($atts['category_name'])) {
cnFunction::parseStringList($atts['category_name'], ',');
foreach ($atts['category_name'] as $categoryName) {
// Add the parent category to the array and remove any whitespace from the beginning/end of the name just in case the user added it when using the shortcode.
$categoryNames[] = $wpdb->prepare('%s', htmlspecialchars($categoryName));
// Retrieve the children categories
$results = $this->categoryChildren('name', $categoryName);
foreach ((array) $results as $term) {
// Only add the name if it doesn't already exist. If it doesn't sanitize and add to the array.
if (!in_array($term->name, $categoryNames)) {
$categoryNames[] = $wpdb->prepare('%s', $term->name);
}
}
}
}
/*
* Build and array of the supplied category slugs and their children.
*/
if (!empty($atts['category_slug'])) {
$categorySlugs = array();
cnFunction::parseStringList($atts['category_slug'], ',');
foreach ($atts['category_slug'] as $categorySlug) {
// Add the parent category to the array and remove any whitespace from the beginning/end of the name in case the user added it when using the shortcode.
$categorySlugs[] = sanitize_title($categorySlug);
// Retrieve the children categories.
$results = $this->categoryChildren('slug', $categorySlug);
foreach ((array) $results as $term) {
// Only add the slug if it doesn't already exist. If it doesn't sanitize and add to the array.
if (!in_array($term->name, $categorySlugs)) {
$categorySlugs[] = sanitize_title($term->slug);
}
}
}
}
/*
* Build an array of all the categories and their children based on the supplied category IDs.
*/
if (!empty($atts['category'])) {
$categoryIDs = array();
$atts['category'] = wp_parse_id_list($atts['category']);
foreach ($atts['category'] as $categoryID) {
// Add the parent category ID to the array.
$categoryIDs[] = absint($categoryID);
// Retrieve the children categories
$termChildren = cnTerm::children($categoryID, 'category');
if (!is_a($termChildren, 'WP_Error') && !empty($termChildren)) {
$categoryIDs = array_merge($categoryIDs, $termChildren);
}
}
}
/*
* Exclude the specified categories by ID.
*/
if (!empty($atts['exclude_category'])) {
if (!isset($categoryIDs)) {
$categoryIDs = array();
}
$categoryExcludeIDs = array();
$atts['exclude_category'] = wp_parse_id_list($atts['exclude_category']);
$categoryIDs = array_diff($categoryIDs, $atts['exclude_category']);
foreach ($atts['exclude_category'] as $categoryID) {
// Add the parent category ID to the array.
$categoryExcludeIDs[] = absint($categoryID);
// Retrieve the children categories
$termChildren = cnTerm::children($categoryID, 'category');
if (!is_a($termChildren, 'WP_Error') && !empty($termChildren)) {
$categoryExcludeIDs = array_merge($categoryExcludeIDs, $termChildren);
}
}
$atts['exclude_category'] = array_unique($categoryExcludeIDs);
$sql = 'SELECT tr.entry_id FROM ' . CN_TERM_RELATIONSHIP_TABLE . ' AS tr
INNER JOIN ' . CN_TERM_TAXONOMY_TABLE . ' AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE 1=1 AND tt.term_id IN (' . implode(", ", $atts['exclude_category']) . ')';
// Store the entryIDs that are to be excluded.
$results = $wpdb->get_col($sql);
//print_r($results);
if (!empty($results)) {
$where[] = 'AND ' . CN_ENTRY_TABLE . '.id NOT IN (' . implode(", ", $results) . ')';
}
}
// Convert the supplied category IDs $atts['category_in'] to an array.
if (!empty($atts['category_in'])) {
$atts['category_in'] = wp_parse_id_list($atts['category_in']);
// Remove empty values from the array.
$atts['category_in'] = array_filter($atts['category_in']);
// Ensure there is something to query after filtering the array.
if (!empty($atts['category_in'])) {
// Exclude any category IDs that may have been set.
$atts['category_in'] = array_diff($atts['category_in'], (array) $atts['exclude_category']);
// Build the query to retrieve entry IDs that are assigned to all the supplied category IDs; operational AND.
$sql = 'SELECT DISTINCT tr.entry_id FROM ' . CN_TERM_RELATIONSHIP_TABLE . ' AS tr
INNER JOIN ' . CN_TERM_TAXONOMY_TABLE . ' AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE 1=1 AND tt.term_id IN (' . implode(", ", $atts['category_in']) . ') GROUP BY tr.entry_id HAVING COUNT(*) = ' . count($atts['category_in']) . ' ORDER BY tr.entry_id';
// Store the entryIDs that exist on all of the supplied category IDs
$results = $wpdb->get_col($sql);
//print_r($results);
if (!empty($results)) {
$where[] = 'AND ' . CN_ENTRY_TABLE . '.id IN (' . implode(", ", $results) . ')';
} else {
$where[] = 'AND 1=2';
}
}
/*
* This is the query to use to return entry IDs that are in the same categories. The COUNT value
* should equal the number of category IDs in the IN() statement.
SELECT DISTINCT tr.entry_id FROM `wp_connections_term_relationships` AS tr
INNER JOIN `wp_connections_term_taxonomy` AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE 1=1 AND tt.term_id IN ('51','73','76') GROUP BY tr.entry_id HAVING COUNT(*) = 3 ORDER BY tr.entry_id
*/
}
if (!empty($categoryIDs) || !empty($categoryExcludeIDs) || !empty($categoryNames) || !empty($categorySlugs)) {
// Set the query string to INNER JOIN the term relationship and taxonomy tables.
$join[] = 'INNER JOIN ' . CN_TERM_RELATIONSHIP_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_TERM_RELATIONSHIP_TABLE . '.entry_id )';
$join[] = 'INNER JOIN ' . CN_TERM_TAXONOMY_TABLE . ' ON ( ' . CN_TERM_RELATIONSHIP_TABLE . '.term_taxonomy_id = ' . CN_TERM_TAXONOMY_TABLE . '.term_taxonomy_id )';
$join[] = 'INNER JOIN ' . CN_TERMS_TABLE . ' ON ( ' . CN_TERMS_TABLE . '.term_id = ' . CN_TERM_TAXONOMY_TABLE . '.term_id )';
// Set the query string to return entries within the category taxonomy.
$where[] = 'AND ' . CN_TERM_TAXONOMY_TABLE . '.taxonomy = \'category\'';
if (!empty($categoryIDs)) {
$where[] = 'AND ' . CN_TERM_TAXONOMY_TABLE . '.term_id IN (' . implode(", ", $categoryIDs) . ')';
unset($categoryIDs);
}
if (!empty($categoryExcludeIDs)) {
$where[] = 'AND ' . CN_TERM_TAXONOMY_TABLE . '.term_id NOT IN (' . implode(", ", $categoryExcludeIDs) . ')';
unset($categoryExcludeIDs);
}
if (!empty($categoryNames)) {
$where[] = 'AND ' . CN_TERMS_TABLE . '.name IN (' . implode(", ", (array) $categoryNames) . ')';
unset($categoryNames);
}
if (!empty($categorySlugs)) {
$where[] = 'AND ' . CN_TERMS_TABLE . '.slug IN (\'' . implode("', '", (array) $categorySlugs) . '\')';
unset($categorySlugs);
}
}
/*
* // START --> Set up the query to only return the entries that match the supplied IDs.
* NOTE: This includes the entry IDs returned for category_in.
*/
if (!empty($atts['id'])) {
$atts['id'] = wp_parse_id_list($atts['id']);
// Set query string to return specific entries.
$where[] = 'AND ' . CN_ENTRY_TABLE . '.id IN (\'' . implode("', '", $atts['id']) . '\')';
}
/*
* // END --> Set up the query to only return the entries that match the supplied IDs.
*/
/*
* // START --> Set up the query to only return the entries that match the supplied search terms.
*/
if (!empty($atts['search_terms'])) {
$searchResults = $this->search(array('terms' => $atts['search_terms']));
//print_r($searchResults);
// If there were no results, add a WHERE clause that will not return results when performing the whole query.
if (empty($searchResults)) {
$where[] = 'AND 1=2';
} else {
// Set $atts['order_by'] to the order the entry IDs were returned.
// This is to support the relevancy order results being returned by self::search().
$atts['order_by'] = 'id|SPECIFIED';
$atts['id'] = $searchResults;
// Set the entry IDs to be the search results.
$where[] = 'AND ' . CN_ENTRY_TABLE . '.id IN (\'' . implode("', '", $searchResults) . '\')';
}
}
/*
* // END --> Set up the query to only return the entries that match the supplied search terms.
*/
/*
* // START --> Set up the query to only return the entry that matches the supplied slug.
*/
if (!empty($atts['slug'])) {
// Trim the white space from the ends.
$atts['slug'] = trim($atts['slug']);
$where[] = $wpdb->prepare('AND ' . CN_ENTRY_TABLE . '.slug = %s', $atts['slug']);
}
/*
* // END --> Set up the query to only return the entry that matches the supplied slug.
*/
/*
* // START --> Set up the query to only return the entries that match the supplied entry type.
*/
if (!empty($atts['list_type'])) {
cnFunction::parseStringList($atts['list_type'], ',');
$permittedEntryTypes = array('individual', 'organization', 'family', 'connection_group');
// Set query string for entry type.
if ((bool) array_intersect($atts['list_type'], $permittedEntryTypes)) {
// Compatibility code to make sure any occurrences of the deprecated entry type connections_group
// is changed to entry type family
$atts['list_type'] = str_replace('connection_group', 'family', $atts['list_type']);
$where[] = cnQuery::where(array('field' => 'entry_type', 'value' => $atts['list_type']));
}
}
/*
* // END --> Set up the query to only return the entries that match the supplied entry type.
*/
/*
* // START --> Set up the query to only return the entries that match the supplied filters.
*/
$where[] = cnQuery::where(array('field' => 'family_name', 'value' => $atts['family_name']));
$where[] = cnQuery::where(array('field' => 'last_name', 'value' => $atts['last_name']));
$where[] = cnQuery::where(array('field' => 'title', 'value' => $atts['title']));
$where[] = cnQuery::where(array('field' => 'organization', 'value' => $atts['organization']));
$where[] = cnQuery::where(array('field' => 'department', 'value' => $atts['department']));
if (!empty($atts['district']) || !empty($atts['county']) || !empty($atts['city']) || !empty($atts['state']) || !empty($atts['zip_code']) || !empty($atts['country'])) {
if (!isset($join['address'])) {
$join['address'] = 'INNER JOIN ' . CN_ENTRY_ADDRESS_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_ENTRY_ADDRESS_TABLE . '.entry_id )';
}
$where[] = cnQuery::where(array('table' => CN_ENTRY_ADDRESS_TABLE, 'field' => 'district', 'value' => $atts['district']));
$where[] = cnQuery::where(array('table' => CN_ENTRY_ADDRESS_TABLE, 'field' => 'county', 'value' => $atts['county']));
$where[] = cnQuery::where(array('table' => CN_ENTRY_ADDRESS_TABLE, 'field' => 'city', 'value' => $atts['city']));
$where[] = cnQuery::where(array('table' => CN_ENTRY_ADDRESS_TABLE, 'field' => 'state', 'value' => $atts['state']));
$where[] = cnQuery::where(array('table' => CN_ENTRY_ADDRESS_TABLE, 'field' => 'zipcode', 'value' => $atts['zip_code']));
$where[] = cnQuery::where(array('table' => CN_ENTRY_ADDRESS_TABLE, 'field' => 'country', 'value' => $atts['country']));
}
if (0 < strlen($atts['char'])) {
$having[] = $wpdb->prepare('HAVING sort_column LIKE %s', $wpdb->esc_like($atts['char']) . '%');
}
/*
* // END --> Set up the query to only return the entries that match the supplied filters.
*/
/*
* // START --> Set up the query to only return the entries based on user permissions.
*/
$where = self::setQueryVisibility($where, $atts);
/*
* // END --> Set up the query to only return the entries based on user permissions.
*/
/*
* // START --> Set up the query to only return the entries based on status.
*/
$where = self::setQueryStatus($where, $atts);
/*
* // END --> Set up the query to only return the entries based on status.
*/
/*
* // START --> Geo-limit the query.
*/
//$atts['latitude'] = 40.3663671;
//$atts['longitude'] = -75.8876941;
if (!empty($atts['latitude']) && !empty($atts['longitude'])) {
$earthRadius = 6371;
// Earth's radius in (SI) km.
// Convert the supplied radius from the supplied unit to (SI) km.
$atts['radius'] = cnGeo::convert(array('value' => $atts['radius'], 'from' => $atts['unit'], 'to' => 'km', 'format' => FALSE, 'return' => TRUE));
// Limiting bounding box (in degrees).
$minLat = $atts['latitude'] - rad2deg($atts['radius'] / $earthRadius);
$maxLat = $atts['latitude'] + rad2deg($atts['radius'] / $earthRadius);
$minLng = $atts['longitude'] - rad2deg($atts['radius'] / $earthRadius / cos(deg2rad($atts['latitude'])));
$maxLng = $atts['longitude'] + rad2deg($atts['radius'] / $earthRadius / cos(deg2rad($atts['latitude'])));
// Convert origin of geographic circle to radians.
$atts['latitude'] = deg2rad($atts['latitude']);
$atts['longitude'] = deg2rad($atts['longitude']);
// Add the SELECT statement that adds the `radius` column.
$select[] = $wpdb->prepare('acos(sin(%f)*sin(radians(latitude)) + cos(%f)*cos(radians(latitude))*cos(radians(longitude)-%f))*6371 AS distance', $atts['latitude'], $atts['latitude'], $atts['longitude']);
// Create a subquery that will limit the rows that have the cosine law applied to within the bounding box.
$geoSubselect = $wpdb->prepare('(SELECT entry_id FROM ' . CN_ENTRY_ADDRESS_TABLE . ' WHERE latitude>%f AND latitude<%f AND longitude>%f AND longitude<%f) AS geo_bound', $minLat, $maxLat, $minLng, $maxLng);
// The subquery needs to be added to the beginning of the array so the inner joins on the other tables are joined to the CN_ENTRY_TABLE
array_unshift($from, $geoSubselect);
// Add the JOIN for the address table. NOTE: This JOIN is also set in the ORDER BY section. The 'address' index is to make sure it doea not get added to the query twice.
if (!isset($join['address'])) {
$join['address'] = 'INNER JOIN ' . CN_ENTRY_ADDRESS_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_ENTRY_ADDRESS_TABLE . '.entry_id )';
}
// Add the WHERE statement to limit the query to a geographic circle per the defined radius.
$where[] = $wpdb->prepare('AND acos(sin(%f)*sin(radians(latitude)) + cos(%f)*cos(radians(latitude))*cos(radians(longitude)-%f))*6371 < %f', $atts['latitude'], $atts['latitude'], $atts['longitude'], $atts['radius']);
// This is required otherwise addresses the user may not have permissions to view will be included in the query
// which could be confusing since entries could appear to be outside of the search radius when in fact the entry
// is within the search radius, it is just the address used to determine that is not viewable to the user.
//$where[] = 'AND ' . CN_ENTRY_ADDRESS_TABLE . '.visibility IN (\'' . implode( "', '", (array) $visibility ) . '\')';
$where = self::setQueryVisibility($where, array('table' => CN_ENTRY_ADDRESS_TABLE));
// Temporarily set the sort order to 'radius' for testing.
//$atts['order_by'] = array('radius');
}
/*
* // END --> Geo-limit the query.
*/
/*
* // START --> Build the ORDER BY query segment.
*/
//if ( empty($atts['order_by']) )
//{
// Setup the default sort order if none were supplied.
//$orderBy = array('sort_column', 'last_name', 'first_name');
//}
//else
//{
$orderFields = array('id', 'date_added', 'date_modified', 'first_name', 'last_name', 'title', 'organization', 'department', 'city', 'state', 'zipcode', 'country', 'birthday', 'anniversary', 'sort_column');
$orderFlags = array('SPECIFIED' => 'SPECIFIED', 'RANDOM' => 'RANDOM', 'ASC' => 'ASC', 'SORT_ASC' => 'ASC', 'DESC' => 'DESC', 'SORT_DESC' => 'DESC', 'NUMERIC' => '+0', 'SORT_NUMERIC' => '+0', 'SORT_NUMERIC_ASC' => '+0', 'NUMERIC_DESC' => '+0 DESC', 'SORT_NUMERIC_DESC' => '+0 DESC');
// If a geo-bound query is being performed the `radius` order field can be used.
if (!empty($atts['latitude']) && !empty($atts['longitude'])) {
array_push($orderFields, 'distance');
}
// Get registered date types.
$dateTypes = array_keys($instance->options->getDateOptions());
// Convert to an array
if (!is_array($atts['order_by'])) {
// Trim the space characters if present.
$atts['order_by'] = str_replace(' ', '', $atts['order_by']);
// Build an array of each field to order by and its sort order.
$atts['order_by'] = explode(',', $atts['order_by']);
}
// For each field the sort order can be defined.
foreach ($atts['order_by'] as $orderByField) {
$orderByAtts[] = explode('|', $orderByField);
}
// Build the ORDER BY query segment
foreach ($orderByAtts as $field) {
// Trim any spaces the user may have supplied and set it to be lowercase.
$field[0] = strtolower(trim($field[0]));
// Check to make sure the supplied field is one of the valid fields to order by.
if (in_array($field[0], $orderFields) || cnString::startsWith('meta_key:', $field[0])) {
// The date_modified actually maps to the `ts` column in the db.
if ($field[0] == 'date_modified') {
$field[0] = 'ts';
}
// If one of the order fields is an address region add the INNER JOIN to the CN_ENTRY_ADDRESS_TABLE
if ($field[0] == 'city' || $field[0] == 'state' || $field[0] == 'zipcode' || $field[0] == 'country') {
if (!isset($join['address'])) {
$join['address'] = 'INNER JOIN ' . CN_ENTRY_ADDRESS_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_ENTRY_ADDRESS_TABLE . '.entry_id )';
}
}
if (cnString::startsWith('meta_key:', $field[0])) {
// Extract the meta key name from $field[0].
$meta = explode(':', $field[0]);
// Ensure the meta key does exist and is not empty before altering the query.
if (isset($meta[1]) && !empty($meta[1])) {
$atts['meta_query']['meta_query'][] = array('key' => $meta[1]);
if (1 < count($atts['meta_query']['meta_query'])) {
$field[0] = 'mt' . (count($atts['meta_query']['meta_query']) - 1) . '.meta_value';
} else {
$field[0] = CN_ENTRY_TABLE_META . '.meta_value';
}
}
}
// If we're ordering by anniversary or birthday, we need to convert the string to a UNIX timestamp so it is properly ordered.
// Otherwise, it is sorted as a string which can give some very odd results compared to what is expected.
//if ( $field[0] == 'anniversary' || $field[0] == 'birthday' ) {
//
// $field[0] = 'FROM_UNIXTIME( ' . $field[0] . ' )';
//}
if (in_array($field[0], $dateTypes)) {
if (!isset($join['date'])) {
$join['date'] = 'INNER JOIN ' . CN_ENTRY_DATE_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_ENTRY_DATE_TABLE . '.entry_id )';
}
$where[] = $wpdb->prepare('AND ' . CN_ENTRY_DATE_TABLE . '.type = %s', $field[0]);
$field[0] = 'date';
}
// Check to see if an order flag was set and is a valid order flag.
if (isset($field[1])) {
// Trim any spaces the user might have added and change the string to uppercase..
$field[1] = strtoupper(trim($field[1]));
// If a user included a sort flag that is invalid/mis-spelled it is skipped since it can not be used.
if (array_key_exists($field[1], $orderFlags)) {
/*
* The SPECIFIED and RANDOM order flags are special use and should only be used with the id sort field.
* Set the default sort flag if it was use on any other sort field than id.
*/
if (($orderFlags[$field[1]] == 'SPECIFIED' || $orderFlags[$field[1]] == 'RANDOM') && $field[0] != 'id') {
$field[1] = 'SORT_ASC';
}
switch ($orderFlags[$field[1]]) {
/*
* Order the results based on the order of the supplied entry IDs
*/
case 'SPECIFIED':
if (!empty($atts['id'])) {
$orderBy = array('FIELD( ' . CN_ENTRY_TABLE . '.id, ' . implode(', ', (array) $atts['id']) . ' )');
}
break;
/*
* Randomize the order of the results.
*/
/*
* Randomize the order of the results.
*/
case 'RANDOM':
$random = TRUE;
break;
/*
* Return the results in ASC or DESC order.
*/
/*
* Return the results in ASC or DESC order.
*/
default:
$orderBy[] = $field[0] . ' ' . $orderFlags[$field[1]];
break;
}
} else {
$orderBy[] = $field[0];
}
} else {
$orderBy[] = $field[0];
}
}
}
//}
if (!empty($atts['meta_query'])) {
$metaQuery = new cnMeta_Query();
$metaQuery->parse_query_vars($atts['meta_query']);
$metaClause = $metaQuery->get_sql('entry', CN_ENTRY_TABLE, 'id');
$join['meta'] = $metaClause['join'];
$where['meta'] = $metaClause['where'];
}
$orderBy = empty($orderBy) ? 'ORDER BY sort_column, last_name, first_name' : 'ORDER BY ' . implode(', ', $orderBy);
/*
* // END --> Build the ORDER BY query segment.
*/
/*
* // START --> Set up the query LIMIT and OFFSET.
*/
$limit = empty($atts['limit']) ? '' : $wpdb->prepare(' LIMIT %d ', $atts['limit']);
$offset = empty($atts['offset']) ? '' : $wpdb->prepare(' OFFSET %d ', $atts['offset']);
/*
* // END --> Set up the query LIMIT and OFFSET.
*/
/*
* // START --> Build the SELECT query segment.
*/
$select[] = 'CASE `entry_type`
WHEN \'individual\' THEN `last_name`
WHEN \'organization\' THEN `organization`
WHEN \'connection_group\' THEN `family_name`
WHEN \'family\' THEN `family_name`
END AS `sort_column`';
/*
* // END --> Build the SELECT query segment.
*/
$pieces = array('select', 'from', 'join', 'where', 'having', 'orderBy', 'limit', 'offset');
/**
* Filter the query SQL clauses.
*
* @since 8.5.14
*
* @param array $pieces Terms query SQL clauses.
*/
$clauses = apply_filters('cn_entry_query_clauses', compact($pieces));
foreach ($pieces as $piece) {
${$piece} = isset($clauses[$piece]) ? $clauses[$piece] : '';
}
/**
* NOTES:
*
* Many queries can produce multiple results per entry ID when we really only want it once.
* For example an entry maybe return once for each category it is assigned or once for each
* address an entry has that is within the search radius.
*
* Simply adding `GROUP BY CN_ENTRY_TABLE.id seems to fix this, but may be incorrect and might fail
* on db/s other than MySQL such as Oracle.
*
* Very useful links that provide more details that require further study:
*
* @link http://www.psce.com/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/
* @link http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html
*/
if ($random) {
$seed = cnFormatting::stripNonNumeric(cnUtility::getIP()) . date('Hdm', current_time('timestamp', 1));
$sql = 'SELECT SQL_CALC_FOUND_ROWS *, RAND(' . $seed . ') AS random FROM ( SELECT DISTINCT ' . implode(', ', $select) . ' FROM ' . implode(', ', $from) . ' ' . implode(' ', $join) . ' ' . implode(' ', $where) . ' GROUP BY ' . CN_ENTRY_TABLE . '.id ' . implode(' ', $having) . ') AS T ORDER BY random' . $limit . $offset;
// print_r($sql);
} else {
$sql = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT ' . implode(', ', $select) . ' FROM ' . implode(', ', $from) . ' ' . implode(' ', $join) . ' ' . implode(' ', $where) . ' GROUP BY ' . CN_ENTRY_TABLE . '.id ' . implode(' ', $having) . ' ' . $orderBy . ' ' . $limit . $offset;
// print_r($sql);
}
if (!($results = $this->results($sql))) {
$results = $wpdb->get_results($sql);
$this->cache($sql, $results);
// The most recent query to have been executed by cnRetrieve::entries
$instance->lastQuery = $wpdb->last_query;
// The most recent query error to have been generated by cnRetrieve::entries
$instance->lastQueryError = $wpdb->last_error;
// ID generated for an AUTO_INCREMENT column by the most recent INSERT query.
$instance->lastInsertID = $wpdb->insert_id;
// The number of rows returned by the last query.
$instance->resultCount = $wpdb->num_rows;
// The number of rows returned by the last query without the limit clause set
$foundRows = $wpdb->get_results('SELECT FOUND_ROWS()');
$instance->resultCountNoLimit = $foundRows[0]->{'FOUND_ROWS()'};
$this->resultCountNoLimit = $foundRows[0]->{'FOUND_ROWS()'};
}
// The total number of entries based on user permissions.
// $instance->recordCount = self::recordCount( array( 'public_override' => $atts['allow_public_override'], 'private_override' => $atts['private_override'] ) );
// The total number of entries based on user permissions with the status set to 'pending'
// $instance->recordCountPending = self::recordCount( array( 'public_override' => $atts['allow_public_override'], 'private_override' => $atts['private_override'], 'status' => array( 'pending' ) ) );
// The total number of entries based on user permissions with the status set to 'approved'
// $instance->recordCountApproved = self::recordCount( array( 'public_override' => $atts['allow_public_override'], 'private_override' => $atts['private_override'], 'status' => array( 'approved' ) ) );
/*
* ONLY in the admin.
*
* Reset the pagination filter for the current user, remove the offset from the query and re-run the
* query if the offset for the query is greater than the record count with no limit set in the query.
*
*/
if (is_admin() && $atts['offset'] > $instance->resultCountNoLimit) {
$instance->currentUser->resetFilterPage('manage');
unset($atts['offset']);
$results = $this->entries($atts);
} elseif ($atts['offset'] > $instance->resultCountNoLimit) {
/*
* This is for the front end, reset the page and offset and re-run the query if the offset
* is greater than the record count with no limit.
*
* @TODO this should somehow be precessed in the parse_request action hook so the URL
* permalink and query vars can be properly updated.
*/
set_query_var('cn-pg', 0);
$atts['offset'] = 0;
$results = $this->entries($atts);
}
return $results;
}
