SqlQueries::getReviewIdsByType() - Code Metrics - Inspection of "backup" - pryley/site-reviews - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Push — tmp ( 15f615...89cc97 )

by Paul

created 2020-06-28 05:34 UTC

SqlQueries::getReviewIdsByType() A

↳ Parent: SqlQueries

Complexity

Conditions	1
Paths	1

Size

Total Lines	13
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes

Metric	Value
cc	1
eloc	7
c	0
b	0
f	0
nc	1
nop	1
dl	0
loc	13
ccs	0
cts	13
cp	0
crap	2
rs	10

<?php

namespace GeminiLabs\SiteReviews\Database;

use GeminiLabs\SiteReviews\Application;
use GeminiLabs\SiteReviews\Database\SqlSchema;
use GeminiLabs\SiteReviews\Helper;
use GeminiLabs\SiteReviews\Helpers\Arr;
use GeminiLabs\SiteReviews\Helpers\Str;

class SqlQueries
{
    protected $db;
    protected $postType;

    public function __construct()
    {
        global $wpdb;
        $this->db = $wpdb;
        $this->postType = Application::POST_TYPE;
    }

    /**
     * @return bool
     */
    public function deletePostCountMetaKeys()
    {
        return $this->db->query("
            DELETE
            FROM {$this->db->postmeta}
            WHERE meta_key LIKE '_glsr_%'
        ");
    }

    /**
     * @return bool
     */
    public function deleteTermCountMetaKeys()
    {
        return $this->db->query("
            DELETE
            FROM {$this->db->termmeta}
            WHERE meta_key LIKE '_glsr_%'
        ");
    }

    /**
     * @param string $metaReviewId
     * @return int
     */
    public function getPostIdFromReviewId($metaReviewId)
    {
        $postId = $this->db->get_var("
            SELECT p.ID
            FROM {$this->db->posts} AS p
            INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
            WHERE p.post_type = '{$this->postType}'
            AND m.meta_key = '_review_id'
            AND m.meta_value = '{$metaReviewId}'
        ");
        return intval($postId);
    }

    /**
     * @return array
     */
    public function getRatings(array $args)
    {
        // get types
        // get for each type
        $table = glsr(SqlSchema::class)->table('ratings');
        return (array) $this->db->get_results("
            SELECT r.rating AS rating, COUNT(r.rating) AS count
            FROM {$table} AS r
            {$this->getInnerJoinForRatings($args)}
            WHERE r.is_approved = 1
            {$this->getAndForRatings($args)}
            GROUP BY rating
        ");
    }

    /**
     * @param int $lastPostId
     * @param int $limit
     * @return array
     */
    public function getReviewCounts(array $args, $lastPostId = 0, $limit = 500)
    {
        return (array) $this->db->get_results("
            SELECT DISTINCT p.ID, m1.meta_value AS rating, m2.meta_value AS type
            FROM {$this->db->posts} AS p
            INNER JOIN {$this->db->postmeta} AS m1 ON p.ID = m1.post_id
            INNER JOIN {$this->db->postmeta} AS m2 ON p.ID = m2.post_id
            {$this->getInnerJoinForCounts($args)}
            WHERE p.ID > {$lastPostId}
            AND p.post_status = 'publish'
            AND p.post_type = '{$this->postType}'
            AND m1.meta_key = '_rating'
            AND m2.meta_key = '_review_type'
            {$this->getAndForCounts($args)}
            ORDER By p.ID ASC
            LIMIT {$limit}
        ");
    }

    /**
     * @todo remove this?
     * @param string $metaKey
     * @return array
     */
    public function getReviewCountsFor($metaKey)
    {
        $metaKey = Str::prefix('_', $metaKey);
        return (array) $this->db->get_results("
            SELECT DISTINCT m.meta_value AS name, COUNT(*) num_posts
            FROM {$this->db->posts} AS p
            INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
            WHERE p.post_type = '{$this->postType}'
            AND m.meta_key = '{$metaKey}'
            GROUP BY name
        ");
    }

    /**
     * @todo remove this?
     * @param string $reviewType
     * @return array
     */
    public function getReviewIdsByType($reviewType)
    {
        $results = $this->db->get_col("
            SELECT DISTINCT m1.meta_value AS review_id
            FROM {$this->db->posts} AS p
            INNER JOIN {$this->db->postmeta} AS m1 ON p.ID = m1.post_id
            INNER JOIN {$this->db->postmeta} AS m2 ON p.ID = m2.post_id
            WHERE p.post_type = '{$this->postType}'
            AND m1.meta_key = '_review_id'
            AND m2.meta_key = '_review_type'
            AND m2.meta_value = '{$reviewType}'
        ");
        return array_keys(array_flip($results));
    }

    /**
     * @param int $greaterThanId
     * @param int $limit
     * @return array
     */
    public function getReviewRatingsFromIds(array $postIds, $greaterThanId = 0, $limit = 100)
    {
        sort($postIds);
        $postIds = array_slice($postIds, intval(array_search($greaterThanId, $postIds)), $limit);
        $postIds = implode(',', $postIds);
        return (array) $this->db->get_results("
            SELECT p.ID, m.meta_value AS rating
            FROM {$this->db->posts} AS p
            INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
            WHERE p.ID > {$greaterThanId}
            AND p.ID IN ('{$postIds}')
            AND p.post_status = 'publish'
            AND p.post_type = '{$this->postType}'
            AND m.meta_key = '_rating'
            GROUP BY p.ID
            ORDER By p.ID ASC
            LIMIT {$limit}
        ");
    }

    /**
     * @param string $key
     * @param string $status
     * @return array
     */
    public function getReviewsMeta($key, $status = 'publish')
    {
        $postStatusQuery = 'all' != $status && !empty($status)
            ? "AND p.post_status = '{$status}'"
            : '';
        $key = Str::prefix('_', $key);
        $values = $this->db->get_col("
            SELECT DISTINCT m.meta_value
            FROM {$this->db->postmeta} m
            LEFT JOIN {$this->db->posts} p ON p.ID = m.post_id
            WHERE p.post_type = '{$this->postType}'
            AND m.meta_key = '{$key}'
            AND m.meta_value > '' -- No empty values or ID's less than 1
            $postStatusQuery
            GROUP BY p.ID -- remove duplicate meta_value entries
            ORDER BY m.meta_id ASC -- sort by oldest meta_value
        ");
        sort($values);
        return $values;
    }

    /**
     * @param string $and
     * @return string
     */
    protected function getAndForCounts(array $args, $and = '')
    {
        $postIds = implode(',', array_filter(Arr::get($args, 'post_ids', [])));
        $termIds = implode(',', array_filter(Arr::get($args, 'term_ids', [])));
        if (!empty($args['type'])) {
            $and .= "AND m2.meta_value = '{$args['type']}' ";
        }
        if ($postIds) {
            $and .= "AND m3.meta_key = '_assigned_to' AND m3.meta_value IN ({$postIds}) ";
        }
        if ($termIds) {
            $and .= "AND tr.term_taxonomy_id IN ({$termIds}) ";
        }
        return glsr()->filter('query/and-for-counts', $and);
    }

    /**
     * @param string $and
     * @return string
     */
    protected function getAndForRatings(array $args, $and = '')
    {
        $assignedQueries = [];
        if ($postIds = Arr::consolidate(Arr::get($args, 'post_ids', []))) {
            $postIds = implode(',', array_filter($postIds));
            $assignedQueries[] = "(ap.post_id IN ({$postIds}) AND ap.is_published = 1)";
        }
        if ($termIds = Arr::consolidate(Arr::get($args, 'term_ids', []))) {
            $termIds = implode(',', array_filter($termIds));
            $assignedQueries[] = "at.term_id IN ({$termIds})";
        }
        if ($rating = Helper::castToInt(Arr::get($args, 'rating'))) {
            ++$rating;
            $and .= "AND r.rating < {$rating} ";
        }
        if ($type = Arr::get($args, 'type')) {
            $and .= "AND r.type = '{$type}' ";
        }
        if ($assignedQuery = implode(' OR ', $assignedQueries)) {
            $and .= "AND ($assignedQuery) ";
        }
        return glsr()->filter('query/and-for-ratings', $and);
    }

    /**
     * @param string $innerJoin
     * @return string
     */
    protected function getInnerJoinForCounts(array $args, $innerJoin = '')
    {
        if (!empty(Arr::get($args, 'post_ids'))) {
            $innerJoin .= "INNER JOIN {$this->db->postmeta} AS m3 ON p.ID = m3.post_id ";
        }
        if (!empty(Arr::get($args, 'term_ids'))) {
            $innerJoin .= "INNER JOIN {$this->db->term_relationships} AS tr ON p.ID = tr.object_id ";
        }
        return glsr()->filter('query/inner-join-for-counts', $innerJoin);
    }

    /**
     * @param string $innerJoin
     * @return string
     */
    protected function getInnerJoinForRatings(array $args, $innerJoin = '')
    {
        if (Arr::consolidate(Arr::get($args, 'post_ids', []))) {
            $table = glsr(SqlSchema::class)->table('assigned_posts');
            $innerJoin .= "INNER JOIN {$table} AS ap ON r.ID = ap.rating_id ";
        }
        if (Arr::consolidate(Arr::get($args, 'term_ids', []))) {
            $table = glsr(SqlSchema::class)->table('assigned_terms');
            $innerJoin .= "INNER JOIN {$table} AS at ON r.ID = at.rating_id ";
        }
        return glsr()->filter('query/inner-join-for-ratings', $innerJoin);
    }
}


1			<?php
2
3			namespace GeminiLabs\SiteReviews\Database;
4
5			use GeminiLabs\SiteReviews\Application;
6			use GeminiLabs\SiteReviews\Database\SqlSchema;
7			use GeminiLabs\SiteReviews\Helper;
8			use GeminiLabs\SiteReviews\Helpers\Arr;
9			use GeminiLabs\SiteReviews\Helpers\Str;
10
11			class SqlQueries
12			{
13			protected $db;
14			protected $postType;
15
16			public function __construct()
17			{
18			global $wpdb;
19			$this->db = $wpdb;
20			$this->postType = Application::POST_TYPE;
21			}
22
23			/**
24			* @return bool
25			*/
26			public function deletePostCountMetaKeys()
27			{
28			return $this->db->query("
29			DELETE
30			FROM {$this->db->postmeta}
31			WHERE meta_key LIKE '_glsr_%'
32			");
33			}
34
35			/**
36			* @return bool
37			*/
38			public function deleteTermCountMetaKeys()
39			{
40			return $this->db->query("
41			DELETE
42			FROM {$this->db->termmeta}
43			WHERE meta_key LIKE '_glsr_%'
44			");
45			}
46
47			/**
48			* @param string $metaReviewId
49			* @return int
50			*/
51			public function getPostIdFromReviewId($metaReviewId)
52			{
53			$postId = $this->db->get_var("
54			SELECT p.ID
55			FROM {$this->db->posts} AS p
56			INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
57			WHERE p.post_type = '{$this->postType}'
58			AND m.meta_key = '_review_id'
59			AND m.meta_value = '{$metaReviewId}'
60			");
61			return intval($postId);
62			}
63
64			/**
65			* @return array
66			*/
67			public function getRatings(array $args)
68			{
69			// get types
70			// get for each type
71			$table = glsr(SqlSchema::class)->table('ratings');
72			return (array) $this->db->get_results("
73			SELECT r.rating AS rating, COUNT(r.rating) AS count
74			FROM {$table} AS r
75			{$this->getInnerJoinForRatings($args)}
76			WHERE r.is_approved = 1
77			{$this->getAndForRatings($args)}
78			GROUP BY rating
79			");
80			}
81
82			/**
83			* @param int $lastPostId
84			* @param int $limit
85			* @return array
86			*/
87			public function getReviewCounts(array $args, $lastPostId = 0, $limit = 500)
88			{
89			return (array) $this->db->get_results("
90			SELECT DISTINCT p.ID, m1.meta_value AS rating, m2.meta_value AS type
91			FROM {$this->db->posts} AS p
92			INNER JOIN {$this->db->postmeta} AS m1 ON p.ID = m1.post_id
93			INNER JOIN {$this->db->postmeta} AS m2 ON p.ID = m2.post_id
94			{$this->getInnerJoinForCounts($args)}
95			WHERE p.ID > {$lastPostId}
96			AND p.post_status = 'publish'
97			AND p.post_type = '{$this->postType}'
98			AND m1.meta_key = '_rating'
99			AND m2.meta_key = '_review_type'
100			{$this->getAndForCounts($args)}
101			ORDER By p.ID ASC
102			LIMIT {$limit}
103			");
104			}
105
106			/**
107			* @todo remove this?
108			* @param string $metaKey
109			* @return array
110			*/
111			public function getReviewCountsFor($metaKey)
112			{
113			$metaKey = Str::prefix('_', $metaKey);
114			return (array) $this->db->get_results("
115			SELECT DISTINCT m.meta_value AS name, COUNT(*) num_posts
116			FROM {$this->db->posts} AS p
117			INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
118			WHERE p.post_type = '{$this->postType}'
119			AND m.meta_key = '{$metaKey}'
120			GROUP BY name
121			");
122			}
123
124			/**
125			* @todo remove this?
126			* @param string $reviewType
127			* @return array
128			*/
129			public function getReviewIdsByType($reviewType)
130			{
131			$results = $this->db->get_col("
132			SELECT DISTINCT m1.meta_value AS review_id
133			FROM {$this->db->posts} AS p
134			INNER JOIN {$this->db->postmeta} AS m1 ON p.ID = m1.post_id
135			INNER JOIN {$this->db->postmeta} AS m2 ON p.ID = m2.post_id
136			WHERE p.post_type = '{$this->postType}'
137			AND m1.meta_key = '_review_id'
138			AND m2.meta_key = '_review_type'
139			AND m2.meta_value = '{$reviewType}'
140			");
141			return array_keys(array_flip($results));
142			}
143
144			/**
145			* @param int $greaterThanId
146			* @param int $limit
147			* @return array
148			*/
149			public function getReviewRatingsFromIds(array $postIds, $greaterThanId = 0, $limit = 100)
150			{
151			sort($postIds);
152			$postIds = array_slice($postIds, intval(array_search($greaterThanId, $postIds)), $limit);
153			$postIds = implode(',', $postIds);
154			return (array) $this->db->get_results("
155			SELECT p.ID, m.meta_value AS rating
156			FROM {$this->db->posts} AS p
157			INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
158			WHERE p.ID > {$greaterThanId}
159			AND p.ID IN ('{$postIds}')
160			AND p.post_status = 'publish'
161			AND p.post_type = '{$this->postType}'
162			AND m.meta_key = '_rating'
163			GROUP BY p.ID
164			ORDER By p.ID ASC
165			LIMIT {$limit}
166			");
167			}
168
169			/**
170			* @param string $key
171			* @param string $status
172			* @return array
173			*/
174			public function getReviewsMeta($key, $status = 'publish')
175			{
176			$postStatusQuery = 'all' != $status && !empty($status)
177			? "AND p.post_status = '{$status}'"
178			: '';
179			$key = Str::prefix('_', $key);
180			$values = $this->db->get_col("
181			SELECT DISTINCT m.meta_value
182			FROM {$this->db->postmeta} m
183			LEFT JOIN {$this->db->posts} p ON p.ID = m.post_id
184			WHERE p.post_type = '{$this->postType}'
185			AND m.meta_key = '{$key}'
186			AND m.meta_value > '' -- No empty values or ID's less than 1
187			$postStatusQuery
188			GROUP BY p.ID -- remove duplicate meta_value entries
189			ORDER BY m.meta_id ASC -- sort by oldest meta_value
190			");
191			sort($values);
192			return $values;
193			}
194
195			/**
196			* @param string $and
197			* @return string
198			*/
199			protected function getAndForCounts(array $args, $and = '')
200			{
201			$postIds = implode(',', array_filter(Arr::get($args, 'post_ids', [])));
202			$termIds = implode(',', array_filter(Arr::get($args, 'term_ids', [])));
203			if (!empty($args['type'])) {
204			$and .= "AND m2.meta_value = '{$args['type']}' ";
205			}
206			if ($postIds) {
207			$and .= "AND m3.meta_key = '_assigned_to' AND m3.meta_value IN ({$postIds}) ";
208			}
209			if ($termIds) {
210			$and .= "AND tr.term_taxonomy_id IN ({$termIds}) ";
211			}
212			return glsr()->filter('query/and-for-counts', $and);
213			}
214
215			/**
216			* @param string $and
217			* @return string
218			*/
219			protected function getAndForRatings(array $args, $and = '')
220			{
221			$assignedQueries = [];
222			if ($postIds = Arr::consolidate(Arr::get($args, 'post_ids', []))) {
223			$postIds = implode(',', array_filter($postIds));
224			$assignedQueries[] = "(ap.post_id IN ({$postIds}) AND ap.is_published = 1)";
225			}
226			if ($termIds = Arr::consolidate(Arr::get($args, 'term_ids', []))) {
227			$termIds = implode(',', array_filter($termIds));
228			$assignedQueries[] = "at.term_id IN ({$termIds})";
229			}
230			if ($rating = Helper::castToInt(Arr::get($args, 'rating'))) {
231			++$rating;
232			$and .= "AND r.rating < {$rating} ";
233			}
234			if ($type = Arr::get($args, 'type')) {
235			$and .= "AND r.type = '{$type}' ";
236			}
237			if ($assignedQuery = implode(' OR ', $assignedQueries)) {
238			$and .= "AND ($assignedQuery) ";
239			}
240			return glsr()->filter('query/and-for-ratings', $and);
241			}
242
243			/**
244			* @param string $innerJoin
245			* @return string
246			*/
247			protected function getInnerJoinForCounts(array $args, $innerJoin = '')
248			{
249			if (!empty(Arr::get($args, 'post_ids'))) {
250			$innerJoin .= "INNER JOIN {$this->db->postmeta} AS m3 ON p.ID = m3.post_id ";
251			}
252			if (!empty(Arr::get($args, 'term_ids'))) {
253			$innerJoin .= "INNER JOIN {$this->db->term_relationships} AS tr ON p.ID = tr.object_id ";
254			}
255			return glsr()->filter('query/inner-join-for-counts', $innerJoin);
256			}
257
258			/**
259			* @param string $innerJoin
260			* @return string
261			*/
262			protected function getInnerJoinForRatings(array $args, $innerJoin = '')
263			{
264			if (Arr::consolidate(Arr::get($args, 'post_ids', []))) {
265			$table = glsr(SqlSchema::class)->table('assigned_posts');
266			$innerJoin .= "INNER JOIN {$table} AS ap ON r.ID = ap.rating_id ";
267			}
268			if (Arr::consolidate(Arr::get($args, 'term_ids', []))) {
269			$table = glsr(SqlSchema::class)->table('assigned_terms');
270			$innerJoin .= "INNER JOIN {$table} AS at ON r.ID = at.rating_id ";
271			}
272			return glsr()->filter('query/inner-join-for-ratings', $innerJoin);
273			}
274			}
275

pryley / site-reviews

Push — tmp ( 15f615...89cc97 )

SqlQueries::getReviewIdsByType() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like