Custom Field SQL Query

Quiver

Quiver

Active member
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.
 
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
 
Chris Deeming said:
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
Click to expand...
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. :( :(
 
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:
 
Chris Deeming said:
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
Click to expand...
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.
 
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.
 
Chris Deeming said:
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.
Click to expand...
I've sent you a PM. ;)
I still need the SQL query. Anyone?
 
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 ));
 
Chris Deeming said:
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 ));
Click to expand...
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
 
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 ));
Click to expand...

But it didn't like it. :confused:
Works fine without AS banner.
But I need to make $XTopUser.banner for use in templates.
 
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.
 
Chris Deeming said:
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.
Click to expand...
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:
 
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 ));
 
Chris Deeming said:
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 ));
Click to expand...
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" />
Click to expand...
It works fine from forum_list as a result.
But why doesn't it work when I call {$XTopUser.banner} from ad_header template?
 
Not all parameters are available to all templates. Almost no parameters are available to ad_ templates because they are primarily designed for ads.
 
Chris Deeming said:
Not all parameters are available to all templates. Almost no parameters are available to ad_ templates because they are primarily designed for ads.
Click to expand...
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?
 
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.....
 
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']
 
You must log in or register to reply here.

Similar threads

ProCom
Custom Quick / Easy PHP Page - SQL Query / Display Data
Replies
2
Views
90
Korsch
Korsch
Azaly
  • Question Question
XF 2.2 Sql query time is long for xf_post
Replies
4
Views
1K
Azaly
Azaly
frm
  • Question Question
XF 2.1 SQL Query Loop for Custom User Fields
Replies
0
Views
383
frm
frm
Graham Smith
  • Question Question
XF 2.1 Struggling with an SQL Query
Replies
0
Views
301
Graham Smith
Graham Smith
Graham Smith
SQL Query
Replies
7
Views
2K
Graham Smith
Graham Smith
Back
Top Bottom