/** * Perform a query on db depending on args * It's responsible for argument validations * @uses wl_core_sql_query_builder() to compose the sql statement * @uses wpdb() instance to perform the query * * @param array args Arguments to be used in the query builder. * * @return (array) List of WP_Post objects or list of WP_Post ids. False in case of error or invalid params */ function wl_core_get_posts($args, $returned_type = OBJECT) { // Merge given args with defaults args value $args = array_merge(array('with_predicate' => null, 'as' => 'subject', 'post_type' => 'post', 'get' => 'posts', 'post_status' => null), $args); // Arguments validation rules // At least one between related_to and related_to__in has to be set if (!isset($args['related_to']) && !isset($args['related_to__in'])) { return false; } if (isset($args['related_to']) && !is_numeric($args['related_to'])) { return false; } // The same check is applied to post_in, post__not_in and related_to__in options // Only arrays with at least one numeric value are considerad valid // The argument value is further sanitized in order to clean up not numeric values foreach (array('post__in', 'post__not_in', 'related_to__in') as $option_name) { if (isset($args[$option_name])) { if (!is_array($args[$option_name]) || 0 == count(array_filter($args[$option_name], "is_numeric"))) { return false; } // Sanitize value removing non numeric values from the array $args[$option_name] = array_filter($args[$option_name], "is_numeric"); } } // Performing validation rules foreach (wl_core_get_validation_rules() as $option_name => $accepeted_values) { if (isset($args[$option_name]) && !is_null($args[$option_name])) { if (!in_array($args[$option_name], $accepeted_values)) { return false; } } } // Prepare interaction with db global $wpdb; // Build sql statement with given arguments $sql_statement = wl_core_sql_query_builder($args); // wl_write_log( "Going to execute sql statement: $sql_statement " ); // $results = array(); // If ids are required, returns a one-dimensional array containing ids. // Otherwise an array of associative arrays representing the post | relation object if ('post_ids' == $args['get']) { # See https://codex.wordpress.org/Class_Reference/wpdb#SELECT_a_Column $results = $wpdb->get_col($sql_statement); } else { $results = $wpdb->get_results($sql_statement, $returned_type); } // If there were an error performing the query then false is returned if (!empty($wpdb->last_error)) { return false; } // Finally return $results; }
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); }