I'm trying to provide a list view with a related field included, and to allow the user to sort by the related field. The background here is a long list of parishes in a diocese, and their mass times - there will be hundreds of records, making sorting important.

This is my current template:

Code: Select all

<cms:template title='Mass Times' clonable='1' order='20'>
    <cms:editable name='masstime_parish' label='Parish' type='relation' masterpage='parish.php' has='one' orderby='page_name' order_dir='asc' order='10'/>
    <cms:editable name='masstime_day_of_week' label='Day of Week' opt_values='Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday' type='checkbox' order='20' />
    <cms:editable name='masstime_time' label='Time' type='datetime' only_time = '1' order='25' />
    <cms:editable name='masstime_start_date' label='Available from' type='datetime' format='dmy' fields_separator=',' default_time='@current' order='30'  />
    <cms:editable name='masstime_end_date' label='Available until' type='datetime'  format='dmy' fields_separator=',' default_time='@current' order='40'/>
    <cms:editable name='masstime_capacity' label='Capacity for Mass (if not parish capacity)' type='text' order='50' />
    <cms:config_list_view>
        <cms:field 'k_selector_checkbox' />
        <cms:field 'k_page_title' />
        <cms:field 'masstime_parish' header='Parish' sortable = '1' >
            <cms:related_pages 'masstime_parish' >
                <cms:show k_page_title />
           </cms:related_pages>
        </cms:field>
        <cms:field 'masstime_time' header='Time' sortable = '1'>
            <cms:date format='h:i a' masstime_time />
        </cms:field>
        <cms:field 'k_actions' />
        </cms:config_list_view>
</cms:template>

The display is as expected - I have the page title, then the parish name, then the time. I can sort by page title and by time, both ascending and descending.

But if I sort on the parish, the list is sorted once - I'm not clear in what order as it's neither ascending nor descending order of parish names (possibly the order in which they were entered?). Clicking on the heading to sort in the other direction does nothing. (Screenshot attached)

Is full sorting by a related field name achievable?

[trendoman]

Hundreds of records is not much. I would've used DataTable replacement for list-view to allow instant filtering of rows by any character and instant sorting by any column, of course with instant pagination. I had published all necessary files before and only minor tweaking would be needed.

Thanks, @trendoman - -I've found viewtopic.php?f=8&t=11739 and bookmarked it for future reference.

'Hundreds' was something of an understatement, in fact - if this thing gets adopted for use the eventual application will be for around 4000 records, so it's good to know the datatable can handle that many well.

[trendoman]

4000 records, so it's good to know the datatable can handle that many well.

I must clarify myself after you published more realistic number. Datatable can handle any amount of pages via different approaches - one is "DT builds over existing HTML from a single page". In my post I have used this approach and there was one single big list-view without pagination. I used cms:config_list_view limt='/total-number-of-pages-in-template/'. Couch outputs all rows on a single page that DT converts into its own dt-table before user could notice it (I took care of "jumping" in posted scripts). Hundreds of pages is what can be safely managed here. Why? Because above maybe 500-1000 rows, it takes a noticeable amount of time to process HTML rows into DT-view, since DT does it via JS, this slows down browser. It needs to be re-tested, of course. More importantly, using related_pages tag for 4000 cloned pages one by one (as in your request) will slow things considerably too!

There is an awesome server-side approach for building DT, which is "DT builds first several pages from generated HTML and requests other pages in batch when requested". This approach is best but I did not have anyone asking me to implement it the right way. Search and filtering must work on serverside in such case, because DT does not have complete set of data online and only takes care of showing what comes back from server. Any possible number of pages can work like this.

[KK]

@daldred, to keep the records straight - it is indeed not possible to sort the admin listing based on a relation field.

So, we'll have to look for alternatives.
@trendoman's suggestion is one. As one more, you may try the following (or perhaps, @trendoman, this could be combined with the 'server-side approach for building DT' you mentioned?) -

The idea is to have an additional type 'text' field complementing the existing type 'relation'.
When the page is saved, we find the page selected in the relation and insert its title into the text field.
Now, in the listing, instead of trying to use the relation field for sorting, we use the text field instead.

If that makes sense, here is one way of doing it -
1. Add a text field just after the relation (named in this sample as 'masstime_parish_title') -

Code: Select all

<cms:editable name='masstime_parish' label='Parish' type='relation' masterpage='rel/courses.php' has='one' orderby='page_name' order_dir='asc' order='10'/>
<cms:editable name='masstime_parish_title' type='text' order='11' />

2. Now add the following as <cms:config_form_view> -

Code: Select all

<cms:template title='Mass Times' clonable='1' order='20'>
    ..
    ..
    <cms:config_form_view>
        <cms:persist
            masstime_parish_title="<cms:php>echo( my_set_parish_title() );</cms:php>"
        />
    </cms:config_form_view>
    ..
    ..
</cms:template>

As you can see, when the page is saved we are configuring the template to call a PHP function named my_set_parish_title() and using its value to set the text field 'masstime_parish_title'.

So we'll also need to add the above mentioned PHP function to our couch/addons/kfunctions.php file. Copy the following code and paste it into your kfunctions.php -

Code: Select all

function my_set_parish_title(){

    $field_name = 'masstime_parish';

    ///
    global $FUNCS, $PAGE, $DB;

    $title = '';
    if( array_key_exists($field_name, $PAGE->_fields) ){
        $vals = $PAGE->_fields[$field_name]->items_selected;
        if( is_array($vals) ){
            $page_id = $vals[0];
            if( $FUNCS->is_non_zero_natural($page_id) ){
                $page_id = (int)$page_id;

                $rs = $DB->select( K_TBL_PAGES, array('page_title', 'page_name'), "id='" . $DB->sanitize( $page_id ). "'" );
                if( count($rs) ){
                    $title = ( $rs[0]['page_title'] )? $rs[0]['page_title'] : $rs[0]['page_name'];
                }
            }
        }
    }
    return $title;
}

At this point, test and see that whenever you save a page, Couch automatically fills the text field using the value from the relation dropdown.

If that now working ok, we can hide the text field as there is no need to display duplicate values to the users. To do that, modify the <cms:config_form_view> we added above as follows -

Code: Select all

<cms:template title='Mass Times' clonable='1' order='20'>
    ..
    ..
    <cms:config_form_view>
        <cms:persist
            masstime_parish_title="<cms:php>echo( my_set_parish_title() );</cms:php>"
        />

        <cms:field 'masstime_parish_title' hide='1' />
    </cms:config_form_view>
    ..
    ..
</cms:template>

3. Finally, we can now use the text field instead of the relation field on the list-view with sorting enabled.
Remove the entry for the relation field you currently have in <cms:config_list_view> and add the following -

Code: Select all

<cms:config_list_view>
    ..
    ..
    <cms:field 'masstime_parish_title' header='Parish' sortable='1' />
    ..
    ..
</cms:config_list_view>

And that should be it.

Please try and let me know if this helps.

[trendoman]

KK, that php function, perhaps, is a bit too much for the issue at hand but certainly opens up possibilities for things that otherwise would be too complex in other use-cases. Let me humbly suggest a jQuery approach for the Part I of this integral solution i.e. to avoid server-side processing without extra request to database.

Code copies the chosen value to the hidden textfield and hides the field. Parts II and III are left untouched, of course.

[KK]

@trendoman, yup, it is a perfectly valid approach.

[trendoman]

@trendoman, yup, it is a perfectly valid approach.

It is a sophisticated clever approach to replace fetching relation everytime with always-available text value. Kudos to your idea!

I must respect the '--Select-- =-' option and make a change to my code as follows.

Code: Select all

var title = $(this).find(":selected").not("[value=-]").text() || '';

@trendoman, @kk - thanks for your input on this.

I came back to the topic at this stage, having reached my 'improve the admin panel' stage of working on the site, and found the post-datatables discussion which I hadn't seen until then!

I've used the solution suggested by KK, with Trendoman's jQuery modification.