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

Custom Field SQL Query

Quiver

Active member
#1
Okay, well I thought I had this working but I didn't.
What I want to do is extend this:

Code:
<?php
class TwistedPromotion_TopUser_Model_XTopUser extends Xenforo_Model_User
{
        public static function TwistedPromotion_TopUserArray() {
                $db = XenForo_Application::get('db');
                $userModel = XenForo_Model::create('XenForo_Model_User');
                $TwistedPromotion_TopUser = array();
                $numShown = "1";
                $long = "3600";
 
$TTArray = $db->fetchAll( $db->limit( "SELECT u.username AS username, u.user_id, up.homepage AS homepage, COUNT( * ) AS totalPosts FROM xf_post AS p LEFT JOIN xf_user AS u ON ( u.user_id = p.user_id ) LEFT JOIN xf_user_profile AS up ON ( up.user_id = p.user_id ) WHERE post_date > UNIX_TIMESTAMP()-$long GROUP BY p.user_id ORDER BY totalPosts DESC", $numShown ));
                if(sizeof($TTArray) != 0) {
 
                foreach($TTArray as $TTX) {
                        $TTIds[] = $TTX['user_id'];
                }
                $userObjs = $userModel->getUsersByIds($TTIds,array());
                foreach($TTArray as $TT) {
                  if ($TT['user_id'])
      {
      $hrefx = XenForo_Link::buildPublicLink('toposters', $TT);
 
                  }
$TwistedPromotion_TopUser[] = array("user" => $userObjs[$TT['user_id']], "username" => $TT['username'], "totalPosts" => $TT['totalPosts'], "homepage" => $TT['homepage']);
 
                }
                }
 
                if(count($TwistedPromotion_TopUser))
    {
    return $TwistedPromotion_TopUser;
    }
                }
 
        }
So that it also fetches the custom user field (the ID of the field in question is 'banner').
Because, the above addon displays the top poster of the last hour, and that user's homepage.
I want it to also display that user's custom field...which, in this case, happens to be a banner.
 

Chris D

XenForo developer
Staff member
#3
You can't extend an existing model like that.

You need to set up a Code Event Listener for the load_class_model event.

Then your file class should be:

class TwistedPromotion_TopUser_Model_XTopUser extends XFCP_TwistedPromotion_TopUser_Model_XTopUser
 

Quiver

Active member
#4
You can't extend an existing model like that.

You need to set up a Code Event Listener for the load_class_model event.

Then your file class should be:

class TwistedPromotion_TopUser_Model_XTopUser extends XFCP_TwistedPromotion_TopUser_Model_XTopUser
Okay, now my head is spinning like spaghetti. Could you please explain in newbie language? :confused:
I've really got so far with this addon I'll be kind of frustrated if I can't manage to finish it. :( :(
 

Quiver

Active member
#5
I need the database query to fetch the custom user field of the top poster of the last hour first of all.
Once I can get it to do that I can worry about Code Event Listeners. :confused:
 

Quiver

Active member
#6
You can't extend an existing model like that.

You need to set up a Code Event Listener for the load_class_model event.

Then your file class should be:

class TwistedPromotion_TopUser_Model_XTopUser extends XFCP_TwistedPromotion_TopUser_Model_XTopUser
FYI, I have a controller and a listener that do this already. I just didn't paste the code here.
My addon works as is. I'd now like to make it work with the custom field ('banner') as well.
 

Chris D

XenForo developer
Staff member
#7
What I am saying is, your query is probably correct, I haven't got as far as reviewing it yet.

But fundamentally the way you're trying to extend the User Model is not going to work:

PHP:
class TwistedPromotion_TopUser_Model_XTopUser extends Xenforo_Model_User
That will either break existing add-ons, not work at all, or potentially overwrite default functionality.

In your Listener file you need to set up something like this:

PHP:
public static function extendUserModel($class, array &$extend)
{
	if ($class == 'XenForo_Model_User')
	{
		$extend[] = 'TwistedPromotion_TopUser_Model_XTopUser';
	}
}
Then in the Admin CP you should set up a Code Event Listener that listens to the load_class_model event and points to that function above.

Finally, change this:

PHP:
class TwistedPromotion_TopUser_Model_XTopUser extends Xenforo_Model_User
to this:

PHP:
class TwistedPromotion_TopUser_Model_XTopUser extends XFCP_TwistedPromotion_TopUser_Model_XTopUser
That may make things work, or it may only be part of the problem. But definitely do that first.
 

Quiver

Active member
#8
What I am saying is, your query is probably correct, I haven't got as far as reviewing it yet.

But fundamentally the way you're trying to extend the User Model is not going to work:

PHP:
class TwistedPromotion_TopUser_Model_XTopUser extends Xenforo_Model_User
That will either break existing add-ons, not work at all, or potentially overwrite default functionality.

In your Listener file you need to set up something like this:

PHP:
public static function extendUserModel($class, array &$extend)
{
if ($class == 'XenForo_Model_User')
{
$extend[] = 'TwistedPromotion_TopUser_Model_XTopUser';
}
}
Then in the Admin CP you should set up a Code Event Listener that listens to the load_class_model event and points to that function above.

Finally, change this:

PHP:
class TwistedPromotion_TopUser_Model_XTopUser extends Xenforo_Model_User
to this:

PHP:
class TwistedPromotion_TopUser_Model_XTopUser extends XFCP_TwistedPromotion_TopUser_Model_XTopUser
That may make things work, or it may only be part of the problem. But definitely do that first.
I've sent you a PM. ;)
I still need the SQL query. Anyone?
 

Chris D

XenForo developer
Staff member
#9
I still think it's important to get the basics right first but to answer your original question, this includes the changed query:

EDIT: Missed a bit, sorry:

PHP:
		$TTArray = $db->fetchAll($db->limit(
			"SELECT u.username AS username,
				u.user_id, up.homepage AS homepage,
				ufv.*,
				COUNT( * ) AS totalPosts
			FROM xf_post AS p
			LEFT JOIN xf_user AS u ON
				( u.user_id = p.user_id )
			LEFT JOIN xf_user_profile AS up ON
				( up.user_id = p.user_id )
			LEFT JOIN xf_user_field_value AS ufv ON
				(ufv.user_id = u.user_ud)
			WHERE post_date > UNIX_TIMESTAMP()-$long
			AND ufv.field_id = \'banner\'
			GROUP BY p.user_id ORDER BY totalPosts DESC
		", $numShown ));
 

Quiver

Active member
#10
I still think it's important to get the basics right first but to answer your original question, this includes the changed query:

EDIT: Missed a bit, sorry:

PHP:
 $TTArray = $db->fetchAll($db->limit(
"SELECT u.username AS username,
u.user_id, up.homepage AS homepage,
ufv.*,
COUNT( * ) AS totalPosts
FROM xf_post AS p
LEFT JOIN xf_user AS u ON
( u.user_id = p.user_id )
LEFT JOIN xf_user_profile AS up ON
( up.user_id = p.user_id )
LEFT JOIN xf_user_field_value AS ufv ON
(ufv.user_id = u.user_ud)
WHERE post_date > UNIX_TIMESTAMP()-$long
AND ufv.field_id = \'banner\'
GROUP BY p.user_id ORDER BY totalPosts DESC
", $numShown ));
Server Error

Mysqli prepare error: Unknown column 'u.user_ud' in 'on clause'
  1. Zend_Db_Statement_Mysqli->_prepare() in Zend/Db/Statement.php at line 115
  2. Zend_Db_Statement->__construct() in Zend/Db/Adapter/Mysqli.php at line 381
  3. Zend_Db_Adapter_Mysqli->prepare() in Zend/Db/Adapter/Abstract.php at line 478
  4. Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 734
  5. Zend_Db_Adapter_Abstract->fetchAll() in TwistedPromotion/TopUser/Model/XTopUser.php at line 25
  6. TwistedPromotion_TopUser_Model_XTopUser::TwistedPromotion_TopUserArray() in TwistedPromotion/TopUser/Controller/Public.php at line 10
  7. TwistedPromotion_TopUser_Controller_Public->actionIndex() in Arcade/Extend/ControllerPublic/Index.php at line 23
  8. Arcade_Extend_ControllerPublic_Index->actionIndex() in XenForo/FrontController.php at line 310
  9. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
  10. XenForo_FrontController->run() in /home/<my username>/public_html/index.php at line 13
 

Quiver

Active member
#12
Okay I tried (notice the bits in bold):

$TTArray = $db->fetchAll($db->limit(
"SELECT u.username AS username,
u.user_id, up.homepage AS homepage,
ufv.* AS banner,
COUNT( * ) AS totalPosts
FROM xf_post AS p
LEFT JOIN xf_user AS u ON
( u.user_id = p.user_id )
LEFT JOIN xf_user_profile AS up ON
( up.user_id = p.user_id )
LEFT JOIN xf_user_field_value AS ufv ON
(ufv.user_id = u.user_id)
WHERE post_date > UNIX_TIMESTAMP()-$long
AND ufv.field_id = 'banner'
GROUP BY p.user_id ORDER BY totalPosts DESC
", $numShown ));
But it didn't like it. :confused:
Works fine without AS banner.
But I need to make $XTopUser.banner for use in templates.
 

Chris D

XenForo developer
Staff member
#13
Sorry about the previous typo :D

You don't need to use AS banner

$XTopUser.field_value will contain the banner field_value if it is set.
 

Quiver

Active member
#14
Sorry about the previous typo :D

You don't need to use AS banner

$XTopUser.field_value will contain the banner field_value if it is set.
So how will I call this from a template?
Notice homepage is AS up.homepage AS homepage and called by $XTopUser.homepage?
How do I call upon the banner? :confused:
 

Chris D

XenForo developer
Staff member
#15
How I've just said... {$XTopUser.field_value} but to make it consistent with how you're calling the homepage, use this updated query:

PHP:
		$TTArray = $db->fetchAll($db->limit(
			"SELECT u.username AS username,
				u.user_id, up.homepage AS homepage,
				ufv.field_value AS banner,
				COUNT( * ) AS totalPosts
			FROM xf_post AS p
			LEFT JOIN xf_user AS u ON
				( u.user_id = p.user_id )
			LEFT JOIN xf_user_profile AS up ON
				( up.user_id = p.user_id )
			LEFT JOIN xf_user_field_value AS ufv ON
				(ufv.user_id = u.user_ud)
			WHERE post_date > UNIX_TIMESTAMP()-$long
			AND ufv.field_id = \'banner\'
			GROUP BY p.user_id ORDER BY totalPosts DESC
		", $numShown ));
 

Quiver

Active member
#16
How I've just said... {$XTopUser.field_value} but to make it consistent with how you're calling the homepage, use this updated query:

PHP:
 $TTArray = $db->fetchAll($db->limit(
"SELECT u.username AS username,
u.user_id, up.homepage AS homepage,
ufv.field_value AS banner,
COUNT( * ) AS totalPosts
FROM xf_post AS p
LEFT JOIN xf_user AS u ON
( u.user_id = p.user_id )
LEFT JOIN xf_user_profile AS up ON
( up.user_id = p.user_id )
LEFT JOIN xf_user_field_value AS ufv ON
(ufv.user_id = u.user_ud)
WHERE post_date > UNIX_TIMESTAMP()-$long
AND ufv.field_id = \'banner\'
GROUP BY p.user_id ORDER BY totalPosts DESC
", $numShown ));
Thanks, that works. It works in the twistedpromotion_topuser template.
Which I link the forum_list to the twistedpromotion_topuser template...
Setup:
1. Upload all files from the "upload" folder to your library folder.
2. Open template "forum_list" -> find:

</xen:sidebar>

Add Above:

<xen:include template="twistedpromotion_topuser" />
It works fine from forum_list as a result.
But why doesn't it work when I call {$XTopUser.banner} from ad_header template?
 

Chris D

XenForo developer
Staff member
#17
Not all parameters are available to all templates. Almost no parameters are available to ad_ templates because they are primarily designed for ads.
 

Quiver

Active member
#18
Not all parameters are available to all templates. Almost no parameters are available to ad_ templates because they are primarily designed for ads.
Ah, that is very annoying as I'd like to display the said banner as an advert.
Because that's what it effectively is. <a href="{XTopUser.homepage}"><img src="{XTopUser.banner}" alt="{XTopUser.username}"></a>
Any ideas as to what template I can use, other than an advert template?
Or even better, how I can somehow force (aka tweak) it to do what I want?
 

Quiver

Active member
#19
Actually, I just discovered something. This trying to get the custom banner thing has totally broken my addon. Now it will only display the top poster if that individual has actually entered a banner URL. If they haven't, but they're still the top poster, it won't display them. So I've had to revert back to my original addon (minus the banner feature), which works perfectly. :( A real shame.....
 

Chris D

XenForo developer
Staff member
#20
Hmm thinking about it, we've over complicated this. There's actually loads of ways to do this.

In your PHP you can do this:

$userFieldValues = $userModel->getUserFieldValues(*InsertUserIdHere*);

You can then access that user's banner from $userFieldValues['banner']