using @SELECT to create TV with alphabetical list of resources

108 Posts

lukemcd Reply #1, 14 years, 7 months ago

I want to create a TV that shows a pulldown list of active resources sorted alphabetically. The top entry should be blank. I don't see a way to set the sort on the resource list TV so I am using a Listbox (single-select) type. I have the following code in Input Option Values:

@SELECT 'select a page' AS pagetitle, '' AS id 
UNION SELECT pagetitle,id FROM `modx_site_content` 
WHERE `published` = 1 AND `deleted` <> 1 AND `hidemenu` <> 1 
ORDER BY pagetitle

This works fine except "select a page" is sorted under S, not at the top. So this may be more of a SQL syntax question.

I am using Revo 2.2. Any thoughts on how to set this up or if there is a better approach?

64 Posts

culd | steffan Reply #2, 14 years, 7 months ago

You can change your query as follows:

SELECT 'select a page' AS pagetitle, '' AS id 
UNION SELECT pagetitle,id FROM `modx_site_content` 
WHERE `published` = 1 AND `deleted` <> 1 AND `hidemenu` <> 1 
ORDER BY id='' DESC, pagetitle ASC

5,888 Posts

Bruno17 Reply #3, 14 years, 7 months ago

try a snippet like that (getResourceList):

<?php

$sortby='pagetitle';

$c = $modx->newQuery('modResource');
$c->where(array('published'=>1,'deleted:!=' =>'1','hidemenu:!=' => '1'));
$c->sortby($sortby);
$collection = $modx->getCollection('modResource',$c);

$output='select a page==';
foreach ($collection as $o){
$output .= '||'.$o->get('pagetitle').'('.$o->get('id').')=='.$o->get('id');
}
return $output;

and this input-option-values:

@EVAL return $modx->runSnippet('getResourceList');

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

108 Posts

lukemcd Reply #4, 14 years, 7 months ago

Excellent, both great options. Thanks!

380 Posts

Brian Reply #5, 14 years, 4 months ago

Quote from: Bruno17 at Mar 21, 2012, 03:42 PM

try a snippet like that (getResourceList):

<?php

$sortby='pagetitle';

$c = $modx->newQuery('modResource');
$c->where(array('published'=>1,'deleted:!=' =>'1','hidemenu:!=' => '1'));
$c->sortby($sortby);
$collection = $modx->getCollection('modResource',$c);

$output='select a page==';
foreach ($collection as $o){
$output .= '||'.$o->get('pagetitle').'('.$o->get('id').')=='.$o->get('id');
}
return $output;

and this input-option-values:

@EVAL return $modx->runSnippet('getResourceList');

How would one go about setting the parent and depth with this query? something like this?

$c->where(array('published'=>1,'deleted:!=' =>'1','hidemenu:!=' => '1','depth'=>2,'parent'=>45));