@SELECT WHERE parent=

15 Posts

Jayson Reply #1, 16 years, 8 months ago

Hi there, I’m creating a TV which allows the user to insert data from a page of their choice using @SELECT pagetitle FROM tablename_site_content WHERE parent=’15, ’however I’m struggling to find a way to show all the child documents under a parent, it shows the immediate childs, but not the others, so for example my tree looks a bit like this:

Home
News (15)
February 2010
Article 1
Article 2
Article 3
January 2010
Article 1
Article 2
Article 3

What I’d like to show is all the children under News, not just January 2010 and February 2010.

Any ideas?

Many thanks

J

249 Posts

cyclissmo Reply #2, 16 years, 8 months ago

You will need to add JOIN clauses to your query. I’m very new to the MODx platform so I can’t post an example, but hopefully that is enough information to get you back up and running.

lo9on.com

MODx Evolution/Revolution | Remote Desktop Training | Development

15 Posts

Jayson Reply #3, 16 years, 8 months ago

At the moment I’m using ditto and a check box on the news item I want to place on the home page. But, in reality that means that an active news item needs to be unchecked before another can be activated for the home page, so I’d rather have a list of available items on the home page template to choose from, which means only one choice...

15 Posts

Jayson Reply #4, 16 years, 8 months ago

Quote from: cyclissmo at Feb 19, 2010, 12:13 AM

You will need to add JOIN clauses to your query. I’m very new to the MODx platform so I can’t post an example, but hopefully that is enough information to get you back up and running.

I’m not particularly with it when it comes to programming, so I;d need an example for me to under stand what you mean I’m afraid. I did a quick web search, but it came back with raw code rather than modx specific, bah..

249 Posts

cyclissmo Reply #5, 16 years, 8 months ago

Preparing an example made me realize that we’re working with recursive sets, something that MySQL is not necessarily optimized for. In other words, you would need a single JOIN statement for each level of recursion. YOu can see how this would get unwieldy and inefficient with an example:

SELECT * FROM modx_site_content t1 
LEFT JOIN modx_site_content t2 ON t2.parent = t1.id 
LEFT JOIN modx_site_content t3 ON t3.parent = t2.id
...
LEFT JOIN modx_site_content tn ON tn.parent = tn-1.id
WHERE t1.id = 15

If all you need are the child ids, you can create a simple snippet such as this:

<?php
// Evo 1.02
// returns all child ids contained in a given resource
// assign your top parent id to the &parentid parameter of your snippet.
$parentid = (isset($parentid)) ? $parentid : 0 ;
$ids = $modx->getChildIds($parentid, 0);
// ... filter or format $ids array to suit your needs ... //
return $ids;
?>

Use @SNIPPET binding, passing the top parent id to the snippet’s &parentid parameter.

I’m sure there’s a PHx alternative, if someone would like to chime in.

- Mike

lo9on.com

MODx Evolution/Revolution | Remote Desktop Training | Development

15 Posts

Jayson Reply #6, 16 years, 8 months ago

Thanks, I’ll try work that out ;o)

J

15 Posts

Jayson Reply #7, 16 years, 8 months ago

Sorry i think my head has exploded, how do I call this snippet from the TV?

Do I use an @SELECT binding or [[snippetname? $parentid=`15`]]

It would really help if you could treat me as an imbecile and right out the line I’d need in the TV to display a drop down list of the child documents in the template.... from which I will hopefully learn something new!

Thanks a mill

J

249 Posts

cyclissmo Reply #8, 16 years, 8 months ago

I must have been half-asleep thinking there was a @SNIPPET binding parameter, although I think there should be lipsrsealed

Try this out, although I still think this can be done much easier with PHx if someone can post an example ...

Your snippet, let’s call it ’myDocSelector’:

<?php
// Evo 1.02
// returns all child ids contained in a given resource
// assign your top parent id to the &parentid parameter of your snippet.
$parentid = (isset($parentid)) ? $parentid : 0 ;
$ids = $modx->getChildIds($parentid, 0);
// ... filter or format $ids array to suit your needs ... //
$return = http_build_query($ids, '', '||');
$return = str_replace('=', '==', $return);
return urldecode($return);
?>

Your template variable, in the Input Option Values area:

@EVAL return $modx->runSnippet('myDocSelector', array('parentid', 15));

Let me know if this is what you had in mind.

-Mike

lo9on.com

MODx Evolution/Revolution | Remote Desktop Training | Development

15 Posts

Jayson Reply #9, 16 years, 8 months ago

Hi Mike, yep that creates a list, I needed to put the top id ’15’ in the snippet to only show the ones I wanted in the news section of the site. Trying now to link the selected doc id to the chunk on the template

249 Posts

cyclissmo Reply #10, 16 years, 8 months ago

I think I made your life a bit easier. I updated the code above to pass the resource id. Try it and let me know.

-Mike

lo9on.com

MODx Evolution/Revolution | Remote Desktop Training | Development