ideas::query_ideas() - Code Metrics - Inspection of "Merge pull request #132 from iMattPro/experiment" - phpbb/ideas - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( a4a81d...185fd8 )

unknown

created 2020-07-04 12:15 UTC

ideas::query_ideas() A

↳ Parent: ideas

Complexity

Conditions	2
Paths	2

Size

Total Lines	16
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	8
c	0
b	0
f	0
dl	0
loc	16
rs	10
cc	2
nc	2
nop	0

<?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;
use phpbb\ideas\ext;

/**
 * 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_ideas($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 = [
			ext::SORT_DATE    => ['ORDER_BY' => 'i.idea_date'],
			ext::SORT_TITLE   => ['ORDER_BY' => 'i.idea_title'],
			ext::SORT_AUTHOR  => ['ORDER_BY' => 'i.idea_author'],
			ext::SORT_SCORE   => ['ORDER_BY' => 'CAST(i.idea_votes_up AS decimal) - CAST(i.idea_votes_down AS decimal)'],
			ext::SORT_VOTES   => ['ORDER_BY' => 'i.idea_votes_up + i.idea_votes_down'],
			ext::SORT_TOP     => ['WHERE' => 'i.idea_votes_up > i.idea_votes_down'],
			ext::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', [ext::$statuses['IMPLEMENTED'], ext::$statuses['DUPLICATE'], ext::$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;
	}

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

phpbb / ideas

Push — master ( a4a81d...185fd8 )

ideas::query_ideas() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like