示例#1
0
/**
 * 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);
}
示例#4
0
$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;
 }