Sql::clauses() - Code Metrics - Inspection of "c9219833593c6f3da8b417659afa0b3149da21a6" - pryley/site-reviews - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — master (c92198)

by Paul

created 2021-01-26 23:25 UTC

Sql::clauses() A

↳ Parent: Sql

Complexity

Conditions	3
Paths	3

Size

Total Lines	10
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	7
CRAP Score	3

Importance

Changes

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

<?php

namespace GeminiLabs\SiteReviews\Database;

use GeminiLabs\SiteReviews\Helper;
use GeminiLabs\SiteReviews\Helpers\Arr;
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 (array_keys($this->args) as $key) {
            $method = Helper::buildMethodName($key, 'clause-'.$prefix);
            if (method_exists($this, $method)) {
                $values[$key] = call_user_func([$this, $method]);
            }
        }
        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);
        return 'WHERE 1=1 '.implode(' ', $and);
    }

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

    /**
     * @return string
     */
    protected function clauseAndAssignedPosts()
    {
        $clauses = [];
        if ($postIds = $this->args['assigned_posts']) {
            $clauses[] = sprintf('(apt.post_id IN (%s) AND apt.is_published = 1)', implode(',', $postIds));
        }
        if ($termIds = $this->args['assigned_terms']) {
            $clauses[] = sprintf('(att.term_id IN (%s))', implode(',', $termIds));
        }
        if ($userIds = $this->args['assigned_users']) {
            $clauses[] = sprintf('(aut.user_id IN (%s))', implode(',', $userIds));
        }
        $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)) {
            return "AND ($clauses)";
        }
        return '';
    }

    /**
     * @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 Helper::ifTrue(!empty($this->args['email']),
            $this->db->prepare('AND r.email = %s', $this->args['email'])
        );
    }

    /**
     * @return string
     */
    protected function clauseAndIpAddress()
    {
        return Helper::ifTrue(!empty($this->args['ip_address']),
            $this->db->prepare('AND r.ip_address = %s', $this->args['ip_address'])
        );
    }

    /**
     * @return string
     */
    protected function clauseAndPostIn()
    {
        return Helper::ifTrue(!empty($this->args['post__in']),
            $this->db->prepare('AND r.review_id IN (%s)', implode(',', $this->args['post__in']))
        );
    }

    /**
     * @return string
     */
    protected function clauseAndPostNotIn()
    {
        return Helper::ifTrue(!empty($this->args['post__not_in']),
            $this->db->prepare('AND r.review_id NOT IN (%s)', implode(',', $this->args['post__not_in']))
        );
    }

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

    /**
     * @return string
     */
    protected function clauseAndStatus()
    {
        return Helper::ifTrue(!Helper::isEmpty($this->args['status']),
            $this->db->prepare('AND r.is_approved = %d', $this->args['status'])
        );
    }

    /**
     * @return string
     */
    protected function clauseAndType()
    {
        return Helper::ifTrue(!empty($this->args['type']),
            $this->db->prepare('AND r.type = %s', $this->args['type'])
        );
    }

    /**
     * @return string
     */
    protected function clauseAndUserIn()
    {
        return Helper::ifTrue(!empty($this->args['user__in']),
            $this->db->prepare('AND p.post_author IN (%s)', implode(',', $this->args['user__in']))
        );
    }

    /**
     * @return string
     */
    protected function clauseAndUserNotIn()
    {
        return Helper::ifTrue(!empty($this->args['user__not_in']),
            $this->db->prepare('AND p.post_author NOT IN (%s)', implode(',', $this->args['user__not_in']))
        );
    }

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

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

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

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

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

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

    /**
     * @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"
        );
    }

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

pryley / site-reviews

Branch — master (c92198)

Sql::clauses() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like