ideas::livesearch() - Code Metrics - Inspection of "WIP Experiment" - phpbb/ideas - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#131)

by Matt

created 2020-06-03 07:46 UTC

ideas::livesearch() A

↳ Parent: ideas

Complexity

Conditions	2
Paths	2

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	20
rs	9.6
c	0
b	0
f	0
cc	2
nc	2
nop	2

<?php
/**
 *
 * Ideas extension for the phpBB Forum Software package.
 *
 * @copyright (c) phpBB Limited <https://www.phpbb.com>
 * @license GNU General Public License, version 2 (GPL-2.0)
 *
 */

namespace phpbb\ideas\factory;

use phpbb\exception\runtime_exception;

/**
 * Class for handling multiple ideas
 */
class ideas extends base
{
	/** @var int */
	protected $idea_count;

	/** @var array */
	protected $sql;

	/**
	 * Returns an array of ideas. Defaults to ten ideas ordered by date
	 * excluding implemented, duplicate or invalid ideas.
	 *
	 * @param int       $number    The number of ideas to return
	 * @param string    $sort      A sorting option/collection
	 * @param string    $direction Should either be ASC or DESC
	 * @param array|int $status    The id of the status(es) to load
	 * @param int       $start     Start value for pagination
	 *
	 * @return array Array of row data
	 */
	public function get($number = 10, $sort = 'date', $direction = 'DESC', $status = [], $start = 0)
	{
		// Initialize a query to request ideas
		$sql = $this->query_ideas()
			->query_sort($sort, $direction)
			->query_status($status);

		// For pagination, get a count of the total ideas being requested
		if ($number >= $this->config['posts_per_page'])
		{
			$this->idea_count = $sql->query_count();
		}

		$ideas = $sql->query_get($number, $start);

		if (count($ideas))
		{
			$topic_ids = array_column($ideas, 'topic_id');
			$idea_ids = array_column($ideas, 'idea_id');

			$topic_tracking_info = get_complete_topic_tracking((int) $this->config['ideas_forum_id'], $topic_ids);
			$user_voting_info = $this->get_users_votes($this->user->data['user_id'], $idea_ids);

			foreach ($ideas as &$idea)
			{
				$idea['read'] = !(isset($topic_tracking_info[$idea['topic_id']]) && $idea['topic_last_post_time'] > $topic_tracking_info[$idea['topic_id']]);
				$idea['u_voted'] = isset($user_voting_info[$idea['idea_id']]) ? (int) $user_voting_info[$idea['idea_id']] : '';
			}
			unset ($idea);
		}

		return $ideas;
	}

	/**
	 * Initialize the $sql property with necessary SQL statements.
	 *
	 * @return \phpbb\ideas\factory\ideas $this For chaining calls
	 */
	protected function query_ideas()
	{
		$this->sql = [];

		$this->sql['SELECT'][] = 't.topic_last_post_time, t.topic_status, t.topic_visibility, i.*';
		$this->sql['FROM'] = "{$this->table_ideas} i";
		$this->sql['JOIN'] = "{$this->table_topics} t ON i.topic_id = t.topic_id";
		$this->sql['WHERE'][] = 't.forum_id = ' . (int) $this->config['ideas_forum_id'];

		// Only get approved topics for regular users, Moderators will see unapproved topics
		if (!$this->auth->acl_get('m_', $this->config['ideas_forum_id']))
		{
			$this->sql['WHERE'][] = 't.topic_visibility = ' . ITEM_APPROVED;
		}

		return $this;
	}

	/**
	 * Update the $sql property with ORDER BY statements to obtain
	 * the requested collection of Ideas. Some instances may add
	 * additional WHERE or SELECT statements to refine the collection.
	 *
	 * @param string $sort      A sorting option/collection
	 * @param string $direction Will either be ASC or DESC
	 *
	 * @return \phpbb\ideas\factory\ideas $this For chaining calls
	 */
	protected function query_sort($sort, $direction)
	{
		$sort = strtolower($sort);
		$direction = $direction === 'DESC' ? 'DESC' : 'ASC';

		// Most sorting relies on simple ORDER BY statements, but some may use a WHERE statement
		$statements = [
			self::SORT_DATE    => ['ORDER_BY' => 'i.idea_date'],
			self::SORT_TITLE   => ['ORDER_BY' => 'i.idea_title'],
			self::SORT_AUTHOR  => ['ORDER_BY' => 'i.idea_author'],
			self::SORT_SCORE   => ['ORDER_BY' => 'CAST(i.idea_votes_up AS decimal) - CAST(i.idea_votes_down AS decimal)'],
			self::SORT_VOTES   => ['ORDER_BY' => 'i.idea_votes_up + i.idea_votes_down'],
			self::SORT_TOP     => ['WHERE' => 'i.idea_votes_up > i.idea_votes_down'],
			self::SORT_MYIDEAS => ['ORDER_BY' => 'i.idea_date', 'WHERE' => 'i.idea_author = ' . (int) $this->user->data['user_id']],
		];

		// Append a new WHERE statement if the sort has one
		if (isset($statements[$sort]['WHERE']))
		{
			$this->sql['WHERE'][] = $statements[$sort]['WHERE'];
		}

		// If we have an ORDER BY we use that. The absence of an ORDER BY
		// means we will default to sorting ideas by their calculated score.
		if (isset($statements[$sort]['ORDER_BY']))
		{
			$this->sql['ORDER_BY'] = "{$statements[$sort]['ORDER_BY']} $direction";
		}
		else
		{
			// https://www.evanmiller.org/how-not-to-sort-by-average-rating.html
			$this->sql['SELECT'][] = '((i.idea_votes_up + 1.9208) / (i.idea_votes_up + i.idea_votes_down) -
				1.96 * SQRT((i.idea_votes_up * i.idea_votes_down) / (i.idea_votes_up + i.idea_votes_down) + 0.9604) /
				(i.idea_votes_up + i.idea_votes_down)) / (1 + 3.8416 / (i.idea_votes_up + i.idea_votes_down))
				AS ci_lower_bound';

			$this->sql['ORDER_BY'] = "ci_lower_bound $direction";
		}

		return $this;
	}

	/**
	 * Update $sql property with additional SQL statements to filter ideas
	 * by status. If $status is given we'll get those ideas. If no $status
	 * is given, the default is to get all ideas excluding Duplicates, Invalid
	 * and Implemented statuses (because they are considered done & dusted,
	 * if they were gases they'd be inert).
	 *
	 * @param array|int $status The id(s) of the status(es) to load
	 *
	 * @return \phpbb\ideas\factory\ideas $this For chaining calls
	 */
	protected function query_status($status = [])
	{
		$this->sql['WHERE'][] = !empty($status) ? $this->db->sql_in_set('i.idea_status', $status) : $this->db->sql_in_set(
			'i.idea_status', [self::$statuses['IMPLEMENTED'], self::$statuses['DUPLICATE'], self::$statuses['INVALID'],
		], true);

		return $this;
	}

	/**
	 * Run a query using the $sql property to get a collection of ideas.
	 *
	 * @param int $number The number of ideas to return
	 * @param int $start  Start value for pagination
	 *
	 * @return mixed      Nested array if the query had rows, false otherwise
	 * @throws \phpbb\exception\runtime_exception
	 */
	protected function query_get($number, $start)
	{
		if (empty($this->sql))
		{
			throw new runtime_exception('INVALID_IDEA_QUERY');
		}

		$sql = 'SELECT ' . implode(', ', $this->sql['SELECT']) . '
			FROM ' . $this->sql['FROM'] . '
			INNER JOIN ' . $this->sql['JOIN'] . '
			WHERE ' . implode(' AND ', $this->sql['WHERE']) . '
			ORDER BY ' . $this->sql['ORDER_BY'];

		$result = $this->db->sql_query_limit($sql, $number, $start);
		$rows = $this->db->sql_fetchrowset($result);
		$this->db->sql_freeresult($result);

		return $rows;
	}

	/**
	 * Run a query using the $sql property to get a count of ideas.
	 *
	 * @return int The number of ideas
	 * @throws \phpbb\exception\runtime_exception
	 */
	protected function query_count()
	{
		if (empty($this->sql))
		{
			throw new runtime_exception('INVALID_IDEA_QUERY');
		}

		$sql = 'SELECT COUNT(i.idea_id) as count
			FROM ' . $this->sql['FROM'] . '
       		INNER JOIN ' . $this->sql['JOIN'] . '
			WHERE ' . implode(' AND ', $this->sql['WHERE']);

		$result = $this->db->sql_query($sql);
		$count = (int) $this->db->sql_fetchfield('count');
		$this->db->sql_freeresult($result);

		return $count;
	}

	/**
	 * Do a live search on idea titles. Return any matches based on a given search query.
	 *
	 * @param string $search The string of characters to search using LIKE
	 * @param int    $limit  The number of results to return
	 *
	 * @return array An array of matching idea id/key and title/values
	 */
	public function livesearch($search, $limit = 10)
	{
		$results = [];
		$sql = 'SELECT idea_title, idea_id
			FROM ' . $this->table_ideas . '
			WHERE idea_title ' . $this->db->sql_like_expression($this->db->get_any_char() . $search . $this->db->get_any_char());
		$result = $this->db->sql_query_limit($sql, $limit);
		while ($row = $this->db->sql_fetchrow($result))
		{
			$results[] = [
				'idea_id'     => $row['idea_id'],
				'result'      => $row['idea_id'],
				'clean_title' => $row['idea_title'],
				'display'     => "<span>{$row['idea_title']}</span>", // spans are expected in phpBB's live search JS
			];
		}
		$this->db->sql_freeresult($result);

		return $results;
	}

	/**
	 * Get a user's votes from a group of ideas
	 *
	 * @param int $user_id The user's id
	 * @param array $ids An array of idea ids
	 *
	 * @return array An array of ideas the user voted on and their vote result, or empty otherwise.
	 *               example: [idea_id => vote_result]
	 *                         1 => 1, idea 1, voted up by the user
	 *                         2 => 0, idea 2, voted down by the user
	 */
	public function get_users_votes($user_id, array $ids)
	{
		$results = [];
		$sql = 'SELECT idea_id, vote_value
			FROM ' . $this->table_votes . '
			WHERE user_id = ' . (int) $user_id . '
			AND ' . $this->db->sql_in_set('idea_id', $ids, false, true);
		$result = $this->db->sql_query($sql);
		while ($row = $this->db->sql_fetchrow($result))
		{
			$results[$row['idea_id']] = $row['vote_value'];
		}
		$this->db->sql_freeresult($result);

		return $results;
	}

	/**
	 * Delete orphaned ideas. Orphaned ideas may exist after a
	 * topic has been deleted or moved to another forum.
	 *
	 * @return int Number of rows affected
	 */
	public function delete_orphans()
	{
		// Find any orphans
		$sql = 'SELECT idea_id FROM ' . $this->table_ideas . '
 			WHERE topic_id NOT IN (SELECT t.topic_id
 			FROM ' . $this->table_topics . ' t
 				WHERE t.forum_id = ' . (int) $this->config['ideas_forum_id'] . ')';
		$result = $this->db->sql_query($sql);
		$rows = $this->db->sql_fetchrowset($result);
		$this->db->sql_freeresult($result);

		if (empty($rows))
		{
			return 0;
		}

		$this->db->sql_transaction('begin');

		foreach ($rows as $row)
		{
			// Delete idea
			$this->delete_idea_data($row['idea_id'], $this->table_ideas);

			// Delete votes
			$this->delete_idea_data($row['idea_id'], $this->table_votes);
		}

		$this->db->sql_transaction('commit');

		return count($rows);
	}

	/**
	 * Get the stored idea count
	 * Note: this should only be called after get_ideas()
	 *
	 * @return int Count of ideas
	 */
	public function count()
	{
		return isset($this->idea_count) ? $this->idea_count : 0;
	}
}


1			<?php
2			/**
3			*
4			* Ideas extension for the phpBB Forum Software package.
5			*
6			* @copyright (c) phpBB Limited <https://www.phpbb.com>
7			* @license GNU General Public License, version 2 (GPL-2.0)
8			*
9			*/
10
11			namespace phpbb\ideas\factory;
12
13			use phpbb\exception\runtime_exception;
14
15			/**
16			* Class for handling multiple ideas
17			*/
18			class ideas extends base
19			{
20			/** @var int */
21			protected $idea_count;
22
23			/** @var array */
24			protected $sql;
25
26			/**
27			* Returns an array of ideas. Defaults to ten ideas ordered by date
28			* excluding implemented, duplicate or invalid ideas.
29			*
30			* @param int $number The number of ideas to return
31			* @param string $sort A sorting option/collection
32			* @param string $direction Should either be ASC or DESC
33			* @param array\|int $status The id of the status(es) to load
34			* @param int $start Start value for pagination
35			*
36			* @return array Array of row data
37			*/
38			public function get($number = 10, $sort = 'date', $direction = 'DESC', $status = [], $start = 0)
39			{
40			// Initialize a query to request ideas
41			$sql = $this->query_ideas()
42			->query_sort($sort, $direction)
43			->query_status($status);
44
45			// For pagination, get a count of the total ideas being requested
46			if ($number >= $this->config['posts_per_page'])
47			{
48			$this->idea_count = $sql->query_count();
49			}
50
51			$ideas = $sql->query_get($number, $start);
52
53			if (count($ideas))
54			{
55			$topic_ids = array_column($ideas, 'topic_id');
56			$idea_ids = array_column($ideas, 'idea_id');
57
58			$topic_tracking_info = get_complete_topic_tracking((int) $this->config['ideas_forum_id'], $topic_ids);
59			$user_voting_info = $this->get_users_votes($this->user->data['user_id'], $idea_ids);
60
61			foreach ($ideas as &$idea)
62			{
63			$idea['read'] = !(isset($topic_tracking_info[$idea['topic_id']]) && $idea['topic_last_post_time'] > $topic_tracking_info[$idea['topic_id']]);
64			$idea['u_voted'] = isset($user_voting_info[$idea['idea_id']]) ? (int) $user_voting_info[$idea['idea_id']] : '';
65			}
66			unset ($idea);
67			}
68
69			return $ideas;
70			}
71
72			/**
73			* Initialize the $sql property with necessary SQL statements.
74			*
75			* @return \phpbb\ideas\factory\ideas $this For chaining calls
76			*/
77			protected function query_ideas()
78			{
79			$this->sql = [];
80
81			$this->sql['SELECT'][] = 't.topic_last_post_time, t.topic_status, t.topic_visibility, i.*';
82			$this->sql['FROM'] = "{$this->table_ideas} i";
83			$this->sql['JOIN'] = "{$this->table_topics} t ON i.topic_id = t.topic_id";
84			$this->sql['WHERE'][] = 't.forum_id = ' . (int) $this->config['ideas_forum_id'];
85
86			// Only get approved topics for regular users, Moderators will see unapproved topics
87			if (!$this->auth->acl_get('m_', $this->config['ideas_forum_id']))
88			{
89			$this->sql['WHERE'][] = 't.topic_visibility = ' . ITEM_APPROVED;
90			}
91
92			return $this;
93			}
94
95			/**
96			* Update the $sql property with ORDER BY statements to obtain
97			* the requested collection of Ideas. Some instances may add
98			* additional WHERE or SELECT statements to refine the collection.
99			*
100			* @param string $sort A sorting option/collection
101			* @param string $direction Will either be ASC or DESC
102			*
103			* @return \phpbb\ideas\factory\ideas $this For chaining calls
104			*/
105			protected function query_sort($sort, $direction)
106			{
107			$sort = strtolower($sort);
108			$direction = $direction === 'DESC' ? 'DESC' : 'ASC';
109
110			// Most sorting relies on simple ORDER BY statements, but some may use a WHERE statement
111			$statements = [
112			self::SORT_DATE => ['ORDER_BY' => 'i.idea_date'],
113			self::SORT_TITLE => ['ORDER_BY' => 'i.idea_title'],
114			self::SORT_AUTHOR => ['ORDER_BY' => 'i.idea_author'],
115			self::SORT_SCORE => ['ORDER_BY' => 'CAST(i.idea_votes_up AS decimal) - CAST(i.idea_votes_down AS decimal)'],
116			self::SORT_VOTES => ['ORDER_BY' => 'i.idea_votes_up + i.idea_votes_down'],
117			self::SORT_TOP => ['WHERE' => 'i.idea_votes_up > i.idea_votes_down'],
118			self::SORT_MYIDEAS => ['ORDER_BY' => 'i.idea_date', 'WHERE' => 'i.idea_author = ' . (int) $this->user->data['user_id']],
119			];
120
121			// Append a new WHERE statement if the sort has one
122			if (isset($statements[$sort]['WHERE']))
123			{
124			$this->sql['WHERE'][] = $statements[$sort]['WHERE'];
125			}
126
127			// If we have an ORDER BY we use that. The absence of an ORDER BY
128			// means we will default to sorting ideas by their calculated score.
129			if (isset($statements[$sort]['ORDER_BY']))
130			{
131			$this->sql['ORDER_BY'] = "{$statements[$sort]['ORDER_BY']} $direction";
132			}
133			else
134			{
135			// https://www.evanmiller.org/how-not-to-sort-by-average-rating.html
136			$this->sql['SELECT'][] = '((i.idea_votes_up + 1.9208) / (i.idea_votes_up + i.idea_votes_down) -
137			1.96 * SQRT((i.idea_votes_up * i.idea_votes_down) / (i.idea_votes_up + i.idea_votes_down) + 0.9604) /
138			(i.idea_votes_up + i.idea_votes_down)) / (1 + 3.8416 / (i.idea_votes_up + i.idea_votes_down))
139			AS ci_lower_bound';
140
141			$this->sql['ORDER_BY'] = "ci_lower_bound $direction";
142			}
143
144			return $this;
145			}
146
147			/**
148			* Update $sql property with additional SQL statements to filter ideas
149			* by status. If $status is given we'll get those ideas. If no $status
150			* is given, the default is to get all ideas excluding Duplicates, Invalid
151			* and Implemented statuses (because they are considered done & dusted,
152			* if they were gases they'd be inert).
153			*
154			* @param array\|int $status The id(s) of the status(es) to load
155			*
156			* @return \phpbb\ideas\factory\ideas $this For chaining calls
157			*/
158			protected function query_status($status = [])
159			{
160			$this->sql['WHERE'][] = !empty($status) ? $this->db->sql_in_set('i.idea_status', $status) : $this->db->sql_in_set(
161			'i.idea_status', [self::$statuses['IMPLEMENTED'], self::$statuses['DUPLICATE'], self::$statuses['INVALID'],
162			], true);
163
164			return $this;
165			}
166
167			/**
168			* Run a query using the $sql property to get a collection of ideas.
169			*
170			* @param int $number The number of ideas to return
171			* @param int $start Start value for pagination
172			*
173			* @return mixed Nested array if the query had rows, false otherwise
174			* @throws \phpbb\exception\runtime_exception
175			*/
176			protected function query_get($number, $start)
177			{
178			if (empty($this->sql))
179			{
180			throw new runtime_exception('INVALID_IDEA_QUERY');
181			}
182
183			$sql = 'SELECT ' . implode(', ', $this->sql['SELECT']) . '
184			FROM ' . $this->sql['FROM'] . '
185			INNER JOIN ' . $this->sql['JOIN'] . '
186			WHERE ' . implode(' AND ', $this->sql['WHERE']) . '
187			ORDER BY ' . $this->sql['ORDER_BY'];
188
189			$result = $this->db->sql_query_limit($sql, $number, $start);
190			$rows = $this->db->sql_fetchrowset($result);
191			$this->db->sql_freeresult($result);
192
193			return $rows;
194			}
195
196			/**
197			* Run a query using the $sql property to get a count of ideas.
198			*
199			* @return int The number of ideas
200			* @throws \phpbb\exception\runtime_exception
201			*/
202			protected function query_count()
203			{
204			if (empty($this->sql))
205			{
206			throw new runtime_exception('INVALID_IDEA_QUERY');
207			}
208
209			$sql = 'SELECT COUNT(i.idea_id) as count
210			FROM ' . $this->sql['FROM'] . '
211			INNER JOIN ' . $this->sql['JOIN'] . '
212			WHERE ' . implode(' AND ', $this->sql['WHERE']);
213
214			$result = $this->db->sql_query($sql);
215			$count = (int) $this->db->sql_fetchfield('count');
216			$this->db->sql_freeresult($result);
217
218			return $count;
219			}
220
221			/**
222			* Do a live search on idea titles. Return any matches based on a given search query.
223			*
224			* @param string $search The string of characters to search using LIKE
225			* @param int $limit The number of results to return
226			*
227			* @return array An array of matching idea id/key and title/values
228			*/
229			public function livesearch($search, $limit = 10)
230			{
231			$results = [];
232			$sql = 'SELECT idea_title, idea_id
233			FROM ' . $this->table_ideas . '
234			WHERE idea_title ' . $this->db->sql_like_expression($this->db->get_any_char() . $search . $this->db->get_any_char());
235			$result = $this->db->sql_query_limit($sql, $limit);
236			while ($row = $this->db->sql_fetchrow($result))
237			{
238			$results[] = [
239			'idea_id' => $row['idea_id'],
240			'result' => $row['idea_id'],
241			'clean_title' => $row['idea_title'],
242			'display' => "<span>{$row['idea_title']}</span>", // spans are expected in phpBB's live search JS
243			];
244			}
245			$this->db->sql_freeresult($result);
246
247			return $results;
248			}
249
250			/**
251			* Get a user's votes from a group of ideas
252			*
253			* @param int $user_id The user's id
254			* @param array $ids An array of idea ids
255			*
256			* @return array An array of ideas the user voted on and their vote result, or empty otherwise.
257			* example: [idea_id => vote_result]
258			* 1 => 1, idea 1, voted up by the user
259			* 2 => 0, idea 2, voted down by the user
260			*/
261			public function get_users_votes($user_id, array $ids)
262			{
263			$results = [];
264			$sql = 'SELECT idea_id, vote_value
265			FROM ' . $this->table_votes . '
266			WHERE user_id = ' . (int) $user_id . '
267			AND ' . $this->db->sql_in_set('idea_id', $ids, false, true);
268			$result = $this->db->sql_query($sql);
269			while ($row = $this->db->sql_fetchrow($result))
270			{
271			$results[$row['idea_id']] = $row['vote_value'];
272			}
273			$this->db->sql_freeresult($result);
274
275			return $results;
276			}
277
278			/**
279			* Delete orphaned ideas. Orphaned ideas may exist after a
280			* topic has been deleted or moved to another forum.
281			*
282			* @return int Number of rows affected
283			*/
284			public function delete_orphans()
285			{
286			// Find any orphans
287			$sql = 'SELECT idea_id FROM ' . $this->table_ideas . '
288			WHERE topic_id NOT IN (SELECT t.topic_id
289			FROM ' . $this->table_topics . ' t
290			WHERE t.forum_id = ' . (int) $this->config['ideas_forum_id'] . ')';
291			$result = $this->db->sql_query($sql);
292			$rows = $this->db->sql_fetchrowset($result);
293			$this->db->sql_freeresult($result);
294
295			if (empty($rows))
296			{
297			return 0;
298			}
299
300			$this->db->sql_transaction('begin');
301
302			foreach ($rows as $row)
303			{
304			// Delete idea
305			$this->delete_idea_data($row['idea_id'], $this->table_ideas);
306
307			// Delete votes
308			$this->delete_idea_data($row['idea_id'], $this->table_votes);
309			}
310
311			$this->db->sql_transaction('commit');
312
313			return count($rows);
314			}
315
316			/**
317			* Get the stored idea count
318			* Note: this should only be called after get_ideas()
319			*
320			* @return int Count of ideas
321			*/
322			public function count()
323			{
324			return isset($this->idea_count) ? $this->idea_count : 0;
325			}
326			}
327

phpbb / ideas

Pull Request — master (#131)

ideas::livesearch() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like