• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

need to join table with xf_thread can i ?

marioman

Active member
#1
hi,

i have new table in database with col thread_id

in forum_view i need to get this table information without new query

is there sql hook like vbulletin ?

i see that query come after handle some conditions in XenForo_Model_Thread with this const

PHP:
const FETCH_USER = 0x01;
    const FETCH_FORUM = 0x02;
    const FETCH_FIRSTPOST = 0x04;
    const FETCH_AVATAR = 0x08;
    const FETCH_DELETION_LOG = 0x10;
    const FETCH_FORUM_OPTIONS = 0x20;
is there away to add my table to threads query ?
 

Fuhrmann

Well-known member
#2
hi,

i have new table in database with col thread_id

in forum_view i need to get this table information without new query

is there sql hook like vbulletin ?

i see that query come after handle some conditions in XenForo_Model_Thread with this const

PHP:
const FETCH_USER = 0x01;
    const FETCH_FORUM = 0x02;
    const FETCH_FIRSTPOST = 0x04;
    const FETCH_AVATAR = 0x08;
    const FETCH_DELETION_LOG = 0x10;
    const FETCH_FORUM_OPTIONS = 0x20;
is there away to add my table to threads query ?
I think if you want to get data from your own table, you'll need to do a query...
 

Jake Bunce

XenForo moderator
Staff member
#3
To add a join to the existing thread query you will need to extend the thread model. The joins are handled by this function:

XenForo_Model_Thread::prepareThreadFetchOptions

You can see there is a single fetchOption called 'join' which is accompanied by consts:

Code:
		if (!empty($fetchOptions['join']))
		{
			if ($fetchOptions['join'] & self::FETCH_USER)
			{
				$selectFields .= ',
					user.*, IF(user.username IS NULL, thread.username, user.username) AS username';
				$joinTables .= '
					LEFT JOIN xf_user AS user ON
						(user.user_id = thread.user_id)';
			}
			else if ($fetchOptions['join'] & self::FETCH_AVATAR)
			{
				$selectFields .= ',
					user.avatar_date, user.gravatar';
				$joinTables .= '
					LEFT JOIN xf_user AS user ON
						(user.user_id = thread.user_id)';
			}

			if ($fetchOptions['join'] & self::FETCH_FORUM)
			{
				$selectFields .= ',
					node.title AS node_title';
				$joinTables .= '
					INNER JOIN xf_node AS node ON
						(node.node_id = thread.node_id)';
			}

			if ($fetchOptions['join'] & self::FETCH_FORUM_OPTIONS)
			{
				$selectFields .= ',
					forum.*';
				$joinTables .= '
					INNER JOIN xf_forum AS forum ON
						(forum.node_id = thread.node_id)';
			}

			if ($fetchOptions['join'] & self::FETCH_FIRSTPOST)
			{
				$selectFields .= ',
					post.message, post.attach_count';
				$joinTables .= '
					INNER JOIN xf_post AS post ON
						(post.post_id = thread.first_post_id)';
			}

			if ($fetchOptions['join'] & self::FETCH_DELETION_LOG)
			{
				$selectFields .= ',
					deletion_log.delete_date, deletion_log.delete_reason,
					deletion_log.delete_user_id, deletion_log.delete_username';
				$joinTables .= '
					LEFT JOIN xf_deletion_log AS deletion_log ON
						(deletion_log.content_type = \'thread\' AND deletion_log.content_id = thread.thread_id)';
			}
		}
You will need to extend this function to add an appropriate join.

You can see how these joins are enabled by looking in the controller:

XenForo_ControllerPublic_Forum::actionIndex

Here are the highlights:

Code:
		$threadFetchOptions = array(
			'perPage' => $threadsPerPage,
			'page' => $page,

			'join' => XenForo_Model_Thread::FETCH_USER,
			'readUserId' => $visitor['user_id'],
			'postCountUserId' => $visitor['user_id'],

			'order' => $order,
			'orderDirection' => $orderDirection
		);

		...

		$threads = $threadModel->getThreadsInForum($forumId, $threadFetchConditions, $threadFetchOptions);
Multiple joins can be enabled like this:

Code:
		$threadFetchOptions = array(
			'perPage' => $threadsPerPage,
			'page' => $page,

			'join' => XenForo_Model_Thread::FETCH_USER | XenForo_Model_Thread::FETCH_FORUM,
			'readUserId' => $visitor['user_id'],
			'postCountUserId' => $visitor['user_id'],

			'order' => $order,
			'orderDirection' => $orderDirection
		);

		...

		$threads = $threadModel->getThreadsInForum($forumId, $threadFetchConditions, $threadFetchOptions);
 

marioman

Active member
#4
jake, you mean i must create model and extend it using listener (load_class_model)
then create new constant and overwrite prepareThreadFetchOptions action to add what i need ?

is that right ?
 

Jake Bunce

XenForo moderator
Staff member
#5
Yes, correct.

This simple addon I made shows how you can extend an existing class. It's good for reference if you have never done this before:

http://xenforo.com/community/threads/force-rich-code-tag.20317/

In this case when you write your own prepareThreadFetchOptions function it would be a good idea to first call on the original function to capture the return value, then amend it with your own stuff. Something like this:

Code:
	public function prepareThreadFetchOptions(array $fetchOptions)
	{
		// CALL ORIGINAL
		$retval = parent::prepareThreadFetchOptions($fetchOptions);

		// ADD YOUR OWN JOINS
		$retval['selectFields'] .= ', newtable.field';
		$retval['joinTables'] .= ' LEFT JOIN newtable ON (newtable.thread_id = thread.thread_id)';

		// RETURN ORIGINAL AND NEW JOINS
		return $retval;
	}
 

marioman

Active member
#6
i know about extends and overwrite old action by this way
i did it many times before with controller

your help is very useful to me, thanks