CRUD_helpers::search_conditions_join_table() - Code Metrics - Inspection of "Simplify searches with `cs\CRUD_helpers` trait" - nazar-pc/CleverStyle-Framework - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( fa65af...9eb297 )

by Nazar

created 2016-04-07 00:05 UTC

CRUD_helpers::search_conditions_join_table() C

↳ Parent: CRUD_helpers

Complexity

Conditions	7
Paths	36

Size

Total Lines	37
Code Lines	24

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	1	Features	0

Metric	Value
c	2
b	1
f	0
dl	0
loc	37
rs	6.7272
cc	7
eloc	24
nc	36
nop	6

<?php
/**
 * @package   CleverStyle CMS
 * @author    Nazar Mokrynskyi <[email protected]>
 * @copyright Copyright (c) 2015-2016, Nazar Mokrynskyi
 * @license   MIT License, see license.txt
 */
namespace cs;
/**
 * CRUD trait extended with useful additional methods, for example, for searching
 *
 * Might me used instead of regular CRUD trait
 */
trait CRUD_helpers {
	use
		CRUD;
	/**
	 * Generic search
	 *
	 * @param mixed[] $search_parameters Array in form [attribute => value];<br>
	 *                                   Or [attribute => [value1, value2, value3]];<br>
	 *                                   Or [attribute => [from => a, to => b]];<br>
	 *                                   Or [attribute => [...]] in case of joined tables, where ... is any of three constructions mentioned above;<br>
	 *                                   if `total_count => true` element is present - total number of found rows will be returned instead of rows themselves
	 * @param int     $page
	 * @param int     $count
	 * @param string  $order_by
	 * @param bool    $asc
	 *
	 * @return false|int|int[]|string[] Array of `id` or number of elements

	 */
	protected function search ($search_parameters = [], $page = 1, $count = 100, $order_by = 'id', $asc = false) {
		if (!isset($this->data_model[$order_by])) {
			return false;
		}
		$joins        = '';
		$join_params  = [];
		$join_index   = 0;
		$where        = [];
		$where_params = [];
		foreach ($search_parameters as $key => $details) {
			if (!isset($this->data_model[$key])) {
				continue;
			}
			if (isset($this->data_model[$key]['data_model'])) {
				$this->search_conditions_join_table('t', $key, $details, $joins, $join_params, $join_index);
			} else {
				$this->search_conditions('t', $key, $details, $where, $where_params);
			}
		}
		return $this->search_do('t', @$search_parameters['total_count'], $where, $where_params, $joins, $join_params, $page, $count, $order_by, $asc);
	}
	/**
	 * @param string   $table_alias
	 * @param bool     $total_count
	 * @param string[] $where
	 * @param array    $where_params
	 * @param string   $joins
	 * @param array    $join_params
	 * @param int      $page
	 * @param int      $count
	 * @param string   $order_by
	 * @param bool     $asc
	 *
	 * @return false|int|int[]|string[]

	 */
	private function search_do ($table_alias, $total_count, $where, $where_params, $joins, $join_params, $page, $count, $order_by, $asc) {
		$first_column = array_keys($this->data_model)[0];
		$where        = $where ? 'WHERE '.implode(' AND ', $where) : '';
		if ($total_count) {
			return $this->db()->qfs(
				"SELECT COUNT(`$table_alias`.`$first_column`)
				FROM `$this->table` AS `$table_alias`
				$joins
				$where",
				array_merge($join_params, $where_params)
			);
		}
		$where_params[] = ($page - 1) * $count;
		$where_params[] = $count;
		$order_by       = $this->search_order_by($table_alias, $order_by, $joins, $join_index);
		$asc            = $asc ? 'ASC' : 'DESC';
		return $this->db()->qfas(
			"SELECT `$table_alias`.`$first_column`
			FROM `$this->table` AS `$table_alias`
			$joins
			$where
			ORDER BY $order_by $asc
			LIMIT %d, %d",
			array_merge($join_params, $where_params)
		);
	}
	/**
	 * @param string   $table_alias
	 * @param string   $key
	 * @param array    $details
	 * @param string[] $where
	 * @param array    $where_params
	 */
	private function search_conditions ($table_alias, $key, $details, &$where, &$where_params) {
		if (is_scalar($details)) {
			$where[]        = "`$table_alias`.`$key` = '%s'";
			$where_params[] = $details;
		} elseif (is_array($details) && $details) {
			if (is_array_indexed($details)) {
				$where_tmp = [];
				foreach ($details as $d) {
					$where_tmp[]    = "`$table_alias`.`$key` = '%s'";
					$where_params[] = $d;
				}
				$where[] = '('.implode(' OR ', $where_tmp).')';
				return;
			}
			if (isset($details['from'])) {
				$where[]        = "`$table_alias`.`$key` => '%s'";
				$where_params[] = $details['from'];
			}
			if (isset($details['to'])) {
				$where[]        = "`$table_alias`.`$key` <= '%s'";
				$where_params[] = $details['to'];
			}
		}
	}
	/**
	 * @param string           $table_alias
	 * @param string           $table
	 * @param array|int|string $details
	 * @param string           $joins
	 * @param array            $join_params
	 * @param int              $join_index
	 */
	private function search_conditions_join_table ($table_alias, $table, $details, &$joins, &$join_params, &$join_index) {
		$data_model        = $this->data_model[$table];
		$first_column      = array_keys($this->data_model)[0];
		$first_column_join = array_keys($data_model['data_model'])[0];
		if (is_scalar($details)) {
			$details = [
				array_keys($data_model['data_model'])[1] => $details
			];
		}
		/**
		 * @var array $details
		 */
		++$join_index;
		$joins .=
			"INNER JOIN `{$this->table}_$table` AS `j$join_index`
			ON
				`$table_alias`.`$first_column` = `j$join_index`.`$first_column_join`";
		$language_field = isset($data_model['language_field']) ? $data_model['language_field'] : false;
		foreach ($details as $field => $value) {
			if ($language_field === $field) {
				continue;
			}
			$where_tmp = [];
			$this->search_conditions("j$join_index", $field, $value, $where_tmp, $join_params);
			$joins .= " AND ".implode(" AND ", $where_tmp);
		}
		if ($language_field) {
			/** @noinspection OffsetOperationsInspection */
			$clang = isset($details[$language_field]) ? $details[$language_field] : Language::instance()->clang;
			$joins .=
				" AND
				(
					`j$join_index`.`lang`	= '$clang' OR
					`j$join_index`.`lang`	= ''
				)";
		}
	}
	/**
	 * @param string $table_alias
	 * @param string $order_by
	 * @param string $joins
	 * @param int    $join_index
	 *
	 * @return string
	 */
	private function search_order_by ($table_alias, $order_by, &$joins, &$join_index) {
		$order_by = explode(':', $order_by);
		if (!isset($this->data_model[$order_by[0]])) {
			/**
			 * Non-existing field
			 */
			$order_by = ['id'];
		} elseif (isset($order_by[1])) {
			/**
			 * Non-existing field in joined table
			 */
			if (!isset($this->data_model[$order_by[0]]['data_model'][$order_by[1]])) {
				$order_by = ['id'];
			}
		} elseif (isset($this->data_model[$order_by[0]]['data_model'])) {
			/**
			 * Default field in joined table
			 */
			$order_by[1] = array_keys($this->data_model[$order_by[0]]['data_model'])[1];
		}
		if (isset($order_by[1])) {
			++$join_index;
			$first_column      = array_keys($this->data_model)[0];
			$first_column_join = array_keys($this->data_model[$order_by[0]]['data_model'])[0];
			$joins .=
				"INNER JOIN `{$this->table}_$order_by[0]` AS `j$join_index`
				ON
					`$table_alias`.`$first_column`	= `j$join_index`.`$first_column_join`";
			$order_by = "`j$join_index`.`$order_by[1]`";
		} else {
			$order_by = "`$table_alias`.`$order_by[0]`";
		}
		return $order_by;
	}
}


1			<?php
2			/**
3			* @package CleverStyle CMS
4			* @author Nazar Mokrynskyi <[email protected]>
5			* @copyright Copyright (c) 2015-2016, Nazar Mokrynskyi
6			* @license MIT License, see license.txt
7			*/
8			namespace cs;
9			/**
10			* CRUD trait extended with useful additional methods, for example, for searching
11			*
12			* Might me used instead of regular CRUD trait
13			*/
14			trait CRUD_helpers {
15			use
16			CRUD;
17			/**
18			* Generic search
19			*
20			* @param mixed[] $search_parameters Array in form [attribute => value];<br>
21			* Or [attribute => [value1, value2, value3]];<br>
22			* Or [attribute => [from => a, to => b]];<br>
23			* Or [attribute => [...]] in case of joined tables, where ... is any of three constructions mentioned above;<br>
24			* if `total_count => true` element is present - total number of found rows will be returned instead of rows themselves
25			* @param int $page
26			* @param int $count
27			* @param string $order_by
28			* @param bool $asc
29			*
30			* @return false\|int\|int[]\|string[] Array of `id` or number of elements
			0 ignored issues – show Documentation introduced 2015-11-21 02:31 UTC by Report Bug Copy Issue Report Should the return type not be `false\|array[]\|integer\|integer[]\|string\|string[]`? This check compares the return type specified in the `@return` annotation of a function or method doc comment with the types returned by the function and raises an issue if they mismatch. Loading history...
31			*/
32			protected function search ($search_parameters = [], $page = 1, $count = 100, $order_by = 'id', $asc = false) {
33			if (!isset($this->data_model[$order_by])) {
34			return false;
35			}
36			$joins = '';
37			$join_params = [];
38			$join_index = 0;
39			$where = [];
40			$where_params = [];
41			foreach ($search_parameters as $key => $details) {
42			if (!isset($this->data_model[$key])) {
43			continue;
44			}
45			if (isset($this->data_model[$key]['data_model'])) {
46			$this->search_conditions_join_table('t', $key, $details, $joins, $join_params, $join_index);
47			} else {
48			$this->search_conditions('t', $key, $details, $where, $where_params);
49			}
50			}
51			return $this->search_do('t', @$search_parameters['total_count'], $where, $where_params, $joins, $join_params, $page, $count, $order_by, $asc);
52			}
53			/**
54			* @param string $table_alias
55			* @param bool $total_count
56			* @param string[] $where
57			* @param array $where_params
58			* @param string $joins
59			* @param array $join_params
60			* @param int $page
61			* @param int $count
62			* @param string $order_by
63			* @param bool $asc
64			*
65			* @return false\|int\|int[]\|string[]
			0 ignored issues – show Documentation introduced 2016-04-06 16:10 UTC by Report Bug Copy Issue Report Should the return type not be `array[]\|false\|integer\|integer[]\|string\|string[]`? This check compares the return type specified in the `@return` annotation of a function or method doc comment with the types returned by the function and raises an issue if they mismatch. Loading history...
66			*/
67			private function search_do ($table_alias, $total_count, $where, $where_params, $joins, $join_params, $page, $count, $order_by, $asc) {
68			$first_column = array_keys($this->data_model)[0];
69			$where = $where ? 'WHERE '.implode(' AND ', $where) : '';
70			if ($total_count) {
71			return $this->db()->qfs(
72			"SELECT COUNT(`$table_alias`.`$first_column`)
73			FROM `$this->table` AS `$table_alias`
74			$joins
75			$where",
76			array_merge($join_params, $where_params)
77			);
78			}
79			$where_params[] = ($page - 1) * $count;
80			$where_params[] = $count;
81			$order_by = $this->search_order_by($table_alias, $order_by, $joins, $join_index);
82			$asc = $asc ? 'ASC' : 'DESC';
83			return $this->db()->qfas(
84			"SELECT `$table_alias`.`$first_column`
85			FROM `$this->table` AS `$table_alias`
86			$joins
87			$where
88			ORDER BY $order_by $asc
89			LIMIT %d, %d",
90			array_merge($join_params, $where_params)
91			);
92			}
93			/**
94			* @param string $table_alias
95			* @param string $key
96			* @param array $details
97			* @param string[] $where
98			* @param array $where_params
99			*/
100			private function search_conditions ($table_alias, $key, $details, &$where, &$where_params) {
101			if (is_scalar($details)) {
102			$where[] = "`$table_alias`.`$key` = '%s'";
103			$where_params[] = $details;
104			} elseif (is_array($details) && $details) {
105			if (is_array_indexed($details)) {
106			$where_tmp = [];
107			foreach ($details as $d) {
108			$where_tmp[] = "`$table_alias`.`$key` = '%s'";
109			$where_params[] = $d;
110			}
111			$where[] = '('.implode(' OR ', $where_tmp).')';
112			return;
113			}
114			if (isset($details['from'])) {
115			$where[] = "`$table_alias`.`$key` => '%s'";
116			$where_params[] = $details['from'];
117			}
118			if (isset($details['to'])) {
119			$where[] = "`$table_alias`.`$key` <= '%s'";
120			$where_params[] = $details['to'];
121			}
122			}
123			}
124			/**
125			* @param string $table_alias
126			* @param string $table
127			* @param array\|int\|string $details
128			* @param string $joins
129			* @param array $join_params
130			* @param int $join_index
131			*/
132			private function search_conditions_join_table ($table_alias, $table, $details, &$joins, &$join_params, &$join_index) {
133			$data_model = $this->data_model[$table];
134			$first_column = array_keys($this->data_model)[0];
135			$first_column_join = array_keys($data_model['data_model'])[0];
136			if (is_scalar($details)) {
137			$details = [
138			array_keys($data_model['data_model'])[1] => $details
139			];
140			}
141			/**
142			* @var array $details
143			*/
144			++$join_index;
145			$joins .=
146			"INNER JOIN `{$this->table}_$table` AS `j$join_index`
147			ON
148			`$table_alias`.`$first_column` = `j$join_index`.`$first_column_join`";
149			$language_field = isset($data_model['language_field']) ? $data_model['language_field'] : false;
150			foreach ($details as $field => $value) {
151			if ($language_field === $field) {
152			continue;
153			}
154			$where_tmp = [];
155			$this->search_conditions("j$join_index", $field, $value, $where_tmp, $join_params);
156			$joins .= " AND ".implode(" AND ", $where_tmp);
157			}
158			if ($language_field) {
159			/** @noinspection OffsetOperationsInspection */
160			$clang = isset($details[$language_field]) ? $details[$language_field] : Language::instance()->clang;
161			$joins .=
162			" AND
163			(
164			`j$join_index`.`lang` = '$clang' OR
165			`j$join_index`.`lang` = ''
166			)";
167			}
168			}
169			/**
170			* @param string $table_alias
171			* @param string $order_by
172			* @param string $joins
173			* @param int $join_index
174			*
175			* @return string
176			*/
177			private function search_order_by ($table_alias, $order_by, &$joins, &$join_index) {
178			$order_by = explode(':', $order_by);
179			if (!isset($this->data_model[$order_by[0]])) {
180			/**
181			* Non-existing field
182			*/
183			$order_by = ['id'];
184			} elseif (isset($order_by[1])) {
185			/**
186			* Non-existing field in joined table
187			*/
188			if (!isset($this->data_model[$order_by[0]]['data_model'][$order_by[1]])) {
189			$order_by = ['id'];
190			}
191			} elseif (isset($this->data_model[$order_by[0]]['data_model'])) {
192			/**
193			* Default field in joined table
194			*/
195			$order_by[1] = array_keys($this->data_model[$order_by[0]]['data_model'])[1];
196			}
197			if (isset($order_by[1])) {
198			++$join_index;
199			$first_column = array_keys($this->data_model)[0];
200			$first_column_join = array_keys($this->data_model[$order_by[0]]['data_model'])[0];
201			$joins .=
202			"INNER JOIN `{$this->table}_$order_by[0]` AS `j$join_index`
203			ON
204			`$table_alias`.`$first_column` = `j$join_index`.`$first_column_join`";
205			$order_by = "`j$join_index`.`$order_by[1]`";
206			} else {
207			$order_by = "`$table_alias`.`$order_by[0]`";
208			}
209			return $order_by;
210			}
211			}
212

nazar-pc / CleverStyle-Framework

Push — master ( fa65af...9eb297 )

CRUD_helpers::search_conditions_join_table() C

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like