Sql::clauseIfValueNotEmpty() - Code Metrics - Inspection of "v5.10.3 release" - pryley/site-reviews - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( b694dd...6c1d9a )

by Paul

created 2021-04-28 18:47 UTC

Sql::clauseIfValueNotEmpty() A

↳ Parent: Sql

Complexity

Conditions	4
Paths	4

Size

Total Lines	12
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	8
CRAP Score	4

Importance

Changes

Metric	Value
eloc	7
c	0
b	0
f	0
dl	0
loc	12
ccs	8
cts	8
cp	1
rs	10
cc	4
nc	4
nop	3
crap	4

<?php

namespace GeminiLabs\SiteReviews\Database;

use GeminiLabs\SiteReviews\Database;
use GeminiLabs\SiteReviews\Helper;
use GeminiLabs\SiteReviews\Helpers\Arr;
use GeminiLabs\SiteReviews\Helpers\Cast;
use GeminiLabs\SiteReviews\Helpers\Str;

trait Sql
{
    /**
     * @var array
     */
    public $args;

    /**
     * @var \wpdb
     */
    public $db;

    /**
     * @param string $clause
     * @return array
     */
    public function clauses($clause, array $values = [])
    {
        $prefix = Str::restrictTo('and,join', $clause);
        foreach ($this->args as $key => $value) {
            $method = Helper::buildMethodName($key, 'clause-'.$prefix);
            if (!method_exists($this, $method) || Helper::isEmpty($value)) {
                continue;
            }
            if ($statement = call_user_func([$this, $method])) {
                $values[$key] = $statement;
            }
        }
        return $values;
    }

    /**
     * @return string
     */
    public function escFieldsForInsert(array $fields)
    {
        return sprintf('(`%s`)', implode('`,`', $fields));
    }

    /**
     * @return string
     */
    public function escValuesForInsert(array $values)
    {
        $values = array_values(array_map('esc_sql', $values));
        return sprintf("('%s')", implode("','", $values));
    }

    /**
     * @param string $statement
     * @return string
     */
    public function sql($statement)
    {
        $handle = $this->sqlHandle();
        $statement = glsr()->filterString('database/sql/'.$handle, $statement);
        glsr()->action('database/sql/'.$handle, $statement);
        glsr()->action('database/sql', $statement, $handle);
        return $statement;
    }

    /**
     * @return string
     */
    public function sqlJoin()
    {
        $join = $this->clauses('join');
        $join = glsr()->filterArrayUnique('query/sql/join', $join, $this->sqlHandle(), $this);
        return implode(' ', $join);
    }

    /**
     * @return string
     */
    public function sqlLimit()
    {
        $limit = Helper::ifTrue($this->args['per_page'] > 0,
            $this->db->prepare('LIMIT %d', $this->args['per_page'])
        );
        return glsr()->filterString('query/sql/limit', $limit, $this->sqlHandle(), $this);
    }

    /**
     * @return string
     */
    public function sqlOffset()
    {
        $offsetBy = (($this->args['page'] - 1) * $this->args['per_page']) + $this->args['offset'];
        $offset = Helper::ifTrue($offsetBy > 0,
            $this->db->prepare('OFFSET %d', $offsetBy)
        );
        return glsr()->filterString('query/sql/offset', $offset, $this->sqlHandle(), $this);
    }

    /**
     * @return string|void
     */
    public function sqlOrderBy()
    {
        $values = [
            'random' => 'RAND()',
        ];
        $order = $this->args['order'];
        $orderby = $this->args['orderby'];
        $orderedby = [];
        if (Str::startsWith(['p.', 'r.'], $orderby)) {
            $orderedby[] = "r.is_pinned {$order}";
            $orderedby[] = "{$orderby} {$order}";
        } elseif (array_key_exists($orderby, $values)) {
            $orderedby[] = $values[$orderby];
        }
        $orderedby = glsr()->filterArrayUnique('query/sql/order-by', $orderedby, $this->sqlHandle(), $this);
        if (!empty($orderedby)) {
            return 'ORDER BY '.implode(', ', $orderedby);
        }
    }

    /**
     * @return string
     */
    public function sqlWhere()
    {
        $and = $this->clauses('and');
        $and = glsr()->filterArrayUnique('query/sql/and', $and, $this->sqlHandle(), $this);
        $and = $this->normalizeAndClauses($and);
        return 'WHERE 1=1 '.implode(' ', $and);
    }

    /**
     * @return string
     */
    public function table($table)
    {
        return glsr(SqlSchema::class)->table($table);
    }

    /**
     * @return string
     */
    protected function clauseAndAssignedPosts()
    {
        return $this->clauseIfValueNotEmpty('(apt.post_id IN (%s) AND apt.is_published = 1)', $this->args['assigned_posts']);
    }

    /**
     * @return string
     */
    protected function clauseAndAssignedTerms()
    {
        return $this->clauseIfValueNotEmpty('(att.term_id IN (%s))', $this->args['assigned_terms']);
    }

    /**
     * @return string
     */
    protected function clauseAndAssignedUsers()
    {
        return $this->clauseIfValueNotEmpty('(aut.user_id IN (%s))', $this->args['assigned_users']);
    }

    /**
     * @return string
     */
    protected function clauseAndDate()
    {
        $clauses = [];
        $date = $this->args['date'];
        if (!empty($date['after'])) {
            $clauses[] = $this->db->prepare("(p.post_date >{$date['inclusive']} %s)", $date['after']);
        }
        if (!empty($date['before'])) {
            $clauses[] = $this->db->prepare("(p.post_date <{$date['inclusive']} %s)", $date['before']);
        }
        if (!empty($date['year'])) {
            $clauses[] = $this->db->prepare('(YEAR(p.post_date) = %d AND MONTH(p.post_date) = %d AND DAYOFMONTH(p.post_date) = %d)',
                $date['year'], $date['month'], $date['day']
            );
        }
        if ($clauses = implode(' AND ', $clauses)) {
            return sprintf('AND (%s)', $clauses);
        }
        return '';
    }

    /**
     * @return string
     */
    protected function clauseAndEmail()
    {
        return $this->clauseIfValueNotEmpty('AND r.email = %s', $this->args['email']);
    }

    /**
     * @return string
     */
    protected function clauseAndIpAddress()
    {
        return $this->clauseIfValueNotEmpty('AND r.ip_address = %s', $this->args['ip_address']);
    }

    /**
     * @return string
     */
    protected function clauseAndPostIn()
    {
        return $this->clauseIfValueNotEmpty('AND r.review_id IN (%s)', $this->args['post__in']);
    }

    /**
     * @return string
     */
    protected function clauseAndPostNotIn()
    {
        return $this->clauseIfValueNotEmpty('AND r.review_id NOT IN (%s)', $this->args['post__not_in']);
    }

    /**
     * @return string
     */
    protected function clauseAndRating()
    {
        return Helper::ifTrue($this->args['rating'] > 0,
            $this->db->prepare('AND r.rating > %d', --$this->args['rating'])
        );
    }

    /**
     * @return string
     */
    protected function clauseAndStatus()
    {
        return $this->clauseIfValueNotEmpty('AND r.is_approved = %d', $this->args['status']);
    }

    /**
     * @return string
     */
    protected function clauseAndTerms()
    {
        if (Helper::isEmpty($this->args['terms'])) {
            return '';
        }
        $value = Cast::toInt(Cast::toBool($this->args['terms']));
        return $this->clauseIfValueNotEmpty('AND r.terms = %d', $value);
    }

    /**
     * @return string
     */
    protected function clauseAndType()
    {
        return $this->clauseIfValueNotEmpty('AND r.type = %s', $this->args['type']);
    }

    /**
     * @return string
     */
    protected function clauseAndUserIn()
    {
        return $this->clauseIfValueNotEmpty('AND p.post_author IN (%s)', $this->args['user__in']);
    }

    /**
     * @return string
     */
    protected function clauseAndUserNotIn()
    {
        return $this->clauseIfValueNotEmpty('AND p.post_author NOT IN (%s)', $this->args['user__not_in']);
    }

    /**
     * @param string $clause
     * @param array|int|string $value
     * @param bool $prepare
     * @return string
     */
    protected function clauseIfValueNotEmpty($clause, $value, $prepare = true)
    {
        if (Helper::isEmpty($value)) {
            return '';
        }
        if (!$prepare) {
            return $clause;
        }
        if (is_array($value)) {
            $value = implode(',', $value);
        }
        return $this->db->prepare($clause, $value);
    }

    /**
     * @return string
     */
    protected function clauseJoinAssignedPosts()
    {
        return $this->clauseIfValueNotEmpty(
            "INNER JOIN {$this->table('assigned_posts')} AS apt ON r.ID = apt.rating_id",
            $this->args['assigned_posts'],
            $prepare = false
        );
    }

    /**
     * @return string
     */
    protected function clauseJoinAssignedTerms()
    {
        return $this->clauseIfValueNotEmpty(
            "INNER JOIN {$this->table('assigned_terms')} AS att ON r.ID = att.rating_id",
            $this->args['assigned_terms'],
            $prepare = false
        );
    }

    /**
     * @return string
     */
    protected function clauseJoinAssignedUsers()
    {
        return $this->clauseIfValueNotEmpty(
            "INNER JOIN {$this->table('assigned_users')} AS aut ON r.ID = aut.rating_id",
            $this->args['assigned_users'],
            $prepare = false
        );
    }

    /**
     * @return string
     */
    protected function clauseJoinDate()
    {
        return $this->clauseIfValueNotEmpty(
            "INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID",
            array_filter($this->args['date']),
            $prepare = false
        );
    }

    /**
     * @return string
     */
    protected function clauseJoinUserIn()
    {
        return $this->clauseIfValueNotEmpty(
            "INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID",
            $this->args['user__in'],
            $prepare = false
        );
    }

    /**
     * @return string
     */
    protected function clauseJoinUserNotIn()
    {
        return $this->clauseIfValueNotEmpty(
            "INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID",
            $this->args['user__not_in'],
            $prepare = false
        );
    }

    /**
     * @return string
     */
    protected function clauseJoinOrderBy()
    {
        return Helper::ifTrue(Str::startsWith('p.', $this->args['orderby']),
            "INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID"
        );
    }

    /**
     * @return array
     */
    protected function normalizeAndClauses(array $and)
    {
        $clauses = [];
        foreach ($and as $key => $value) {
            if (Str::startsWith('assigned_', $key)) {
                $clauses[] = $value;
                unset($and[$key]);
            }
        }
        $operator = glsr()->filterString('query/sql/clause/operator', 'OR', $clauses, $this->args);
        $operator = strtoupper($operator);
        $operator = Helper::ifTrue(in_array($operator, ['AND', 'OR']), $operator, 'OR');
        if ($clauses = implode(" {$operator} ", $clauses)) {
            $and['assigned'] = "AND ($clauses)";
        }
        return $and;
    }

    /**
     * @param int $depth
     * @return string
     */
    protected function sqlHandle($depth = 2)
    {
        return Str::dashCase(Arr::get((new \Exception())->getTrace(), $depth.'.function'));
    }
}


1		<?php
2
3		namespace GeminiLabs\SiteReviews\Database;
4
5		use GeminiLabs\SiteReviews\Database;
6		use GeminiLabs\SiteReviews\Helper;
7		use GeminiLabs\SiteReviews\Helpers\Arr;
8		use GeminiLabs\SiteReviews\Helpers\Cast;
9		use GeminiLabs\SiteReviews\Helpers\Str;
10
11		trait Sql
12		{
13		/**
14		* @var array
15		*/
16		public $args;
17
18		/**
19		* @var \wpdb
20		*/
21		public $db;
22
23		/**
24		* @param string $clause
25		* @return array
26		*/
27	8	public function clauses($clause, array $values = [])
28		{
29	8	$prefix = Str::restrictTo('and,join', $clause);
30	8	foreach ($this->args as $key => $value) {
31	8	$method = Helper::buildMethodName($key, 'clause-'.$prefix);
32	8	if (!method_exists($this, $method) \|\| Helper::isEmpty($value)) {
33	8	continue;
34		}
35	8	if ($statement = call_user_func([$this, $method])) {
36	8	$values[$key] = $statement;
37		}
38		}
39	8	return $values;
40		}
41
42		/**
43		* @return string
44		*/
45	14	public function escFieldsForInsert(array $fields)
46		{
47	14	return sprintf('(`%s`)', implode('`,`', $fields));
48		}
49
50		/**
51		* @return string
52		*/
53	22	public function escValuesForInsert(array $values)
54		{
55	22	$values = array_values(array_map('esc_sql', $values));
56	22	return sprintf("('%s')", implode("','", $values));
57		}
58
59		/**
60		* @param string $statement
61		* @return string
62		*/
63	22	public function sql($statement)
64		{
65	22	$handle = $this->sqlHandle();
66	22	$statement = glsr()->filterString('database/sql/'.$handle, $statement);
67	22	glsr()->action('database/sql/'.$handle, $statement);
68	22	glsr()->action('database/sql', $statement, $handle);
69	22	return $statement;
70		}
71
72		/**
73		* @return string
74		*/
75	7	public function sqlJoin()
76		{
77	7	$join = $this->clauses('join');
78	7	$join = glsr()->filterArrayUnique('query/sql/join', $join, $this->sqlHandle(), $this);
79	7	return implode(' ', $join);
80		}
81
82		/**
83		* @return string
84		*/
85	1	public function sqlLimit()
86		{
87	1	$limit = Helper::ifTrue($this->args['per_page'] > 0,
88	1	$this->db->prepare('LIMIT %d', $this->args['per_page'])
89		);
90	1	return glsr()->filterString('query/sql/limit', $limit, $this->sqlHandle(), $this);
91		}
92
93		/**
94		* @return string
95		*/
96	1	public function sqlOffset()
97		{
98	1	$offsetBy = (($this->args['page'] - 1) * $this->args['per_page']) + $this->args['offset'];
99	1	$offset = Helper::ifTrue($offsetBy > 0,
100	1	$this->db->prepare('OFFSET %d', $offsetBy)
101		);
102	1	return glsr()->filterString('query/sql/offset', $offset, $this->sqlHandle(), $this);
103		}
104
105		/**
106		* @return string\|void
107		*/
108	1	public function sqlOrderBy()
109		{
110		$values = [
111	1	'random' => 'RAND()',
112		];
113	1	$order = $this->args['order'];
114	1	$orderby = $this->args['orderby'];
115	1	$orderedby = [];
116	1	if (Str::startsWith(['p.', 'r.'], $orderby)) {
117	1	$orderedby[] = "r.is_pinned {$order}";
118	1	$orderedby[] = "{$orderby} {$order}";
119		} elseif (array_key_exists($orderby, $values)) {
120		$orderedby[] = $values[$orderby];
121		}
122	1	$orderedby = glsr()->filterArrayUnique('query/sql/order-by', $orderedby, $this->sqlHandle(), $this);
123	1	if (!empty($orderedby)) {
124	1	return 'ORDER BY '.implode(', ', $orderedby);
125		}
126		}
127
128		/**
129		* @return string
130		*/
131	7	public function sqlWhere()
132		{
133	7	$and = $this->clauses('and');
134	7	$and = glsr()->filterArrayUnique('query/sql/and', $and, $this->sqlHandle(), $this);
135	7	$and = $this->normalizeAndClauses($and);
136	7	return 'WHERE 1=1 '.implode(' ', $and);
137		}
138
139		/**
140		* @return string
141		*/
142	23	public function table($table)
143		{
144	23	return glsr(SqlSchema::class)->table($table);
145		}
146
147		/**
148		* @return string
149		*/
150	2	protected function clauseAndAssignedPosts()
151		{
152	2	return $this->clauseIfValueNotEmpty('(apt.post_id IN (%s) AND apt.is_published = 1)', $this->args['assigned_posts']);
153		}
154
155		/**
156		* @return string
157		*/
158	2	protected function clauseAndAssignedTerms()
159		{
160	2	return $this->clauseIfValueNotEmpty('(att.term_id IN (%s))', $this->args['assigned_terms']);
161		}
162
163		/**
164		* @return string
165		*/
166	2	protected function clauseAndAssignedUsers()
167		{
168	2	return $this->clauseIfValueNotEmpty('(aut.user_id IN (%s))', $this->args['assigned_users']);
169		}
170
171		/**
172		* @return string
173		*/
174	7	protected function clauseAndDate()
175		{
176	7	$clauses = [];
177	7	$date = $this->args['date'];
178	7	if (!empty($date['after'])) {
179		$clauses[] = $this->db->prepare("(p.post_date >{$date['inclusive']} %s)", $date['after']);
180		}
181	7	if (!empty($date['before'])) {
182		$clauses[] = $this->db->prepare("(p.post_date <{$date['inclusive']} %s)", $date['before']);
183		}
184	7	if (!empty($date['year'])) {
185		$clauses[] = $this->db->prepare('(YEAR(p.post_date) = %d AND MONTH(p.post_date) = %d AND DAYOFMONTH(p.post_date) = %d)',
186		$date['year'], $date['month'], $date['day']
187		);
188		}
189	7	if ($clauses = implode(' AND ', $clauses)) {
190		return sprintf('AND (%s)', $clauses);
191		}
192	7	return '';
193		}
194
195		/**
196		* @return string
197		*/
198	1	protected function clauseAndEmail()
199		{
200	1	return $this->clauseIfValueNotEmpty('AND r.email = %s', $this->args['email']);
201		}
202
203		/**
204		* @return string
205		*/
206	1	protected function clauseAndIpAddress()
207		{
208	1	return $this->clauseIfValueNotEmpty('AND r.ip_address = %s', $this->args['ip_address']);
209		}
210
211		/**
212		* @return string
213		*/
214		protected function clauseAndPostIn()
215		{
216		return $this->clauseIfValueNotEmpty('AND r.review_id IN (%s)', $this->args['post__in']);
217		}
218
219		/**
220		* @return string
221		*/
222		protected function clauseAndPostNotIn()
223		{
224		return $this->clauseIfValueNotEmpty('AND r.review_id NOT IN (%s)', $this->args['post__not_in']);
225		}
226
227		/**
228		* @return string
229		*/
230	7	protected function clauseAndRating()
231		{
232	7	return Helper::ifTrue($this->args['rating'] > 0,
233	7	$this->db->prepare('AND r.rating > %d', --$this->args['rating'])
234		);
235		}
236
237		/**
238		* @return string
239		*/
240	20	protected function clauseAndStatus()
241		{
242	20	return $this->clauseIfValueNotEmpty('AND r.is_approved = %d', $this->args['status']);
243		}
244
245		/**
246		* @return string
247		*/
248		protected function clauseAndTerms()
249		{
250		if (Helper::isEmpty($this->args['terms'])) {
251		return '';
252		}
253		$value = Cast::toInt(Cast::toBool($this->args['terms']));
254		return $this->clauseIfValueNotEmpty('AND r.terms = %d', $value);
255		}
256
257		/**
258		* @return string
259		*/
260	14	protected function clauseAndType()
261		{
262	14	return $this->clauseIfValueNotEmpty('AND r.type = %s', $this->args['type']);
263		}
264
265		/**
266		* @return string
267		*/
268	1	protected function clauseAndUserIn()
269		{
270	1	return $this->clauseIfValueNotEmpty('AND p.post_author IN (%s)', $this->args['user__in']);
271		}
272
273		/**
274		* @return string
275		*/
276		protected function clauseAndUserNotIn()
277		{
278		return $this->clauseIfValueNotEmpty('AND p.post_author NOT IN (%s)', $this->args['user__not_in']);
279		}
280
281		/**
282		* @param string $clause
283		* @param array\|int\|string $value
284		* @param bool $prepare
285		* @return string
286		*/
287	21	protected function clauseIfValueNotEmpty($clause, $value, $prepare = true)
288		{
289	21	if (Helper::isEmpty($value)) {
290	21	return '';
291		}
292	21	if (!$prepare) {
293	8	return $clause;
294		}
295	20	if (is_array($value)) {
296	7	$value = implode(',', $value);
297		}
298	20	return $this->db->prepare($clause, $value);
299		}
300
301		/**
302		* @return string
303		*/
304	3	protected function clauseJoinAssignedPosts()
305		{
306	3	return $this->clauseIfValueNotEmpty(
307	3	"INNER JOIN {$this->table('assigned_posts')} AS apt ON r.ID = apt.rating_id",
308	3	$this->args['assigned_posts'],
309	3	$prepare = false
310		);
311		}
312
313		/**
314		* @return string
315		*/
316	3	protected function clauseJoinAssignedTerms()
317		{
318	3	return $this->clauseIfValueNotEmpty(
319	3	"INNER JOIN {$this->table('assigned_terms')} AS att ON r.ID = att.rating_id",
320	3	$this->args['assigned_terms'],
321	3	$prepare = false
322		);
323		}
324
325		/**
326		* @return string
327		*/
328	3	protected function clauseJoinAssignedUsers()
329		{
330	3	return $this->clauseIfValueNotEmpty(
331	3	"INNER JOIN {$this->table('assigned_users')} AS aut ON r.ID = aut.rating_id",
332	3	$this->args['assigned_users'],
333	3	$prepare = false
334		);
335		}
336
337		/**
338		* @return string
339		*/
340	8	protected function clauseJoinDate()
341		{
342	8	return $this->clauseIfValueNotEmpty(
343	8	"INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID",
344	8	array_filter($this->args['date']),
345	8	$prepare = false
346		);
347		}
348
349		/**
350		* @return string
351		*/
352	1	protected function clauseJoinUserIn()
353		{
354	1	return $this->clauseIfValueNotEmpty(
355	1	"INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID",
356	1	$this->args['user__in'],
357	1	$prepare = false
358		);
359		}
360
361		/**
362		* @return string
363		*/
364		protected function clauseJoinUserNotIn()
365		{
366		return $this->clauseIfValueNotEmpty(
367		"INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID",
368		$this->args['user__not_in'],
369		$prepare = false
370		);
371		}
372
373		/**
374		* @return string
375		*/
376	2	protected function clauseJoinOrderBy()
377		{
378	2	return Helper::ifTrue(Str::startsWith('p.', $this->args['orderby']),
379	2	"INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID"
380		);
381		}
382
383		/**
384		* @return array
385		*/
386	7	protected function normalizeAndClauses(array $and)
387		{
388	7	$clauses = [];
389	7	foreach ($and as $key => $value) {
390	7	if (Str::startsWith('assigned_', $key)) {
391	6	$clauses[] = $value;
392	6	unset($and[$key]);
393		}
394		}
395	7	$operator = glsr()->filterString('query/sql/clause/operator', 'OR', $clauses, $this->args);
396	7	$operator = strtoupper($operator);
397	7	$operator = Helper::ifTrue(in_array($operator, ['AND', 'OR']), $operator, 'OR');
398	7	if ($clauses = implode(" {$operator} ", $clauses)) {
399	6	$and['assigned'] = "AND ($clauses)";
400		}
401	7	return $and;
402		}
403
404		/**
405		* @param int $depth
406		* @return string
407		*/
408	22	protected function sqlHandle($depth = 2)
409		{
410	22	return Str::dashCase(Arr::get((new \Exception())->getTrace(), $depth.'.function'));
411		}
412		}
413

pryley / site-reviews

Push — master ( b694dd...6c1d9a )

Sql::clauseIfValueNotEmpty() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like