/** * Get relations for a given $subject_id as an associative array. * * @global type $wpdb * * @param type $post_id * @param type $predicate * * @return array in the following format: * Array ( * [0] => stdClass Object ( [id] => 140 [subject_id] => 17 [predicate] => what [object_id] => 47 ), * [1] => stdClass Object ( [id] => 141 [subject_id] => 17 [predicate] => what [object_id] => 14 ), * [2] => stdClass Object ( [id] => 142 [subject_id] => 17 [predicate] => where [object_id] => 16 ), * ... */ function wl_tests_get_relation_instances_for($post_id, $predicate = null) { // Prepare interaction with db global $wpdb; // Retrieve Wordlift relation instances table name $table_name = wl_core_get_relation_instances_table_name(); // Sql Action $sql_statement = $wpdb->prepare("SELECT * FROM {$table_name} WHERE subject_id = %d", $post_id); if (null != $predicate) { $sql_statement .= $wpdb->prepare(" AND predicate = %s", $predicate); } $results = $wpdb->get_results($sql_statement); return $results; }
/** * Define a sql statement between wp_posts and wp_wl_relation_instances tables * It's used by wl_core_get_posts. Implements a subset of WpQuery object * @see https://codex.wordpress.org/Class_Reference/WP_Query * Arguments validation is delegated to wl_core_get_posts method. * Form the array like this: * <code> * $args = array( * 'get' => 'posts', // posts, post_ids, relations, relation_ids * 'first' => n, * 'related_to' => 10, // the post/s / entity/ies id / ids * 'related_to__in' => array(10,20,30) * 'post__in' => array(10,20,30), // the post/s / entity/ies id / ids * 'post__not_in' => array(10,20,30), // the post/s / entity/ies id / ids * 'as' => [ subject | object ], * 'with_predicate' => [ what | where | when | who ], // null as default value * 'post_type' => [ post | entity ], * 'post_status' => [ publish | draft | pending | trash ], default is null (meaning *any* post status) * ); * </code> * * @param array args Arguments to be used in the query builder. * * @return string | false String representing a sql statement, or false in case of error */ function wl_core_sql_query_builder($args) { // Prepare interaction with db global $wpdb; // Retrieve Wordlift relation instances table name $table_name = wl_core_get_relation_instances_table_name(); // Sql Action $sql = "SELECT "; // Determine what has to be returned depending on 'get' argument value switch ($args['get']) { case 'posts': $sql .= "p.*"; break; case 'post_ids': $sql .= "p.id"; break; } // If we look for posts related as objects the JOIN has to be done with the object_id column and viceversa $join_column = $args['as'] . "_id"; $sql .= " FROM {$wpdb->posts} as p JOIN {$table_name} as r ON p.id = r.{$join_column}"; // Sql add post type filter $sql .= $wpdb->prepare(" AND p.post_type = %s AND", $args['post_type']); // Sql add post status filter if (isset($args['post_status']) && !is_null($args['post_status'])) { $sql .= $wpdb->prepare(" p.post_status = %s AND", $args['post_status']); } // Add filtering conditions // If we look for posts related as objects this means that // related_to is a reference for a subject: subject_id is the filtering column // If we look for posts related as subject this means that // related_to is reference for an object: object_id is the filtering column $filtering_column = 'object' == $args['as'] ? "subject_id" : "object_id"; if (isset($args['related_to'])) { $sql .= $wpdb->prepare(" r.{$filtering_column} = %d", $args['related_to']); } if (isset($args['related_to']) && isset($args['related_to__in'])) { $sql .= " AND"; } if (isset($args['related_to__in'])) { $sql .= " r.{$filtering_column} IN (" . implode(",", $args['related_to__in']) . ")"; } if (isset($args['post__not_in'])) { $sql .= " AND r." . $args['as'] . "_id NOT IN (" . implode(",", $args['post__not_in']) . ")"; } if (isset($args['post__in'])) { $sql .= " AND r." . $args['as'] . "_id IN (" . implode(",", $args['post__in']) . ")"; } // Add predicate filter if required if (isset($args['with_predicate'])) { // Sql Inner Join clausole $sql .= $wpdb->prepare(" AND r.predicate = %s", $args['with_predicate']); } // Add a group by clousole to avoid duplicated rows $sql .= " GROUP BY p.id"; if (isset($args['first']) && is_numeric($args['first'])) { // Sql Inner Join clausole $sql .= $wpdb->prepare(" LIMIT %d", $args['first']); } // Close sql statement $sql .= ";"; return $sql; }
function wl_shortcode_faceted_search_ajax($http_raw_data = null) { // Entity ID must be defined if (!isset($_GET['entity_id'])) { wp_die('No entity_id given'); return; } $entity_id = $_GET['entity_id']; // If the current post is not an entity post an exception needs to be raised $entity = get_post($entity_id); if (Wordlift_Entity_Service::TYPE_NAME !== $entity->post_type) { wp_die('Faceted search supports only entity posts'); return; } // Which type was requested? if (isset($_GET['type'])) { $required_type = $_GET['type']; } else { $required_type = null; } // Extract filtering conditions $filtering_entity_uris = null == $http_raw_data ? file_get_contents("php://input") : $http_raw_data; $filtering_entity_uris = json_decode($filtering_entity_uris); // Set up data structures $referencing_post_ids = wl_core_get_related_post_ids($entity_id, array('status' => 'publish')); $results = array(); if ('posts' == $required_type) { // Required filtered posts. wl_write_log("Going to find related posts for the current entity [ entity ID :: {$entity_id} ]"); if (empty($filtering_entity_uris)) { // No filter, just get referencing posts foreach ($referencing_post_ids as $post_obj_id) { $post_obj = get_post($post_obj_id); $thumbnail = wp_get_attachment_url(get_post_thumbnail_id($post_obj->ID, 'thumbnail')); $post_obj->thumbnail = $thumbnail ? $thumbnail : WL_DEFAULT_THUMBNAIL_PATH; $post_obj->permalink = get_post_permalink($post_obj->ID); $results[] = $post_obj; } } else { $filtering_entity_ids = wl_get_entity_post_ids_by_uris($filtering_entity_uris); // Search posts that reference all the filtering entities. $filtered_posts = wl_core_get_posts(array('get' => 'posts', 'post__in' => $referencing_post_ids, 'related_to__in' => $filtering_entity_ids, 'post_type' => 'post', 'as' => 'subject')); foreach ($filtered_posts as $post_obj) { $thumbnail = wp_get_attachment_url(get_post_thumbnail_id($post_obj->ID, 'thumbnail')); $post_obj->thumbnail = $thumbnail ? $thumbnail : WL_DEFAULT_THUMBNAIL_PATH; $post_obj->permalink = get_post_permalink($post_obj->ID); $results[] = $post_obj; } $results = $filtered_posts; } } else { global $wpdb; wl_write_log("Going to find related entities for the current entity [ entity ID :: {$entity_id} ]"); // Retrieve Wordlift relation instances table name $table_name = wl_core_get_relation_instances_table_name(); $ids = implode(',', $referencing_post_ids); // TODO - if an entity is related with different predicates each predicate impacts on counter $query = <<<EOF SELECT object_id as ID, count( object_id ) as counter FROM {$table_name} WHERE subject_id IN ({$ids}) and object_id != {$entity_id} GROUP BY object_id; EOF; wl_write_log("Going to find related entities for the current entity [ entity ID :: {$entity_id} ] [ query :: {$query} ]"); $entities = $wpdb->get_results($query, OBJECT); wl_write_log("Entities found " . count($entities)); foreach ($entities as $obj) { $entity = get_post($obj->ID); $entity = wl_serialize_entity($entity); $entity['counter'] = $obj->counter; $results[] = $entity; } } wl_core_send_json($results); }
$args = array('posts_per_page' => -1, 'post_type' => WL_ENTITY_TYPE_NAME, 'post_status' => array('publish', 'pending', 'draft', 'auto-draft', 'future', 'private', 'inherit', 'trash'), 'fields' => 'ids'); $entities_array = get_posts($args); // Loop over entities and delete them. // TODO: thumbnails? wl_write_log('Deleting entities and their meta... '); wl_write_log($entities_array); foreach ($entities_array as $entity_id) { // Delete the whole entity and its metas. wp_delete_post($entity_id, true); } wl_write_log('Done.'); /* * Delete post-entity relationships */ wl_write_log('Deleting post-entity relationships... '); $sql = 'DROP TABLE IF EXISTS ' . wl_core_get_relation_instances_table_name() . ';'; $wpdb->query($sql); delete_option('wl_db_version'); wl_write_log('Done.'); /* * Delete taxonomy */ wl_write_log('Cleaning entities taxonomy... '); // Delte custom taxonomy terms. // We loop over terms in this rude way because in the uninstall script // is not possible to call WP custom taxonomy functions. foreach (range(0, 100) as $index) { delete_option(WL_ENTITY_TYPE_TAXONOMY_NAME . '_' . $index); wp_delete_term($index, WL_ENTITY_TYPE_TAXONOMY_NAME); } delete_option(WL_ENTITY_TYPE_TAXONOMY_NAME . '_children');
function testWlCoreSqlQueryBuilder() { // Prepare interaction with db global $wpdb; $wl_table_name = wl_core_get_relation_instances_table_name(); // Case 6 - Find all posts of type 'post' related to post / entity with ID 3 as subject $args = array('get' => 'posts', 'related_to' => 3, 'as' => 'subject', 'post_type' => 'post'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.subject_id AND p.post_type = 'post' AND r.object_id = 3 GROUP BY p.id; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 7 - Find all post ids of type 'post' related to post / entity with ID 3 as subject $args = array('get' => 'post_ids', 'related_to' => 3, 'as' => 'subject', 'post_type' => 'post'); $expected_sql = <<<EOF SELECT p.id FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.subject_id AND p.post_type = 'post' AND r.object_id = 3 GROUP BY p.id; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 8 - Find first ten post ids of type 'post' related to post / entity with ID 3 as subject $args = array('first' => 10, 'get' => 'posts', 'related_to' => 3, 'as' => 'subject', 'post_type' => 'post'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.subject_id AND p.post_type = 'post' AND r.object_id = 3 GROUP BY p.id LIMIT 10; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 9 - Find first ten post ids of type 'post' related to post / entity with ID 3 as object $args = array('first' => 10, 'get' => 'posts', 'related_to' => 3, 'as' => 'object', 'post_type' => 'post'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.object_id AND p.post_type = 'post' AND r.subject_id = 3 GROUP BY p.id LIMIT 10; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 10 - Find first ten post ids of type 'post' related to post / entity with ID 3 as object with predicate what $args = array('first' => 10, 'get' => 'posts', 'related_to' => 3, 'as' => 'object', 'post_type' => 'post', 'with_predicate' => 'what'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.object_id AND p.post_type = 'post' AND r.subject_id = 3 AND r.predicate = 'what' GROUP BY p.id LIMIT 10; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 11 - Find first ten post ids of type 'post' related to post / entity with ID 3 and IN (4,5) as object with predicate what $args = array('first' => 10, 'get' => 'posts', 'related_to' => 3, 'related_to__in' => array('4', '5'), 'post_type' => 'post', 'as' => 'object'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.object_id AND p.post_type = 'post' AND r.subject_id = 3 AND r.subject_id IN (4,5) GROUP BY p.id LIMIT 10; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 12 - Find first ten post ids of type 'post' related to post / entity id IN (3, 4) as object with predicate what $args = array('first' => 10, 'get' => 'posts', 'related_to__in' => array('4', '5'), 'post_type' => 'post', 'as' => 'object'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.object_id AND p.post_type = 'post' AND r.subject_id IN (4,5) GROUP BY p.id LIMIT 10; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 13 - Find post ids of type 'post' not included IN (6) related to post / entity id IN (3, 4) as object $args = array('get' => 'posts', 'related_to__in' => array('4', '5'), 'post__not_in' => array('6'), 'post_type' => 'post', 'as' => 'object'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.object_id AND p.post_type = 'post' AND r.subject_id IN (4,5) AND r.object_id NOT IN (6) GROUP BY p.id; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 14 - Require a specific post status $args = array('get' => 'posts', 'related_to' => 4, 'post_type' => 'post', 'post_status' => 'draft', 'as' => 'object'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.object_id AND p.post_type = 'post' AND p.post_status = 'draft' AND r.subject_id = 4 GROUP BY p.id; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 15 - Do not require an post status $args = array('get' => 'posts', 'related_to' => 4, 'post_type' => 'post', 'post_status' => null, 'as' => 'object'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.object_id AND p.post_type = 'post' AND r.subject_id = 4 GROUP BY p.id; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); // Case 16 - Find post ids of type 'post' only if included IN (6) and related to post / entity id IN (3, 4) as object $args = array('get' => 'posts', 'related_to__in' => array('4', '5'), 'post__in' => array('6'), 'post_type' => 'post', 'as' => 'object'); $expected_sql = <<<EOF SELECT p.* FROM {$wpdb->posts} as p JOIN {$wl_table_name} as r ON p.id = r.object_id AND p.post_type = 'post' AND r.subject_id IN (4,5) AND r.object_id IN (6) GROUP BY p.id; EOF; $actual_sql = wl_core_sql_query_builder($args); $this->assertEquals($expected_sql, $actual_sql); // Try to perform query in order to see if there are errors on db side $wpdb->get_results($actual_sql); $this->assertEmpty($wpdb->last_error); }
/** * Server side response operations for the classification filter set in *restrict_manage_posts_classification_scope_filter* * * @since 3.3.0 * * @param array $clauses WP main query clauses. * * @return array Modified clauses. */ public function posts_clauses_classification_scope($clauses) { // Run only on admin page if (!is_admin()) { return $clauses; } // Return safely if get_current_screen() is not defined (yet) if (FALSE === function_exists('get_current_screen')) { return; } // Only apply on entity list page, only if this is the main query and if the wl-classification-scope query param is set $screen = get_current_screen(); if (!($screen->post_type === Wordlift_Entity_Service::TYPE_NAME && is_main_query() && isset($_GET['wl-classification-scope']))) { return $clauses; } // Check a valid W was requested $requested_w = $_GET['wl-classification-scope']; $all_w = array(WL_WHAT_RELATION, WL_WHO_RELATION, WL_WHERE_RELATION, WL_WHEN_RELATION); if (!in_array($requested_w, $all_w)) { return $clauses; } global $wpdb; $wl_relation_table = wl_core_get_relation_instances_table_name(); // Change WP main query clauses $clauses['join'] .= "INNER JOIN {$wl_relation_table} ON {$wpdb->posts}.ID = {$wl_relation_table}.object_id"; $clauses['where'] .= $wpdb->prepare("AND {$wl_relation_table}.predicate = %s", $requested_w); $clauses['distinct'] .= "DISTINCT"; return $clauses; }