[3.2][BETA] Best Answer

A place for Extension Authors to post and receive feedback on Extensions still in development. No Extensions within this forum should be used within a live environment!
Scam Warning
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

IMPORTANT: Extensions Development rules

IMPORTANT FOR NEEDED EVENTS!!!
If you need an event for your extension please read this for the steps to follow to request the event(s)
User avatar
WelshPaul
Registered User
Posts: 244
Joined: Tue Aug 19, 2014 2:09 pm

Re: [3.2][BETA] Best Answer

Post by WelshPaul » Fri Jan 19, 2018 5:08 pm

This kills my board because it clashes with another extension. I get the following error:
QL ERROR [ mysqli ]

Not unique table/alias: 'p' [1066]

SQL

SELECT f.*, ft.mark_time, t.answer_post_id, t.topic_id AS forum_last_post_topic_id, ( SELECT COUNT(p.post_id) FROM ukvfgb_posts p WHERE p.forum_id = f.forum_id AND p.post_id IN ( SELECT pb.post_id FROM ukvfgb_post_bookmarks pb WHERE user_id = 2 ) ) as bookmarked_posts FROM (ukvfgb_forums f) LEFT JOIN ukvfgb_forums_track ft ON (ft.user_id = 2 AND ft.forum_id = f.forum_id) LEFT JOIN ukvfgb_posts p ON (f.forum_last_post_id = p.post_id) LEFT JOIN ukvfgb_topics t ON (t.topic_id = p.topic_id) LEFT JOIN ukvfgb_posts p ON (f.forum_last_post_id = p.post_id) LEFT JOIN ukvfgb_topics t ON (t.topic_id = p.topic_id) ORDER BY f.left_id

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 996
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 193
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 329
CALL: phpbb\db\driver\mysqli->sql_query()

FILE: [ROOT]/includes/functions_display.php
LINE: 150
CALL: phpbb\db\driver\factory->sql_query()

FILE: [ROOT]/index.php
LINE: 76
CALL: display_forums()
You shouldn't be using those aliases?

User avatar
WelshPaul
Registered User
Posts: 244
Joined: Tue Aug 19, 2014 2:09 pm

Re: [BETA] Best Answer

Post by WelshPaul » Fri Jan 19, 2018 7:01 pm

kasimi wrote:
Wed Jul 27, 2016 10:58 pm
Looks like there's another extension that adds t.topic_id AS forum_last_post_topic_id as well as those two duplicate left joins. Try to disable any extension that works with data from a forum's last post.
kinerity wrote:I'll leave the code the way it is.
You could try to only add the left joins if they aren't already present. I'm not sure if that's the most reliable way though.

Code: Select all

if (!$this->has_left_join($sql_ary['LEFT_JOIN'], POSTS_TABLE))
{
    $sql_ary['LEFT_JOIN'][] = array(
        'FROM'    => array(POSTS_TABLE => 'p'),
        'ON'    => 'f.forum_last_post_id = p.post_id',
    );
} 
... and the same for the TOPICS_TABLE. Here's the has_left_join() function:

Code: Select all

private function has_left_join($haystack, $needle)
{
    foreach ($haystack as $left_join)
    {
        if (isset($left_join['FROM'][$needle]))
        {
            return true;
        }
    }

    return false;
} 
No it's not because all other extensions that make use of the same function would need these code changes too. The correct way is to prefix the table alias to prevent conflicts with another extensions, like so: https://github.com/senky/phpbb-ext-remo ... c23ac00d19

User avatar
kinerity
Community Team Member
Community Team Member
Posts: 1736
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: [3.2][BETA] Best Answer

Post by kinerity » Fri Jan 19, 2018 7:08 pm

I will look at the code over the weekend.
Kailey Truscott - Community Team

User avatar
WelshPaul
Registered User
Posts: 244
Joined: Tue Aug 19, 2014 2:09 pm

Re: [3.2][BETA] Best Answer

Post by WelshPaul » Sat Jan 20, 2018 12:38 pm

Fixed this issue, see pull request on github.

FYI, I used bestanswer_ as the alias but extension authors should be using as short as possible an alias (e.g. ba_ or kba_ would be better in the long term).

Also, the following error is displayed when trying to permanently delete a post with your extension enabled:
SQL ERROR [ mysqli ]

BIGINT UNSIGNED value is out of range in '(`servrigh_322`.`phpbb_users`.`user_answers` - 1)' [1690]

SQL

UPDATE phpbb_users SET user_answers = user_answers - 1 WHERE user_id = 2

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 996
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 193
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 329
CALL: phpbb\db\driver\mysqli->sql_query()

FILE: [ROOT]/ext/kinerity/bestanswer/event/main_listener.php
LINE: 209
CALL: phpbb\db\driver\factory->sql_query()

FILE: (not given by php)
LINE: (not given by php)
CALL: kinerity\bestanswer\event\main_listener->delete_posts_in_transaction_before()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 184
CALL: call_user_func()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 46
CALL: Symfony\Component\EventDispatcher\EventDispatcher->doDispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 62
CALL: Symfony\Component\EventDispatcher\EventDispatcher->dispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 46
CALL: phpbb\event\dispatcher->dispatch()

FILE: [ROOT]/includes/functions_admin.php
LINE: 1010
CALL: phpbb\event\dispatcher->trigger_event()

FILE: [ROOT]/includes/mcp/mcp_main.php
LINE: 1115
CALL: delete_posts()

FILE: [ROOT]/includes/mcp/mcp_queue.php
LINE: 84
CALL: mcp_delete_post()

FILE: [ROOT]/includes/functions_module.php
LINE: 676
CALL: mcp_queue->main()

FILE: [ROOT]/mcp.php
LINE: 312
CALL: p_master->load_active()

User avatar
WelshPaul
Registered User
Posts: 244
Joined: Tue Aug 19, 2014 2:09 pm

Re: [3.2][BETA] Best Answer

Post by WelshPaul » Sun Jan 21, 2018 5:16 pm

I get the above error when trying to approve posts, delete members via ACP etc too...

My fix (for those who want it), open the main_listener.php file and find:

Code: Select all

	public function delete_posts_in_transaction_before($event)
	{
		$post_ids = $event['post_ids'];
		$poster_ids = $event['poster_ids'];
		$topic_ids = $event['topic_ids'];
		$answer_post_ids = $answer_user_ids = array();

		// We really only care about topics with answers, so
		// select them here, then intersect each array
		$sql = 'SELECT answer_post_id, answer_user_id
			FROM ' . TOPICS_TABLE . '
			WHERE ' . $this->db->sql_in_set('topic_id', $topic_ids) . '
				AND answer_post_id <> 0';
		$result = $this->db->sql_query($sql);
		while ($row = $this->db->sql_fetchrow($result))
		{
			$answer_post_ids[] = $row['answer_post_id'];
			$answer_user_ids[] = $row['answer_user_id'];
		}
		$this->db->sql_freeresult($result);

		if (!empty($answer_post_ids))
		{
			$post_ids = array_intersect($post_ids, $answer_post_ids);
			$poster_ids = array_intersect($poster_ids, $answer_user_ids);
		}

		// Remove the answer data from the TOPICS_TABLE
		foreach ($post_ids as $post_id)
		{
			$data = array(
				'answer_post_id'	=> 0,
				'answer_user_id'	=> 0,
			);

			$sql = 'UPDATE ' . TOPICS_TABLE . ' SET ' . $this->db->sql_build_array('UPDATE', $data) . ' WHERE answer_post_id = ' . (int) $post_id;
			$this->db->sql_query($sql);
		}

		foreach ($poster_ids as $poster_id)
		{
			$sql = 'UPDATE ' . USERS_TABLE . '
				SET user_answers = user_answers - 1
				WHERE user_id = ' . (int) $poster_id;
			$this->db->sql_query($sql);
		}
	}
Replace with:

Code: Select all

	public function delete_posts_in_transaction_before($event)
	{
		$post_ids = $event['post_ids'];
		$topic_ids = $event['topic_ids'];

		$sql = 'SELECT answer_post_id, answer_user_id
			FROM ' . TOPICS_TABLE . '
			WHERE answer_post_id != 0
				AND ' . $this->db->sql_in_set('topic_id', $topic_ids);
		$result = $this->db->sql_query($sql);
		while ($row = $this->db->sql_fetchrow($result))
		{
			if (in_array($row['answer_post_id'], $post_ids))
			{
				$answer_post_ids[] = $row['answer_post_id'];
				$answer_user_ids[] = $row['answer_user_id'];
			}
		}
		$this->db->sql_freeresult($result);

		$postdata = array(
				'answer_post_id'	=> 0,
				'answer_user_id'	=> 0,
			);

		if (sizeof($answer_post_ids)) {
			$sql = 'UPDATE ' . TOPICS_TABLE . ' SET ' . $this->db->sql_build_array('UPDATE', $postdata) . ' WHERE ' . $this->db->sql_in_set('answer_post_id', $answer_post_ids);
			$this->db->sql_query($sql);
			$sql = 'UPDATE ' . USERS_TABLE . ' SET user_answers = user_answers - 1 WHERE ' . $this->db->sql_in_set('user_id', $answer_user_ids);
			$this->db->sql_query($sql);
		}
	}
Regarding my initial "Not unique table/alias: 'p' [1066]" error, this is the fix for that too...

Open the main_listener.php file and find:

Code: Select all

	public function display_forums_modify_sql($event)
	{
		$sql_ary = $event['sql_ary'];

		$sql_ary['SELECT'] .= ', t.answer_post_id';

		if (!$this->has_join($sql_ary['LEFT_JOIN'], POSTS_TABLE))
		{
			$sql_ary['LEFT_JOIN'][] = array(
				'FROM'	=> array(POSTS_TABLE => 'p'),
				'ON'	=> 'f.forum_last_post_id = p.post_id',
			);
		}

		if (!$this->has_join($sql_ary['LEFT_JOIN'], TOPICS_TABLE))
		{
			$sql_ary['LEFT_JOIN'][] = array(
				'FROM'	=> array(TOPICS_TABLE => 't'),
				'ON'	=> 't.topic_id = p.topic_id',
			);
		}

		$event['sql_ary'] = $sql_ary;
	}
Replace with:

Code: Select all

	public function display_forums_modify_sql($event)
	{
		$sql_ary = $event['sql_ary'];
		$sql_ary['SELECT'] .= ', kba_t.answer_post_id';
		$sql_ary['LEFT_JOIN'][] = array(
			'FROM'	=> array(POSTS_TABLE => 'kba_p'),
			'ON'	=> 'f.forum_last_post_id = kba_p.post_id',
		);

		$sql_ary['LEFT_JOIN'][] = array(
			'FROM'	=> array(TOPICS_TABLE => 'kba_t'),
			'ON'	=> 'kba_t.topic_id = kba_p.topic_id',
		);

		$event['sql_ary'] = $sql_ary;
	}
Find:

Code: Select all

	private function has_join($join_ary, $table)
	{
		foreach ($join_ary as $join)
		{
			if (isset($join['FROM'][$table]))
			{
				return true;
			}
		}

		return false;
	}
Delete it!

You're welcome! :)

User avatar
kinerity
Community Team Member
Community Team Member
Posts: 1736
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: [3.2][BETA] Best Answer

Post by kinerity » Sun Jan 21, 2018 5:46 pm

I would have gladly accepted your pull request, however you lumped everything in one commit. If you don't mind, I'll go ahead and push a commit with these changes.

EDIT - I've gone ahead and pushed this commit. It has your changes. Please test.
Kailey Truscott - Community Team

User avatar
WelshPaul
Registered User
Posts: 244
Joined: Tue Aug 19, 2014 2:09 pm

Re: [3.2][BETA] Best Answer

Post by WelshPaul » Fri Jan 26, 2018 8:49 am

The search answered posts is broken, basically it just show the results inside that first page (25 first topics). The filter is being applied after the pagination. It should be before.

Image

Open the main_listener.php file and find:

Code: Select all

	public function search_get_topic_data($event)
	{
		$sql_select = $event['sql_select'];
		$sql_from = $event['sql_from'];
		$sql_where = $event['sql_where'];
		// Allow users to search topics a user has answered
		$filter = $this->request->variable('filter', '');
		$author_id = $this->request->variable('author_id', 0);
		if ($filter == 'topicsanswered')
		{
			$sql_select .= ', p.post_id, p.poster_id';
			$sql_from .= ' LEFT JOIN ' . POSTS_TABLE . ' p ON (p.post_id = t.answer_post_id)';
			$sql_where .= ' AND p.poster_id = ' . (int) $author_id;
			// Set $total_match_count to 0 - DO NOT modify
			// the $event['total_match_count'] variable - it
			// will be set at the end of this if block
			$total_match_count = 0;
			// Grab all necessary data to modify total_match_count
			$sql_array = array(
				'SELECT'	=> 'p.post_id, p.poster_id, t.topic_id, t.answer_post_id',
				'FROM'		=> array(
					POSTS_TABLE		=> 'p',
					TOPICS_TABLE	=> 't',
				),
				'WHERE'		=> 'p.post_id = t.answer_post_id
									AND p.poster_id = ' . (int) $author_id,
			);
			$sql = $this->db->sql_build_query('SELECT', $sql_array);
			// Run the built query
			$result = $this->db->sql_query($sql);
			while ($row = $this->db->sql_fetchrow($result))
			{
				$total_match_count++;
			}
			$this->db->sql_freeresult($result);
			$event['total_match_count'] = $total_match_count;
		}
		// $filter is only allowed to have topics_answered as a value, but
		// must also allow empty values so core searches are not affected
		else if ($filter != '')
		{
			trigger_error($this->lang->lang('INVALID_FILTER'));
		}
		$event['sql_select'] = $sql_select;
		$event['sql_from'] = $sql_from;
		$event['sql_where'] = $sql_where;
	}
Replace with:

Code: Select all

	public function search_get_topic_data($event)
	{
		// Allow users to search topics a user has answered
		$filter = $this->request->variable('filter', '');
		$author_id = $this->request->variable('author_id', 0);

		if ($filter == 'topicsanswered')
		{
			$sql_select = 'f.forum_id, f.forum_name, p.post_id, p.poster_id, t.topic_id, t.topic_attachment, t.answer_post_id, 
				t.topic_title, t.topic_first_poster_name, t.topic_last_poster_name, t.topic_time, t.topic_last_post_time, 
				t.topic_views, t.topic_visibility, t.topic_posts_approved';
			$sql_from = FORUMS_TABLE . ' f, '. POSTS_TABLE . ' p, ' . TOPICS_TABLE . ' t';
			$sql_where = '(p.post_id = t.answer_post_id) AND p.poster_id = ' . (int) $author_id;

			// Set $total_match_count to 0 - DO NOT modify
			// the $event['total_match_count'] variable - it
			// will be set at the end of this if block
			$total_match_count = 0;

			// Grab all necessary data to modify total_match_count
			$sql_array = array(
				'SELECT'	=> 'p.post_id, p.poster_id, t.topic_id, t.answer_post_id',

				'FROM'		=> array(
					POSTS_TABLE		=> 'p',
					TOPICS_TABLE	=> 't',
				),

				'WHERE'		=> 'p.post_id = t.answer_post_id
									AND p.poster_id = ' . (int) $author_id,
			);
			$sql = $this->db->sql_build_query('SELECT', $sql_array);

			// Run the built query
			$result = $this->db->sql_query($sql);
			while ($row = $this->db->sql_fetchrow($result))
			{
				$total_match_count++;
			}
			$this->db->sql_freeresult($result);

			$event['total_match_count'] = $total_match_count;
		}
		// $filter is only allowed to have topics_answered as a value, but
		// must also allow empty values so core searches are not affected
		else if ($filter != '')
		{
			trigger_error($this->lang->lang('INVALID_FILTER'));
		}

		$event['sql_select'] = $sql_select;
		$event['sql_from'] = $sql_from;
		$event['sql_where'] = $sql_where;
	}
EDIT: Do not use the code in this post, the issue lies with pagination not being included in the extensions original core code.

User avatar
kinerity
Community Team Member
Community Team Member
Posts: 1736
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: [3.2][BETA] Best Answer

Post by kinerity » Mon Jan 29, 2018 8:09 am

I'm open to pull requests, but there's no way to modify pagination as there's no events. For now, searching user answers has been removed.
Kailey Truscott - Community Team

User avatar
Niko!
Registered User
Posts: 17
Joined: Thu Jan 22, 2015 5:27 pm
Contact:

Re: [3.2][BETA] Best Answer

Post by Niko! » Fri Feb 02, 2018 2:02 pm

Hello,

I tried it, but how should someone mark the reply as "Best Answer" ?
I can't see any button or anything :shock:
...

User avatar
kinerity
Community Team Member
Community Team Member
Posts: 1736
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: [3.2][BETA] Best Answer

Post by kinerity » Fri Feb 02, 2018 7:01 pm

Image

Note that you will need to be the original poster (with the appropriate forum permission) or a moderator (with the appropriate moderator permission) for the buttons to show. The extension also needs to be enabled in each forum's settings page.
Kailey Truscott - Community Team

User avatar
WelshPaul
Registered User
Posts: 244
Joined: Tue Aug 19, 2014 2:09 pm

Re: [3.2][BETA] Best Answer

Post by WelshPaul » Sat Feb 03, 2018 8:23 pm

Found another bug...

If you have a post in "Forum A" marked as answered, on the main index under the "last post" section the topic is correctly marked as answered.

If you have a post in "Forum A" > "SUBFORUM 1" > "SUBFORUM 2" and no best answer has been marked in that post, on the main index under the "last post" section the topic is incorrectly marked as answered. It appears that when having a subforum inside a subforum, best answers uses the results from the very first forum.

So the "last post" section on main index correctly displays topics marked as best answer when in:
  • Forum A
And:
  • Forum A > Subforum 1
But not for posts in:
  • Forum A > Subforum 1 > Subforum 2

User avatar
kinerity
Community Team Member
Community Team Member
Posts: 1736
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: [3.2][BETA] Best Answer

Post by kinerity » Sat Feb 03, 2018 9:38 pm

I can't reproduce. Are you using the latest GitHub release? This issue was fixed in this commit.
Kailey Truscott - Community Team

User avatar
WelshPaul
Registered User
Posts: 244
Joined: Tue Aug 19, 2014 2:09 pm

Re: [3.2][BETA] Best Answer

Post by WelshPaul » Sat Feb 03, 2018 9:42 pm

Tested on three different servers using the latest github release.

It all works correctly on main forum posts and first sub forum level posts but any posts in additional subforum levels and the bug is present. To reproduce, mark the latest post in the first forum with an answer, create a new post in second sub level subforum and navigate to index page and you will see it's marked as best answered.

This fixes it:

Code: Select all

	public function display_forums_modify_forum_rows($event)
	{
		$forum_rows = $event['forum_rows'];
		$parent_id = $event['parent_id'];
		$row = $event['row'];

		// Suggested by the core, equal post times should never happen. Check it just in case.
		if ($row['forum_last_post_time'] >= $forum_rows[$parent_id]['forum_last_post_time'])
		{
			$forum_rows[$parent_id]['answer_post_id'] = $row['answer_post_id'];
			
			// Is the extension enabled on the forum and related answer_post_id not null?
			if ($row['enable_answer'] && !($forum_rows[$parent_id]['answer_post_id'] === false))
			{
				$forum_rows[$parent_id]['answer_post_id'] = $row['answer_post_id'];
			}
		}

		$event['forum_rows'] = $forum_rows;
	}

User avatar
kinerity
Community Team Member
Community Team Member
Posts: 1736
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: [3.2][BETA] Best Answer

Post by kinerity » Sat Feb 03, 2018 9:57 pm

Ah, I see it now. Your right! Thank you for the report and fix. I've pushed it to the repo.
Kailey Truscott - Community Team

User avatar
WelshPaul
Registered User
Posts: 244
Joined: Tue Aug 19, 2014 2:09 pm

Re: [3.2][BETA] Best Answer

Post by WelshPaul » Sat Feb 03, 2018 10:02 pm

I noticed that the issue is present when Enable best answer is set to No for that second subforum. Appears that that switching it to yes fixes the issue but if you don't want best answers enabled on that subforum it's a problem.

Anyway, my code above appears to fix the issue. Will test further and let you know if I encounter any further issues using my code change above...

Post Reply

Return to “Extensions in Development”

Who is online

Users browsing this forum: PWG-Extension, Thunder_one, v12mike and 21 guests