/** * 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; }