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

Passed

Push — hotfix/fix-counts ( bd8a94 )

by Paul

created 2019-03-10 15:44 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
nc	1
nop	1
dl	0
loc	13
ccs	0
cts	8
cp	0
crap	2
rs	10
c	0
b	0
f	0

<?php

namespace GeminiLabs\SiteReviews\Database;

use GeminiLabs\SiteReviews\Application;
use GeminiLabs\SiteReviews\Database\QueryBuilder;

class SqlQueries
{
	protected $db;

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

	/**
	 * @param string $sessionCookiePrefix
	 * @return int|false
	 */
	public function deleteAllSessions( $sessionCookiePrefix )
	{
		return $this->db->query("
			DELETE
			FROM {$this->db->options}
			WHERE option_name LIKE '{$sessionCookiePrefix}_%'
		");
	}

	/**
	 * @param string $expiredSessions
	 * @return int|false
	 */
	public function deleteExpiredSessions( $expiredSessions )
	{
		return $this->db->query("
			DELETE
			FROM {$this->db->options}
			WHERE option_name IN ('{$expiredSessions}')
		");
	}

	/**
	 * @param string $sessionCookiePrefix
	 * @param int $limit
	 * @return array
	 */
	public function getExpiredSessions( $sessionCookiePrefix, $limit )
	{
		return $this->db->get_results("
			SELECT option_name AS name, option_value AS expiration
			FROM {$this->db->options}
			WHERE option_name LIKE '{$sessionCookiePrefix}_expires_%'
			ORDER BY option_value ASC
			LIMIT 0, {$limit}
		");
	}

	/**
	 * @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 = '{Application::POST_TYPE}'
			AND m.meta_key = 'review_id'
			AND m.meta_value = '{$metaReviewId}'
		");
		return intval( $postId );
	}

	/**
	 * @param int $lastPostId
	 * @param int $limit
	 * @return array
	 */
	public function getReviewCounts( array $args, $lastPostId = 0, $limit = 500 )
	{
		return $this->db->get_results("
			SELECT 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 = '{Application::POST_TYPE}'
			AND m1.meta_key = 'rating'
			AND m2.meta_key = 'review_type'
			{$this->getAndForCounts( $args )}
			ORDER By p.ID
			ASC LIMIT {$limit}
		", OBJECT );
	}

	/**
	 * @param string $metaKey
	 * @return array
	 */
	public function getReviewCountsFor( $metaKey )
	{
		return (array) $this->db->get_results("
			SELECT 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 = '{Application::POST_TYPE}'
			AND m.meta_key = '{$metaKey}'
			GROUP BY name
		");
	}

	/**
	 * @param string $reviewType
	 * @return array
	 */
	public function getReviewIdsByType( $reviewType )
	{
		$query = $this->db->get_col("
			SELECT 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->db->postType}'
			AND m1.meta_key = 'review_id'
			AND m2.meta_key = 'review_type'
			AND m2.meta_value = '{$reviewType}'
		");
		return array_keys( array_flip( $query ));
	}

	/**
	 * @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 $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 = '{Application::POST_TYPE}'
			AND m.meta_key = 'rating'
			ORDER By p.ID
			ASC LIMIT {$limit}
		", OBJECT );
	}

	/**
	 * @param string $key
	 * @param string $status
	 * @return array
	 */
	public function getReviewsMeta( $key, $status = 'publish' )
	{
		$queryBuilder = glsr( QueryBuilder::class );
		$key = $queryBuilder->buildSqlOr( $key, "m.meta_key = '%s'" );
		$status = $queryBuilder->buildSqlOr( $status, "p.post_status = '%s'" );
		return $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 = '{Application::POST_TYPE}'
			AND ({$key})
			AND ({$status})
			ORDER BY m.meta_value
		");
	}

	/**
	 * @param string $and
	 * @return string
	 */
	protected function getAndForCounts( array $args, $and = '' )
	{
		if( !empty( $args['post_id'] )) {
			$and.= "AND m3.meta_key = 'assigned_to' AND m3.meta_value = {$args['post_id']}";
		}
		if( !empty( $args['term_taxonomy_id'] )) {
			$and.= "AND tr.term_taxonomy_id = {$args['term_taxonomy_id']}";
		}
		return $and;
	}

	/**
	 * @param string $innerJoin
	 * @return string
	 */
	protected function getInnerJoinForCounts( array $args, $innerJoin = '' )
	{
		if( !empty( $args['post_id'] )) {
			$innerJoin.= "INNER JOIN {$this->db->postmeta} AS m3 ON p.ID = m3.post_id";
		}
		if( !empty( $args['term_taxonomy_id'] )) {
			$innerJoin.= "INNER JOIN {$this->db->term_relationships} AS tr ON p.ID = tr.object_id";
		}
		return $innerJoin;
	}
}


1		<?php
2
3		namespace GeminiLabs\SiteReviews\Database;
4
5		use GeminiLabs\SiteReviews\Application;
6		use GeminiLabs\SiteReviews\Database\QueryBuilder;
7
8		class SqlQueries
9		{
10		protected $db;
11
12	1	public function __construct()
13		{
14	1	global $wpdb;
15	1	$this->db = $wpdb;
16	1	}
17
18		/**
19		* @param string $sessionCookiePrefix
20		* @return int\|false
21		*/
22		public function deleteAllSessions( $sessionCookiePrefix )
23		{
24		return $this->db->query("
25		DELETE
26		FROM {$this->db->options}
27		WHERE option_name LIKE '{$sessionCookiePrefix}_%'
28		");
29		}
30
31		/**
32		* @param string $expiredSessions
33		* @return int\|false
34		*/
35		public function deleteExpiredSessions( $expiredSessions )
36		{
37		return $this->db->query("
38		DELETE
39		FROM {$this->db->options}
40		WHERE option_name IN ('{$expiredSessions}')
41		");
42		}
43
44		/**
45		* @param string $sessionCookiePrefix
46		* @param int $limit
47		* @return array
48		*/
49		public function getExpiredSessions( $sessionCookiePrefix, $limit )
50		{
51		return $this->db->get_results("
52		SELECT option_name AS name, option_value AS expiration
53		FROM {$this->db->options}
54		WHERE option_name LIKE '{$sessionCookiePrefix}_expires_%'
55		ORDER BY option_value ASC
56		LIMIT 0, {$limit}
57		");
58		}
59
60		/**
61		* @param string $metaReviewId
62		* @return int
63		*/
64		public function getPostIdFromReviewId( $metaReviewId )
65		{
66		$postId = $this->db->get_var("
67		SELECT p.ID
68		FROM {$this->db->posts} AS p
69		INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
70		WHERE p.post_type = '{Application::POST_TYPE}'
71		AND m.meta_key = 'review_id'
72		AND m.meta_value = '{$metaReviewId}'
73		");
74		return intval( $postId );
75		}
76
77		/**
78		* @param int $lastPostId
79		* @param int $limit
80		* @return array
81		*/
82	1	public function getReviewCounts( array $args, $lastPostId = 0, $limit = 500 )
83		{
84	1	return $this->db->get_results("
85		SELECT p.ID, m1.meta_value AS rating, m2.meta_value AS type
86	1	FROM {$this->db->posts} AS p
87	1	INNER JOIN {$this->db->postmeta} AS m1 ON p.ID = m1.post_id
88	1	INNER JOIN {$this->db->postmeta} AS m2 ON p.ID = m2.post_id
89	1	{$this->getInnerJoinForCounts( $args )}
90	1	WHERE p.ID > {$lastPostId}
91		AND p.post_status = 'publish'
92		AND p.post_type = '{Application::POST_TYPE}'
93		AND m1.meta_key = 'rating'
94		AND m2.meta_key = 'review_type'
95	1	{$this->getAndForCounts( $args )}
96		ORDER By p.ID
97	1	ASC LIMIT {$limit}
98	1	", OBJECT );
99		}
100
101		/**
102		* @param string $metaKey
103		* @return array
104		*/
105		public function getReviewCountsFor( $metaKey )
106		{
107		return (array) $this->db->get_results("
108		SELECT m.meta_value AS name, COUNT(*) num_posts
109		FROM {$this->db->posts} AS p
110		INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
111		WHERE p.post_type = '{Application::POST_TYPE}'
112		AND m.meta_key = '{$metaKey}'
113		GROUP BY name
114		");
115		}
116
117		/**
118		* @param string $reviewType
119		* @return array
120		*/
121		public function getReviewIdsByType( $reviewType )
122		{
123		$query = $this->db->get_col("
124		SELECT m1.meta_value AS review_id
125		FROM {$this->db->posts} AS p
126		INNER JOIN {$this->db->postmeta} AS m1 ON p.ID = m1.post_id
127		INNER JOIN {$this->db->postmeta} AS m2 ON p.ID = m2.post_id
128		WHERE p.post_type = '{$this->db->postType}'
129		AND m1.meta_key = 'review_id'
130		AND m2.meta_key = 'review_type'
131		AND m2.meta_value = '{$reviewType}'
132		");
133		return array_keys( array_flip( $query ));
134		}
135
136		/**
137		* @param int $greaterThanId
138		* @param int $limit
139		* @return array
140		*/
141		public function getReviewRatingsFromIds( array $postIds, $greaterThanId = 0, $limit = 100 )
142		{
143		sort( $postIds );
144		$postIds = array_slice( $postIds, intval( array_search( $greaterThanId, $postIds )), $limit );
145		$postIds = implode( ',', $postIds );
146		return $this->db->get_results("
147		SELECT p.ID, m.meta_value AS rating
148		FROM {$this->db->posts} AS p
149		INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
150		WHERE p.ID > {$greaterThanId}
151		AND p.ID IN ('{$postIds}')
152		AND p.post_status = 'publish'
153		AND p.post_type = '{Application::POST_TYPE}'
154		AND m.meta_key = 'rating'
155		ORDER By p.ID
156		ASC LIMIT {$limit}
157		", OBJECT );
158		}
159
160		/**
161		* @param string $key
162		* @param string $status
163		* @return array
164		*/
165	1	public function getReviewsMeta( $key, $status = 'publish' )
166		{
167	1	$queryBuilder = glsr( QueryBuilder::class );
168	1	$key = $queryBuilder->buildSqlOr( $key, "m.meta_key = '%s'" );
169	1	$status = $queryBuilder->buildSqlOr( $status, "p.post_status = '%s'" );
170	1	return $this->db->get_col("
171		SELECT DISTINCT m.meta_value
172	1	FROM {$this->db->postmeta} m
173	1	LEFT JOIN {$this->db->posts} p ON p.ID = m.post_id
174		WHERE p.post_type = '{Application::POST_TYPE}'
175	1	AND ({$key})
176	1	AND ({$status})
177		ORDER BY m.meta_value
178		");
179		}
180
181		/**
182		* @param string $and
183		* @return string
184		*/
185	1	protected function getAndForCounts( array $args, $and = '' )
186		{
187	1	if( !empty( $args['post_id'] )) {
188		$and.= "AND m3.meta_key = 'assigned_to' AND m3.meta_value = {$args['post_id']}";
189		}
190	1	if( !empty( $args['term_taxonomy_id'] )) {
191		$and.= "AND tr.term_taxonomy_id = {$args['term_taxonomy_id']}";
192		}
193	1	return $and;
194		}
195
196		/**
197		* @param string $innerJoin
198		* @return string
199		*/
200	1	protected function getInnerJoinForCounts( array $args, $innerJoin = '' )
201		{
202	1	if( !empty( $args['post_id'] )) {
203		$innerJoin.= "INNER JOIN {$this->db->postmeta} AS m3 ON p.ID = m3.post_id";
204		}
205	1	if( !empty( $args['term_taxonomy_id'] )) {
206		$innerJoin.= "INNER JOIN {$this->db->term_relationships} AS tr ON p.ID = tr.object_id";
207		}
208	1	return $innerJoin;
209		}
210		}
211

pryley / site-reviews

Push — hotfix/fix-counts ( bd8a94 )

SqlQueries::getReviewIdsByType() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like