1. REGISTRATION REQUIREMENTS:

    Register with your xenforo.com Username then start a conversation at xenforo.com w/Bob and provide the following:
    1. Your XenForo License Validation Token
    2. The Domain Name associated with the License
    NOTE: Your account will be validated once ALL requirements are verified/met. Thank you for your patience.

Using Custom Fields in Pages

Discussion in 'Showcase Support' started by MattW, Aug 15, 2013.

  1. MattW

    MattW Server Admin AMS Premium RMS Premium UBS Premium Showcase Premium

    In this example, I'll explain how I accessed my custom fields to used them to make a table using Google Charts API

    OK, so the first thing you are going to need to do is create your own Model to expose the data required.

    In my example, I created a Garage folder inside library. You will then need a ControllerPublic folder

    /library/Garage/ControllerPublic

    The file I created was called Garage.php, and this contains the functions needed.

    The class used is Garage_ControllerPublic_Garage

    I then have 3 functions used to get the various information I'm using (3 as I use this file for 3 different pages)

    PHP:
    <?php
    class Garage_ControllerPublic_Garage
    {
            public static function 
    getPowerBoard(XenForo_ControllerPublic_Abstract $controllerXenForo_ControllerResponse_Abstract &$response)
            {
                    
    $db XenForo_Application::getDb();
           
                    
    $power $db->fetchAll("
                    SELECT
                            xf_nflj_showcase_custom_field_value.*,
                            xf_nflj_showcase_item.item_id,
                            xf_nflj_showcase_item.user_id,
                            xf_nflj_showcase_item.item_name,
                            xf_user.user_id,
                            xf_user.username
                    FROM xf_nflj_showcase_custom_field_value
                    LEFT JOIN xf_nflj_showcase_item ON
                            (xf_nflj_showcase_custom_field_value.item_id = xf_nflj_showcase_item.item_id)
                    LEFT JOIN xf_user ON
                            (xf_nflj_showcase_item.user_id = xf_user.user_id)
                    WHERE xf_nflj_showcase_custom_field_value.field_id =  'bhp'
                    AND xf_nflj_showcase_custom_field_value.field_value !=  ''
                    ORDER BY xf_nflj_showcase_custom_field_value.field_value DESC
                    "
    );

                    
    $response->params['power'] = $power;
            }

            public static function 
    getCategory(XenForo_ControllerPublic_Abstract $controllerXenForo_ControllerResponse_Abstract &$response)
            {
                    
    $db XenForo_Application::getDb();

                    
    $category $db->fetchAll("
                    SELECT
                            category_name,
                            item_count
                    FROM xf_nflj_showcase_category
                    "
    );

                    
    $response->params['category'] = $category;
            }

            public static function 
    getQuarterMile(XenForo_ControllerPublic_Abstract $controllerXenForo_ControllerResponse_Abstract &$response)
            {
                    
    $db XenForo_Application::getDb();

                    
    $power $db->fetchAll("
                    SELECT
                            xf_nflj_showcase_custom_field_value.*,
                            xf_nflj_showcase_item.item_id,
                            xf_nflj_showcase_item.user_id,
                            xf_nflj_showcase_item.item_name,
                            xf_user.user_id,
                            xf_user.username
                    FROM xf_nflj_showcase_custom_field_value
                    LEFT JOIN xf_nflj_showcase_item ON
                            (xf_nflj_showcase_custom_field_value.item_id = xf_nflj_showcase_item.item_id)
                    LEFT JOIN xf_user ON
                            (xf_nflj_showcase_item.user_id = xf_user.user_id)
                    WHERE xf_nflj_showcase_custom_field_value.field_id =  '14_Mile'
                    AND xf_nflj_showcase_custom_field_value.field_value !=  ''
                    ORDER BY xf_nflj_showcase_custom_field_value.field_value DESC
                    "
    );

                    
    $response->params['power'] = $power;
            }
       
    }
    So taking the function getPowerBoard this is what will be used to pull the power figures from my custom field bhp

    basic_info.PNG general_options.PNG options.PNG

    The below is the query and response we are going to pass back to the template

    PHP:
    $power $db->fetchAll("
                    SELECT
                            xf_nflj_showcase_custom_field_value.*,
                            xf_nflj_showcase_item.item_id,
                            xf_nflj_showcase_item.user_id,
                            xf_nflj_showcase_item.item_name,
                            xf_user.user_id,
                            xf_user.username
                    FROM xf_nflj_showcase_custom_field_value
                    LEFT JOIN xf_nflj_showcase_item ON
                            (xf_nflj_showcase_custom_field_value.item_id = xf_nflj_showcase_item.item_id)
                    LEFT JOIN xf_user ON
                            (xf_nflj_showcase_item.user_id = xf_user.user_id)
                    WHERE xf_nflj_showcase_custom_field_value.field_id =  'bhp'
                    AND xf_nflj_showcase_custom_field_value.field_value !=  ''
                    ORDER BY xf_nflj_showcase_custom_field_value.field_value DESC
                    "
    );

                    
    $response->params['power'] = $power;
    The two important lines of that are

    WHERE xf_nflj_showcase_custom_field_value.field_id = 'bhp'
    AND xf_nflj_showcase_custom_field_value.field_value != ''

    which is the name of the custom field, and the AND statement makes it only pull results where there is data (ie not empty)

    For the XenForo page, you want to create a page using a PHP callback to the Class and Function

    call_back.PNG

    This will then allow you access to the response params

    The HTML used to make the page, which works with the Google Charts API

    HTML:
    <style type="text/css">
    .outercontainer {
    border: 1px solid #BAC2C7;
    padding: 10px;
    margin: 0 auto;
    background-color: #F4F6F7 !important;
    }
    </style>
    <script type='text/javascript' src='https://www.google.com/jsapi'></script>
        <script type='text/javascript'>
          google.load('visualization', '1', {packages:['table']});
          google.setOnLoadCallback(drawTable);
          function drawTable() {
            var data = new google.visualization.DataTable();
            data.addColumn('string', 'Member');
            data.addColumn('string', 'Vehicle');
            data.addColumn('string', 'Power');
            data.addRows([
              <xen:foreach loop="$power" value="$power">
              ['<xen:username user="$power" />', '<a href="{xen:link showcase, $power}">{xen:jsescape {xen:raw $power.item_name}, single}</a>', '{$power.field_value} {$power.field_id}'],
              </xen:foreach>
            ]);
            var table = new google.visualization.Table(document.getElementById('table_div'));
            table.draw(data, {showRowNumber: true, allowHtml: true});
          }
        </script>
    
    <div class="outercontainer">
    <div class="baseHtml">
    <div id='table_div'></div>
    </div>
    </div>
    You want to loop through the results, with the below code:

    <xen:foreach loop="$power" value="$power">
    ['<xen:username user="$power" />', '<a href="{xen:link showcase, $power}">{xen:jsescape {xen:raw $power.item_name}, single}</a>', '{$power.field_value} {$power.field_id}'],
    </xen:foreach>

    This links the user back to their profile, their vehicle to their showcase item, and pull the power value from the custom fields. You can see the $power.xxxx_xxxx which relates to the response and the specific field, ie $power.item_name

    Hope this helps, any questions, please ask.
     
    Ludachris, tajhay, Bob and 1 other person like this.
  2. smartpixels

    smartpixels New Member Showcase

    Awesome. Going to try this tomorrow.
     
  3. woei

    woei Member Showcase

    It would be really nice if this becomes a function of showcase. Lot's of car/motor forums would use this feature. Also with the 1/4 mile rankings. Many thanks for this how-to. I'm going to implement this to :) Will post my link after it's final and in production
     
  4. woei

    woei Member Showcase

    I have a question, why is the google maps api called for the powerboard? I have got it running in my development environment. We measure in PK instead of bhp. Also we sprint over a distance of 150 meters. The sprint times pages haven't completed yet. @MattW Can you give the example code of the quartermile page? I'm really bad at queries and so.

    Also is this safe to use a , instead of a . in the values? So I have adapted the regex to use , instead of . (Resulting in 17,80 PK instead of 17.80 PK). No chance for XSS exploits or malicious code to be inserted?
     
  5. Bob

    Bob Developer Staff Member

    Just a heads up that custom fields have changed slightly in SC 1.4.0 which DOES effect this. @MattW is one of the closed beta testers, so he'll be able to make the adjustments needed to this howto prior to public beta release.
     
  6. Ludachris

    Ludachris Active Member AMS Premium Showcase Premium

    Ha, I had this page bookmarked to use as I build out my Timeslips page. Question - is the regular expression in the Power field supposed to match 000.000 decimal number? If so, cool!
     
  7. Ludachris

    Ludachris Active Member AMS Premium Showcase Premium

    Alright, I ran through these instructions and am creating the Page node and I'm getting the following error:

    Callback Garage_ControllerPublic_Garage::getTimeSlips is invalid (Invalid Class)

    What did I miss?
     
  8. Ludachris

    Ludachris Active Member AMS Premium Showcase Premium

    Duh, I used different naming scheme for the DIR. Changed it and it worked. Nevermind me.
     
    Bob likes this.
  9. Ludachris

    Ludachris Active Member AMS Premium Showcase Premium

    Got it working! Now, some questions:

    - The table takes several seconds to load for only 16 records. Is the Google Charts API the best solution to display this?
    - Is this table going to be paginated?
    - Is there a way to display the total number of rows being displayed?
    - How would I display multiple custom field values in the table? It looks like it's only set to display one custom field.
    - How tough would it be to add Image Count, Discussion Count, and a thumbnail for each item? I would just add some lines to the query, right?
     
  10. Bob

    Bob Developer Staff Member

    Probably best to contact Matt directly as I don't think he checks this site on a daily basis.

    As for the Discussion count, you will need an additional query to get that information as its not stored in showcase. You'd also need to properly fetch and prepare items in order to get attachment data. (See the prepareItems method in the Item Model).
     
  11. Ludachris

    Ludachris Active Member AMS Premium Showcase Premium

    Gotcha. I'll look through that method - should keep me busy for a while. And I'll send Matt a note, I think I have his email.

    On a side note, I got the pagination working with the Google API, but the table still loads slow. Wondering if the Google API is the best solution.
     
  12. woei

    woei Member Showcase

    Using a 3rth party will always be slower. I don't have the skills to do this myself, but loading something from your own db would be faster. I'm also using this, but it has some minor flaws. If you delete a showcase item, the query still gets the info from that item.

    See my page: http://scooterforum.net/pages/powerboard/

    Nr. 3 is a deleted showcase item.
     
  13. Bob

    Bob Developer Staff Member

    That's because he's using a basic query instead of the full fetch process which will take things like category permissions and viewing user permission's into consideration. You can add another AND to the where clause to only pull items based on the item_state (as you don't want 'draft', 'moderated' and 'deleted' items displaying.

    AND xf_nflj_showcase_item.item_state = 'visible'
     
    MattW likes this.
  14. Ludachris

    Ludachris Active Member AMS Premium Showcase Premium

    Bob, would it just be a matter of copying the functions for the full fetch process from the Item.php model and replacing Matt's functions above in the Garage.php file that is created using the above example? Or maybe just calling those functions from the new file? I'd imagine that would allow you to bring in all custom fields too, in addition to the item state and counts.
     
  15. Bob

    Bob Developer Staff Member

    yes and no. You can't just "copy" something, because that something is specifically setup for certain complete process. There are "conditions" and "fetchOptions" that have to be set for the fetch process (some are based on category permissions which require several category fetch processes, and some are based on options [both category and global]).

    As far as custom fields go, custom fields are now stored in CACHE fields in the item itself. You don't have to query the item and then join the field tables anymore. What you need to do tho is grab the Item with the full category record and THEN run the results through the prepareItems method (as that handles the preparing of the custom fields cache). THEN (and this is probably the most important) the VIEW needs to be registered. I've registered all of the showcase views, but if you are doing this outside of showcase, you are going to have to edit the core xf view file and register it. If a view file doesn't exist for that "area" of xf (which is highly possible), you will have to create one and register it.
     
  16. Ludachris

    Ludachris Active Member AMS Premium Showcase Premium

    Hey @MattW were you planning on updating the tutorial so that it's more relevant to the newer version of showcase, as Bob described in the reply above this one? I keep fumbling around trying to do it myself and am wasting a lot of time and effort. Hoping maybe you could shine some light on how to do it the right way.
     
  17. Ludachris

    Ludachris Active Member AMS Premium Showcase Premium

    @Bob B - I just had a thought. What if instead of creating a separate page like Matt has done, I just make new sorting tabs within the List view of showcase? The new tabs would be Fastest Timeslip and Highest HP. These new tabs would show on the index_list and category_list:
    http://www.dsmtuners.com/dsm-profiles/

    This would keep it all within showcase and should make it easier to display custom fields with less coding. Would just have to create a couple new sorting options.

    I would then just have to figure out a clean way to add all of the filtering options I used to have on my old pages (used to be able to filter Timeslips and Dyno sheets by AWD, FWD, Manual, Automatic, Turbo Type, Make/Model, and other criteria). Maybe I could customize the list view and add those filtering options under those tabs.

    I like the idea of keeping these views all within showcase anyway, as it really is just a list of showcase items like the other lists.
     
  18. Bob

    Bob Developer Staff Member

    I can't add something into the addon that relies on a specific custom field(s).
     
  19. Ludachris

    Ludachris Active Member AMS Premium Showcase Premium

    I wouldn't expect you to. I'm just looking for the best and most efficient way to replace what I had on my old site for the 1/4 Mile list and the Dyno list. I'm fully expecting this to be a custom modification/page added to Showcase. Maybe it can be done as a Widget... I don't know. I just know I have to get these sections re-built at some point soon. I just need some help trying to figure out the best way to approach it.
     
  20. Bob

    Bob Developer Staff Member

    The way Matt is doing it is the best way to do it IMO. The only thing wrong with his example is that he is not taking item_state into consideration as it will pull draft, visible, moderated and deleted items. You can fix that by adding in the and statement that I posted above.

    I DO, do custom work yano ;) I just know that you like doing things on your own and learning. THIS IMO is the best way to learn. Take what Matt did, look at the Showcase Code (reverse engineer it) and go from there.
     
    alfa1 likes this.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.