SqlQueries::getReviewTermCounts() - Code Metrics - Inspection of "only migrate v2 settings if they exist" - pryley/site-reviews - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( a42385...9b46a7 )

by Paul

created 2018-09-14 02:26 UTC

SqlQueries::getReviewTermCounts() A

↳ Parent: SqlQueries

Complexity

Conditions	1
Paths	1

Size

Total Lines	17
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes

Metric	Value
cc	1
eloc	10
nc	1
nop	3
dl	0
loc	17
ccs	0
cts	11
cp	0
crap	2
rs	9.9332
c	0
b	0
f	0

<?php

namespace GeminiLabs\SiteReviews\Database;

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

class SqlQueries
{
	protected $db;
	protected $postType;

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

	/**
	 * @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 $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 = '{$this->postType}'
			AND m.meta_key = '{$metaKey}'
			GROUP BY name
		");
	}

	/**
	 * @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 int $lastPostId
	 * @param int $limit
	 * @return array
	 */
	public function getReviewCounts( $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
			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'
			ORDER By p.ID
			ASC LIMIT {$limit}
		", OBJECT );
	}

	/**
	 * @param string $metaReviewId
	 * @return int
	 */
	public function getReviewPostId( $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 );
	}

	/**
	 * @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 $postId
	 * @param int $lastPostId
	 * @param int $limit
	 * @return array
	 */
	public function getReviewPostCounts( $postId, $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
			INNER JOIN {$this->db->postmeta} AS m3 ON p.ID = m3.post_id
			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'
			AND m3.meta_key = 'assigned_to'
			AND m3.meta_value = {$postId}
			ORDER By p.ID
			ASC LIMIT {$limit}
		", OBJECT );
	}

	/**
	 * @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 = '{$this->postType}'
			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 = '{$this->postType}'
			AND ({$key})
			AND ({$status})
			ORDER BY m.meta_value
		");
	}

	/**
	 * @param int $termId
	 * @param int $lastPostId
	 * @param int $limit
	 * @return array
	 */
	public function getReviewTermCounts( $termId, $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
			INNER JOIN {$this->db->term_relationships} AS tr ON p.ID = tr.object_id
			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'
			AND tr.term_taxonomy_id = {$termId}
			ORDER By p.ID
			ASC LIMIT {$limit}
		", OBJECT );
	}
}


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		protected $postType;
12
13	1	public function __construct()
14		{
15	1	global $wpdb;
16	1	$this->db = $wpdb;
17	1	$this->postType = Application::POST_TYPE;
18	1	}
19
20		/**
21		* @param string $sessionCookiePrefix
22		* @return int\|false
23		*/
24		public function deleteAllSessions( $sessionCookiePrefix )
25		{
26		return $this->db->query("
27		DELETE
28		FROM {$this->db->options}
29		WHERE option_name LIKE '{$sessionCookiePrefix}_%'
30		");
31		}
32
33		/**
34		* @param string $expiredSessions
35		* @return int\|false
36		*/
37		public function deleteExpiredSessions( $expiredSessions )
38		{
39		return $this->db->query("
40		DELETE
41		FROM {$this->db->options}
42		WHERE option_name IN ('{$expiredSessions}')
43		");
44		}
45
46		/**
47		* @param string $metaKey
48		* @return array
49		*/
50		public function getReviewCountsFor( $metaKey )
51		{
52		return (array) $this->db->get_results("
53		SELECT m.meta_value AS name, COUNT(*) num_posts
54		FROM {$this->db->posts} AS p
55		INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
56		WHERE p.post_type = '{$this->postType}'
57		AND m.meta_key = '{$metaKey}'
58		GROUP BY name
59		");
60		}
61
62		/**
63		* @param string $sessionCookiePrefix
64		* @param int $limit
65		* @return array
66		*/
67		public function getExpiredSessions( $sessionCookiePrefix, $limit )
68		{
69		return $this->db->get_results("
70		SELECT option_name AS name, option_value AS expiration
71		FROM {$this->db->options}
72		WHERE option_name LIKE '{$sessionCookiePrefix}_expires_%'
73		ORDER BY option_value ASC
74		LIMIT 0, {$limit}
75		");
76		}
77
78		/**
79		* @param int $lastPostId
80		* @param int $limit
81		* @return array
82		*/
83	1	public function getReviewCounts( $lastPostId = 0, $limit = 500 )
84		{
85	1	return $this->db->get_results("
86		SELECT p.ID, m1.meta_value AS rating, m2.meta_value AS type
87	1	FROM {$this->db->posts} AS p
88	1	INNER JOIN {$this->db->postmeta} AS m1 ON p.ID = m1.post_id
89	1	INNER JOIN {$this->db->postmeta} AS m2 ON p.ID = m2.post_id
90	1	WHERE p.ID > {$lastPostId}
91		AND p.post_status = 'publish'
92	1	AND p.post_type = '{$this->postType}'
93		AND m1.meta_key = 'rating'
94		AND m2.meta_key = 'review_type'
95		ORDER By p.ID
96	1	ASC LIMIT {$limit}
97	1	", OBJECT );
98		}
99
100		/**
101		* @param string $metaReviewId
102		* @return int
103		*/
104		public function getReviewPostId( $metaReviewId )
105		{
106		$postId = $this->db->get_var("
107		SELECT p.ID
108		FROM {$this->db->posts} AS p
109		INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
110		WHERE p.post_type = '{$this->postType}'
111		AND m.meta_key = 'review_id'
112		AND m.meta_value = '{$metaReviewId}'
113		");
114		return intval( $postId );
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 $postId
138		* @param int $lastPostId
139		* @param int $limit
140		* @return array
141		*/
142		public function getReviewPostCounts( $postId, $lastPostId = 0, $limit = 500 )
143		{
144		return $this->db->get_results("
145		SELECT p.ID, m1.meta_value AS rating, m2.meta_value AS type
146		FROM {$this->db->posts} AS p
147		INNER JOIN {$this->db->postmeta} AS m1 ON p.ID = m1.post_id
148		INNER JOIN {$this->db->postmeta} AS m2 ON p.ID = m2.post_id
149		INNER JOIN {$this->db->postmeta} AS m3 ON p.ID = m3.post_id
150		WHERE p.ID > {$lastPostId}
151		AND p.post_status = 'publish'
152		AND p.post_type = '{$this->postType}'
153		AND m1.meta_key = 'rating'
154		AND m2.meta_key = 'review_type'
155		AND m3.meta_key = 'assigned_to'
156		AND m3.meta_value = {$postId}
157		ORDER By p.ID
158		ASC LIMIT {$limit}
159		", OBJECT );
160		}
161
162		/**
163		* @param int $greaterThanId
164		* @param int $limit
165		* @return array
166		*/
167		public function getReviewRatingsFromIds( array $postIds, $greaterThanId = 0, $limit = 100 )
168		{
169		sort( $postIds );
170		$postIds = array_slice( $postIds, intval( array_search( $greaterThanId, $postIds )), $limit );
171		$postIds = implode( ',', $postIds );
172		return $this->db->get_results("
173		SELECT p.ID, m.meta_value AS rating
174		FROM {$this->db->posts} AS p
175		INNER JOIN {$this->db->postmeta} AS m ON p.ID = m.post_id
176		WHERE p.ID > {$greaterThanId}
177		AND p.ID IN ('{$postIds}')
178		AND p.post_status = 'publish'
179		AND p.post_type = '{$this->postType}'
180		AND m.meta_key = 'rating'
181		ORDER By p.ID
182		ASC LIMIT {$limit}
183		", OBJECT );
184		}
185
186		/**
187		* @param string $key
188		* @param string $status
189		* @return array
190		*/
191	1	public function getReviewsMeta( $key, $status = 'publish' )
192		{
193	1	$queryBuilder = glsr( QueryBuilder::class );
194	1	$key = $queryBuilder->buildSqlOr( $key, "m.meta_key = '%s'" );
195	1	$status = $queryBuilder->buildSqlOr( $status, "p.post_status = '%s'" );
196	1	return $this->db->get_col("
197		SELECT DISTINCT m.meta_value
198	1	FROM {$this->db->postmeta} m
199	1	LEFT JOIN {$this->db->posts} p ON p.ID = m.post_id
200	1	WHERE p.post_type = '{$this->postType}'
201	1	AND ({$key})
202	1	AND ({$status})
203		ORDER BY m.meta_value
204		");
205		}
206
207		/**
208		* @param int $termId
209		* @param int $lastPostId
210		* @param int $limit
211		* @return array
212		*/
213		public function getReviewTermCounts( $termId, $lastPostId = 0, $limit = 500 )
214		{
215		return $this->db->get_results("
216		SELECT p.ID, m1.meta_value AS rating, m2.meta_value AS type
217		FROM {$this->db->posts} AS p
218		INNER JOIN {$this->db->postmeta} AS m1 ON p.ID = m1.post_id
219		INNER JOIN {$this->db->postmeta} AS m2 ON p.ID = m2.post_id
220		INNER JOIN {$this->db->term_relationships} AS tr ON p.ID = tr.object_id
221		WHERE p.ID > {$lastPostId}
222		AND p.post_status = 'publish'
223		AND p.post_type = '{$this->postType}'
224		AND m1.meta_key = 'rating'
225		AND m2.meta_key = 'review_type'
226		AND tr.term_taxonomy_id = {$termId}
227		ORDER By p.ID
228		ASC LIMIT {$limit}
229		", OBJECT );
230		}
231		}
232

pryley / site-reviews

Push — master ( a42385...9b46a7 )

SqlQueries::getReviewTermCounts() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like