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

Custom Field SQL Query

Discussion in 'XenForo Development Discussions' started by Quiver, May 14, 2013.

  1. 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.
     
  2. Quiver

    Quiver Active Member

  3. Chris D

    Chris D XenForo Developer Staff Member

    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
     
  4. Quiver

    Quiver Active Member

    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. :( :(
     
  5. Quiver

    Quiver Active Member

    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:
     
  6. Quiver

    Quiver Active Member

    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.
     
  7. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  8. Quiver

    Quiver Active Member

    I've sent you a PM. ;)
    I still need the SQL query. Anyone?
     
  9. Chris D

    Chris D XenForo Developer Staff Member

    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 ));
     
  10. Quiver

    Quiver Active Member

    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
     
  11. Quiver

    Quiver Active Member

    Oh, u.user_id I think I've spotted the mistake. :p
     
  12. Quiver

    Quiver Active Member

    Okay I tried (notice the bits in bold):

    But it didn't like it. :confused:
    Works fine without AS banner.
    But I need to make $XTopUser.banner for use in templates.
     
  13. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  14. Quiver

    Quiver Active Member

    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:
     
  15. Chris D

    Chris D XenForo Developer Staff Member

    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 ));
     
  16. Quiver

    Quiver Active Member

    Thanks, that works. It works in the twistedpromotion_topuser template.
    Which I link the forum_list to the twistedpromotion_topuser template...
    It works fine from forum_list as a result.
    But why doesn't it work when I call {$XTopUser.banner} from ad_header template?
     
  17. Chris D

    Chris D XenForo Developer Staff Member

    Not all parameters are available to all templates. Almost no parameters are available to ad_ templates because they are primarily designed for ads.
     
  18. Quiver

    Quiver Active Member

    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?
     
  19. Quiver

    Quiver Active Member

    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.....
     
  20. Chris D

    Chris D XenForo Developer Staff Member

    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']
     

Share This Page