QuerySql::sqlSelect() - 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

QuerySql::sqlSelect() A

↳ Parent: QuerySql

Complexity

Conditions	1
Paths	1

Size

Total Lines	16
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes	1
Bugs	0	Features	0

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

<?php

namespace GeminiLabs\SiteReviews\Database;

use GeminiLabs\SiteReviews\Helper;
use GeminiLabs\SiteReviews\Helpers\Str;

trait QuerySql
{
    public $args;
    public $db;

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

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

    /**
     * @param string $clause
     * @return array
     */
    public function sqlClauses(array $values, $clause)
    {
        $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[] = call_user_func([$this, $method]);
            }
        }
        return $values;
    }

    /**
     * @return string
     */
    public function sqlFrom()
    {
        $from = "FROM {$this->table('ratings')} r";
        $from = glsr()->filterString('query/sql/from', $from, $this);
        return $from;
    }

    /**
     * @return string
     */
    public function sqlGroupBy()
    {
        $groupBy = 'GROUP BY p.ID';
        return glsr()->filterString('query/sql/group-by', $groupBy, $this);
    }

    /**
     * @return string
     */
    public function sqlJoin()
    {
        $join = [
            "INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID",
        ];
        $join = glsr()->filterArray('query/sql/join', $join, $this);
        return implode(' ', $join);
    }

    /**
     * @return string
     */
    public function sqlJoinClauses()
    {
        $join = $this->sqlClauses([], 'join');
        $join = glsr()->filterArray('query/sql/join-clauses', $join, $this);
        return trim($this->sqlJoin().' '.implode(' ', $join));
    }

    /**
     * @return string
     */
    public function sqlJoinPivots()
    {
        $join = [
            "LEFT JOIN {$this->table('assigned_posts')} apt on r.ID = apt.rating_id",
            "LEFT JOIN {$this->table('assigned_terms')} att on r.ID = att.rating_id",
            "LEFT JOIN {$this->table('assigned_users')} aut on r.ID = aut.rating_id",
        ];
        $join = glsr()->filterArray('query/sql/join-pivots', $join, $this);
        return implode(' ', $join);
    }

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

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

    /**
     * @return string
     */
    public function sqlOrderBy()
    {
        $values = [
            'none' => '',
            'rand' => 'ORDER BY RAND()',
            'relevance' => '',
        ];
        $order = $this->args['order'];
        $orderby = $this->args['orderby'];
        if (Str::startsWith('p.', $orderby)) {
            $orderBy = "ORDER BY r.is_pinned {$order}, {$orderby} {$order}";
        } elseif (array_key_exists($orderby, $values)) {
            $orderBy = $orderby;
        } else {
            $orderBy = '';
        }
        return glsr()->filterString('query/sql/order-by', $orderBy, $this);
    }

    /**
     * @return string
     */
    public function sqlSelect()
    {
        $select = [
            'r.*',
            'p.post_author as author_id',
            'p.post_date as date',
            'p.post_content as content',
            'p.post_title as title',
            'p.post_status as status',
            'GROUP_CONCAT(DISTINCT apt.post_id) as post_ids',
            'GROUP_CONCAT(DISTINCT att.term_id) as term_ids',
            'GROUP_CONCAT(DISTINCT aut.user_id) as user_ids',
        ];
        $select = glsr()->filterArray('query/sql/select', $select, $this);
        $select = implode(', ', $select);
        return "SELECT {$select}";
    }

    /**
     * @return string
     */
    public function sqlWhere()
    {
        $where = [
            $this->db->prepare('AND p.post_type = %s', glsr()->post_type),
            "AND p.post_status = 'publish'",
        ];
        $where = $this->sqlClauses($where, 'and');
        $where = glsr()->filterArray('query/sql/where', $where, $this);
        $where = implode(' ', $where);
        return "WHERE 1=1 {$where}";
    }

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

    /**
     * This takes care of assigned_to, category, and user.
     * @return string
     */
    protected function clauseAndAssignedTo()
    {
        $clauses = [];
        if ($postIds = $this->args['assigned_to']) {
            $clauses[] = $this->db->prepare('(apt.post_id IN (%s) AND apt.is_published = 1)', implode(',', $postIds));
        }
        if ($termIds = $this->args['category']) {
            $clauses[] = $this->db->prepare('(att.term_id IN (%s))', implode(',', $termIds));
        }
        if ($userIds = $this->args['user']) {
            $clauses[] = $this->db->prepare('(aut.user_id IN (%s))', implode(',', $userIds));
        }
        if ($clauses = implode(' OR ', $clauses)) {
            return "AND ($clauses)";
        }
        return '';
    }

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

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

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

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

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


1			<?php
2
3			namespace GeminiLabs\SiteReviews\Database;
4
5			use GeminiLabs\SiteReviews\Helper;
6			use GeminiLabs\SiteReviews\Helpers\Str;
7
8			trait QuerySql
9			{
10			public $args;
11			public $db;
12
13			public function escFieldsForInsert(array $fields)
14			{
15			return sprintf('(`%s`)', implode('`,`', $fields));
16			}
17
18			public function escValuesForInsert(array $values)
19			{
20			$values = array_map('esc_sql', $values);
21			return sprintf("('%s')", implode("','", array_values($values)));
22			}
23
24			/**
25			* @param string $clause
26			* @return array
27			*/
28			public function sqlClauses(array $values, $clause)
29			{
30			$prefix = Str::restrictTo('and, join', $clause);
31			foreach (array_keys($this->args) as $key) {
32			$method = Helper::buildMethodName($key, 'clause-'.$prefix);
33			if (method_exists($this, $method)) {
34			$values[] = call_user_func([$this, $method]);
35			}
36			}
37			return $values;
38			}
39
40			/**
41			* @return string
42			*/
43			public function sqlFrom()
44			{
45			$from = "FROM {$this->table('ratings')} r";
46			$from = glsr()->filterString('query/sql/from', $from, $this);
47			return $from;
48			}
49
50			/**
51			* @return string
52			*/
53			public function sqlGroupBy()
54			{
55			$groupBy = 'GROUP BY p.ID';
56			return glsr()->filterString('query/sql/group-by', $groupBy, $this);
57			}
58
59			/**
60			* @return string
61			*/
62			public function sqlJoin()
63			{
64			$join = [
65			"INNER JOIN {$this->db->posts} AS p ON r.review_id = p.ID",
66			];
67			$join = glsr()->filterArray('query/sql/join', $join, $this);
68			return implode(' ', $join);
69			}
70
71			/**
72			* @return string
73			*/
74			public function sqlJoinClauses()
75			{
76			$join = $this->sqlClauses([], 'join');
77			$join = glsr()->filterArray('query/sql/join-clauses', $join, $this);
78			return trim($this->sqlJoin().' '.implode(' ', $join));
79			}
80
81			/**
82			* @return string
83			*/
84			public function sqlJoinPivots()
85			{
86			$join = [
87			"LEFT JOIN {$this->table('assigned_posts')} apt on r.ID = apt.rating_id",
88			"LEFT JOIN {$this->table('assigned_terms')} att on r.ID = att.rating_id",
89			"LEFT JOIN {$this->table('assigned_users')} aut on r.ID = aut.rating_id",
90			];
91			$join = glsr()->filterArray('query/sql/join-pivots', $join, $this);
92			return implode(' ', $join);
93			}
94
95			/**
96			* @return string
97			*/
98			public function sqlLimit()
99			{
100			$limit = $this->args['per_page'] > 0
101			? $this->db->prepare('LIMIT %d', $this->args['per_page'])
102			: '';
103			return glsr()->filterString('query/sql/limit', $limit, $this);
104			}
105
106			/**
107			* @return string
108			*/
109			public function sqlOffset()
110			{
111			$offsetBy = (($this->args['page'] - 1) * $this->args['per_page']) + $this->args['offset'];
112			$offset = ($offsetBy > 0)
113			? $this->db->prepare('OFFSET %d', $offsetBy)
114			: '';
115			return glsr()->filterString('query/sql/offset', $offset, $this);
116			}
117
118			/**
119			* @return string
120			*/
121			public function sqlOrderBy()
122			{
123			$values = [
124			'none' => '',
125			'rand' => 'ORDER BY RAND()',
126			'relevance' => '',
127			];
128			$order = $this->args['order'];
129			$orderby = $this->args['orderby'];
130			if (Str::startsWith('p.', $orderby)) {
131			$orderBy = "ORDER BY r.is_pinned {$order}, {$orderby} {$order}";
132			} elseif (array_key_exists($orderby, $values)) {
133			$orderBy = $orderby;
134			} else {
135			$orderBy = '';
136			}
137			return glsr()->filterString('query/sql/order-by', $orderBy, $this);
138			}
139
140			/**
141			* @return string
142			*/
143			public function sqlSelect()
144			{
145			$select = [
146			'r.*',
147			'p.post_author as author_id',
148			'p.post_date as date',
149			'p.post_content as content',
150			'p.post_title as title',
151			'p.post_status as status',
152			'GROUP_CONCAT(DISTINCT apt.post_id) as post_ids',
153			'GROUP_CONCAT(DISTINCT att.term_id) as term_ids',
154			'GROUP_CONCAT(DISTINCT aut.user_id) as user_ids',
155			];
156			$select = glsr()->filterArray('query/sql/select', $select, $this);
157			$select = implode(', ', $select);
158			return "SELECT {$select}";
159			}
160
161			/**
162			* @return string
163			*/
164			public function sqlWhere()
165			{
166			$where = [
167			$this->db->prepare('AND p.post_type = %s', glsr()->post_type),
168			"AND p.post_status = 'publish'",
169			];
170			$where = $this->sqlClauses($where, 'and');
171			$where = glsr()->filterArray('query/sql/where', $where, $this);
172			$where = implode(' ', $where);
173			return "WHERE 1=1 {$where}";
174			}
175
176			/**
177			* @return string
178			*/
179			public function table($table)
180			{
181			return glsr(SqlSchema::class)->table($table);
182			}
183
184			/**
185			* This takes care of assigned_to, category, and user.
186			* @return string
187			*/
188			protected function clauseAndAssignedTo()
189			{
190			$clauses = [];
191			if ($postIds = $this->args['assigned_to']) {
192			$clauses[] = $this->db->prepare('(apt.post_id IN (%s) AND apt.is_published = 1)', implode(',', $postIds));
193			}
194			if ($termIds = $this->args['category']) {
195			$clauses[] = $this->db->prepare('(att.term_id IN (%s))', implode(',', $termIds));
196			}
197			if ($userIds = $this->args['user']) {
198			$clauses[] = $this->db->prepare('(aut.user_id IN (%s))', implode(',', $userIds));
199			}
200			if ($clauses = implode(' OR ', $clauses)) {
201			return "AND ($clauses)";
202			}
203			return '';
204			}
205
206			/**
207			* @return string
208			*/
209			protected function clauseAndRating()
210			{
211			return $this->args['rating']
212			? $this->db->prepare('AND r.rating > %d', --$this->args['rating'])
213			: '';
214			}
215
216			/**
217			* @return string
218			*/
219			protected function clauseAndType()
220			{
221			return $this->args['type']
222			? $this->db->prepare('AND r.type = %s', $this->args['type'])
223			: '';
224			}
225
226			/**
227			* @return string
228			*/
229			protected function clauseJoinAssignedTo()
230			{
231			return !empty($this->args['assigned_to'])
232			? "INNER JOIN {$this->table('assigned_posts')} AS apt ON r.ID = apt.rating_id"
233			: '';
234			}
235
236			/**
237			* @return string
238			*/
239			protected function clauseJoinCategory()
240			{
241			return !empty($this->args['category'])
242			? "INNER JOIN {$this->table('assigned_terms')} AS att ON r.ID = att.rating_id"
243			: '';
244			}
245
246			/**
247			* @return string
248			*/
249			protected function clauseJoinUser()
250			{
251			return !empty($this->args['user'])
252			? "INNER JOIN {$this->table('assigned_users')} AS aut ON r.ID = aut.rating_id"
253			: '';
254			}
255			}
256

pryley / site-reviews

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

QuerySql::sqlSelect() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like