Sorting a related records output in an arbitrary order

17 posts by 3 authors in: Forums > CMS Builder
Last Post: December 3, 2020   (RSS)

By gkornbluth - August 19, 2020 - edited: August 19, 2020

Hi All,

Sorry, but I’m stumped again.

I’m using the related records plugin to assign performers to specific concerts from a master list of performers called 'musicans_listings' (sorted by last name).

Instead, I’d like to be able to sort the performers assigned to a particular concert in 'show lineup order' instead of the default 'alphabetical order'.

Any thoughts?

Thanks,

Jerry Kornbluth

Here’s what I have so far.

I've created a multi-value pillbox list field called 'performers' in my concert listings database called  'listen_live'. The field gets it’s List Options from the musicians_listings database using the 'num' field for values and the 'full_performer_or_group_name' field for labels.

At the top of my list viewer:
// load records from 'listen_live'
list($listen_liveRecords, $listen_liveMetaData) = getRecords(array(
'tableName' => 'listen_live',

));
// load records from 'musicians_lisings'
list($musicians_lisingsRecords, $musicians_lisingsMetaData) = getRecords(array(
'tableName' => 'musicians_lisings',
'loadUploads' => true,
'allowSearch' => false,
'orderBy' => 'concert_lineup_order DESC',
));

beta_lookupRelatedFields(array(
'table' => 'listen_live',
'recordList' => &$listen_liveRecords,
'fieldList' => array( 'performers' )

));
$listen_liveRecord = @$listen_liveRecords[0]; // get first record


In the body I use the following to list only the next concert:


<?php
list($listen_liveRecords, $listen_liveMetaData) = getRecords(array(
'tableName' => 'listen_live',
'where' => '((NOW() + INTERVAL 31 DAY) >= presentation_date AND presentation_date >= TIMESTAMP(CURDATE(), "00:00:00"))',

'orderBy'=> 'presentation_date ASC',
));

?>

And then this to pull the performers in that concert from the musicians_listings database:

<?php foreach (@$listen_liveRecords as $record): ?>
<tr><td>
<span class="alt_heading_font" style="font-size:2em;">
<?php echo $record['event_title'] ?><br />
</span>
</td></tr>
<tr>
<td>
<?php if (!empty($listen_liveRecord['performer'])): ?>
<span class="alt_text_font"><br>
Information About The Performers For This Concert Will Be Available Soon.</span>
<?php else: ?>
<?php foreach ($listen_liveRecord['performers'] as $performer): ?>
<span class="alt_heading_font">FEATURING:</span>

</td>
</tr><tr><td colspan="2"><hr class="active" /></td></tr>
<?php break ?>
<?php endforeach ?>
<!-- BEGIN PERFORMER LISTING CODE -->
<?php foreach ($listen_liveRecord['performers'] as $performer): ?>
<tr>
<td width="25%"><a href="<?php echo $common_informationRecord['master_url'] ?>/musicians_detail.php?<?php echo $performer['num'] ?>">
<?php foreach ($performer['list_page_image'] as $upload): ?>
<img style="border:hidden" src="<?php echo $upload['thumbUrlPath'] ?>" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" alt="" />
<?php endforeach ?>
</a></td>
<td><a href="<?php echo $common_informationRecord['master_url'] ?>/musicians_detail.php?<?php echo $performer['num'] ?>"><span class="sub_heading_font" style="text-decoration:underline"> <?php echo $performer['full_performer_or_group_name'] ?> </span> <br />
</a></td>
</tr>
<tr>
<td colspan="2"><hr class="active" /></td>
</tr>
<?php endforeach ?>
<!-- END PERFORMER LISTING CODE -->
<?php endif ?>

<?php endforeach ?>

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Carl - August 20, 2020

Hi Jerry,
I'm still going through your question.

I'm not sure if this is a bug or a misunderstanding on my part, but was this a typo?

<?php if (!empty($listen_liveRecord['performer'])): ?>
<span class="alt_text_font"><br>
Information About The Performers For This Concert Will Be Available Soon.</span>
<?php else: ?>
<?php foreach ($listen_liveRecord['performers'] as $performer): ?>
<span class="alt_heading_font">FEATURING:</span>

You can see the first part of the code block says 

<?php if (!empty($listen_liveRecord['performer'])): ?>

but  a few lines below you have

<?php foreach ($listen_liveRecord['performers'] as $performer): ?>
Carl

PHP Programmer

interactivetools.com

By gkornbluth - August 20, 2020

Hello Carl, 

Nice to meet you (virtually)...

Yes it's a typo, but just in the post. It should be 'performers'.

You can download the plugin from http://www.thecmsbcookbook.com/downloads/relatedRecordLookupFunctions.zip

I''s an oldie but goodie from Chris Waddel. 

I also set up an account for you on my CMSB cookbook that I've been putting together for the past dozen or so years.

I've sent you an email at what I think is your email address with login credentials.

If you don't get it, reach out to me by email and I'll get them to you.

Thanks for looking at this,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Carl - August 24, 2020 - edited: August 24, 2020

Hi Jerry,
I have all the files you sent me. Try this and let me know if this solves everything:

                <?php foreach ($listen_liveRecord['performers'] as $performer): ?>
                <span class="alt_heading_font">FEATURING TEST:</span>
                
                </td>
            </tr><tr><td colspan="2"><hr class="active" /></td></tr>
            <?php break ?>
                <?php endforeach ?>
            <!-- BEGIN PERFORMER LISTING CODE -->
            <?php
                $lineup_order = array_column($listen_liveRecord['performers'], 'concert_lineup_order');
                array_multisort($lineup_order, SORT_DESC, $listen_liveRecord['performers']);
                    foreach ($listen_liveRecord['performers'] as $performer): ?>

If you're wondering what I did, I used the PHP function array_column to fetch the values from the array and then I performed an array_multisort on the array. The two lines I added to your file starts at line 190. The only changes I made to your code is here:

                $lineup_order = array_column($listen_liveRecord['performers'], 'concert_lineup_order');
                array_multisort($lineup_order, SORT_DESC, $listen_liveRecord['performers']);
Carl

PHP Programmer

interactivetools.com

By gkornbluth - August 24, 2020 - edited: August 24, 2020

Hi Carl,

Thanks for your insights on this, but I’m still a bit confused (nothing new I’m afraid).

I made the changes you suggested to indexA.php, but the lineup order that shows under ‘Featuring Test’ is nothing like the sample lineup order in the pilllbox (beta) field in the Sept 6, listen-live record, (although it does, for some reason, match the lineup text that was entered above that in white). I even tried commenting out the old orderBy in the list records call.

Must be something I don’t understand, could I ask you to take another quick look?

I've attached the latest indexA.php file in case you see something I missed.

Thanks,


Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Attachments:

indexA.php 14K

By Carl - August 25, 2020

For the Featuring Test section, I had added code that sorted the results based on the order you had defined for the concert lineup order numbers, and it was sorted highest to lowest. What's the difference between the Featuring test and Featuring in terms of how you want it to show up? Without the code I created and added yesterday, the Featuring Test at least was displaying the results in the same order as the pillbox results.

Carl

PHP Programmer

interactivetools.com

By gkornbluth - August 25, 2020 - edited: August 26, 2020

Hey Carl,

I moved the foreach to before your code and it seems to work (file attached), however I seem to have an issue inserting musicians into the pillbox in the show lineup order for a particular show.

I'm not sure I'm describing this correctly, but...

When the musicians listings themselves are sorted in last_name, first_name, group_name order, the order in the pillbox pull down list field list is sorted that way, which makes it convenient to find a specific musician in the pull down.(preferred)

However, when I try to insert a name into the listen_live record pillbox in show lineup order, they only appear in the pillbox in alphabetical last_name, first_name, group_name order.

When I remove the sorting from the musicians_listings, the the order in the pillbox pull down list field is in record number order, which makes it very difficult to find a specific musician in the pull down, and I'm still having difficulty entering the musicians in show lineup order. (they don't seem to show in either record number order or alphabetical order)

Or, maybe I just don't know how to reorder entries in a pillbox field or enter new 'pills' in a specific place in the field...

Any thoughts? 

Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Attachments:

indexD.php 14K

By Carl - September 2, 2020

When you want to re-sort pillbox values or add values in a specific place in the pillbox, the only way you can do this is to manually remove all the items up until the spot you want to place the item, and then re-added everything you just removed. Perhaps we can look into doing this more intuitively by dragging and dropping in a future update of CMSB.

Carl

PHP Programmer

interactivetools.com

By gkornbluth - September 2, 2020 - edited: September 2, 2020

Thanks Carl,

The removal and re-adding of values in the Pillbox doesn't seem to work either, since the pillbox order seems to be at least partly determined by the sort order of the musicians. besides, it's a very awkward way to go.

Here's what I'm trying to do. Maybe there's a better, elegant approach.

I have a master list of musicians in a multi record table called 'musicians' in Alphabetical order by 'last_name'

I have a list of monthly concerts in a multi record table called 'listen-live' that appears in a list viewer in chronological order: next month's concert first, then past concerts.

I need to be able to assign musicians to the concert(s) they'll be appearing in.

I need the musicians to be listed on their specific concert's page in 'Show Lineup Order', the order that they're appearing in that concert.

This order may change over time, and musicians can appear in multiple shows.

The Pillbox and related records seemed an elegant, but impossible solution.

Maybe there's a better way to approach this, or, maybe it won't be frightfully expensive to build in a sortable pillbox solution which you could then incorporate into a later CMSB update.

(I've come up with this type of issue before with the need to sort lists (Post # 2244589, maybe there’s a crossover.)

Best,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php