[HELP] getResources -> Doesn't return the resources I want, SQL needs to change

37 Posts

Northie Reply #1, 14 years, 10 months ago

using

MODx 2.0.8
getResources 1.4.0
PHP 5.3.8
MySQL 5.0.77

Hi, While on page with id 22 I have this call to getResourses:

[[!getResources?
	&tpl=`featuredWork`
	&includeTVs=`1`
	&processTVs=`1`
	&tvFilters=`Service==%Website Design%`
]]

This does not output anything, so I added the debug parameter and looked at the SQL, which is

SELECT
	`modResource`.`id`,
	`modResource`.`type`,
	`modResource`.`contentType`,
	`modResource`.`pagetitle`,
	`modResource`.`longtitle`,
	`modResource`.`description`,
	`modResource`.`alias`,
	`modResource`.`link_attributes`,
	`modResource`.`published`,
	`modResource`.`pub_date`,
	`modResource`.`unpub_date`,
	`modResource`.`parent`,
	`modResource`.`isfolder`,
	`modResource`.`introtext`,
	`modResource`.`richtext`,
	`modResource`.`template`,
	`modResource`.`menuindex`,
	`modResource`.`searchable`,
	`modResource`.`cacheable`,
	`modResource`.`createdby`,
	`modResource`.`createdon`,
	`modResource`.`editedby`,
	`modResource`.`editedon`,
	`modResource`.`deleted`,
	`modResource`.`deletedon`,
	`modResource`.`deletedby`,
	`modResource`.`publishedon`,
	`modResource`.`publishedby`,
	`modResource`.`menutitle`,
	`modResource`.`donthit`,
	`modResource`.`haskeywords`,
	`modResource`.`hasmetatags`,
	`modResource`.`privateweb`,
	`modResource`.`privatemgr`,
	`modResource`.`content_dispo`,
	`modResource`.`hidemenu`,
	`modResource`.`class_key`,
	`modResource`.`context_key`,
	`modResource`.`content_type`
FROM
	`modx_site_content` AS `modResource`
WHERE
	(
		(
			modResource.parent IN (22)
			AND
			`modResource`.`deleted` = 0
			AND
			`modResource`.`published` = 1
			AND
			`modResource`.`hidemenu` = 0
		)
		AND EXISTS (
			SELECT
				1
			FROM
				`modx_site_tmplvar_contentvalues` tvr
			JOIN
				`modx_site_tmplvars` tv
				ON
				tvr.value LIKE '%Website Design%'
				AND
				tv.name = 'Service'
				AND
				tv.id = tvr.tmplvarid
			WHERE
				tvr.contentid = modResource.id
		)
	)
ORDER BY
	publishedon DESC
LIMIT
	5

running this in mysql does not produce any errors, so I analysed the SQL and saw this part

....modResource.parent IN (22) AND...

The resources I'm trying to include have various different parents, but all have a TV of 'Service' with a value like 'Website Design'

If I take out that one line of the SQL, and run the query, I get the results I expect.

Back to the Docs

parents : Comma-delimited list of ids serving as parents. Use -1 to ignore parents when specifying resources to include.

Even though I'm not specifying a list of resources I tried adding &parents=`-1` anyway - same result - an empty set as the SQL had

....modResource.parent IN (-1) AND...

in it

So, the question really is this:

How can I use getResources to pull back pages filtered by template values only (ie omiting parents and resources)?
Is this even possible?

5,888 Posts

Bruno17 Reply #2, 14 years, 10 months ago

did you try

&parents=`0`

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

37 Posts

Northie Reply #3, 14 years, 10 months ago

I did,

and guess what, that line of SQL is

....modResource.parent IN (0) AND...

which is just as useless

If I use this snippet call

[[!getResources?
	&tpl=`featuredWork`
	&includeTVs=`1`
	&processTVs=`1`
	&resources=`42`
	&parents=`-1`
]]

the SQL is

SELECT
	`modResource`.`id`,
	`modResource`.`type`,
	`modResource`.`contentType`,
	`modResource`.`pagetitle`,
	`modResource`.`longtitle`,
	`modResource`.`description`,
	`modResource`.`alias`,
	`modResource`.`link_attributes`,
	`modResource`.`published`,
	`modResource`.`pub_date`,
	`modResource`.`unpub_date`,
	`modResource`.`parent`,
	`modResource`.`isfolder`,
	`modResource`.`introtext`,
	`modResource`.`richtext`,
	`modResource`.`template`,
	`modResource`.`menuindex`,
	`modResource`.`searchable`,
	`modResource`.`cacheable`,
	`modResource`.`createdby`,
	`modResource`.`createdon`,
	`modResource`.`editedby`,
	`modResource`.`editedon`,
	`modResource`.`deleted`,
	`modResource`.`deletedon`,
	`modResource`.`deletedby`,
	`modResource`.`publishedon`,
	`modResource`.`publishedby`,
	`modResource`.`menutitle`,
	`modResource`.`donthit`,
	`modResource`.`haskeywords`,
	`modResource`.`hasmetatags`,
	`modResource`.`privateweb`,
	`modResource`.`privatemgr`,
	`modResource`.`content_dispo`,
	`modResource`.`hidemenu`,
	`modResource`.`class_key`,
	`modResource`.`context_key`,
	`modResource`.`content_type`
FROM
	`modx_site_content` AS `modResource`
WHERE
	(
		(
			modResource.parent IN (-1)
			AND
			`modResource`.`deleted` = 0
			AND
			`modResource`.`published` = 1
			AND
			`modResource`.`hidemenu` = 0
		)
		OR
		`modResource`.`id` IN (42)
	)
ORDER BY
	publishedon DESC
LIMIT
	5

and i get the results I expect

I still don't get any results for using TVs only

I even tried setting the parents to 0 and increasing max depth - still nothing
[ed. note: Northie last edited this post 14 years, 10 months ago.]

5,888 Posts

Bruno17 Reply #4, 14 years, 10 months ago

Not sure, why getResources doesn't do what you want, but you can try this snippet.
Name it filterResources with this code:

<?php

//[[filterResources? &tvNames=`cb1,cb2` &cb1=`value A`]]

//$tvNames = 'cb1,cb2';
//$debug = 1;

$element = $modx->getOption('element',$scriptProperties,'getResources');

$tmplVarTbl = $modx->getTableName('modTemplateVar');
$tmplVarResourceTbl = $modx->getTableName('modTemplateVarResource');

$tvNames = explode(',', $tvNames);

$c = $modx->newQuery('modResource');
$columns = $modx->getSelectColumns('modResource', 'modResource', 'res_', array('id'));
$c->select($columns);

foreach ($tvNames as $cGroup => $tvName) {

if (isset($_REQUEST[$tvName])){
    $scriptProperties[$tvName] = $_REQUEST[$tvName];    
}

if (isset($scriptProperties[$tvName])){
    $tvValue = $scriptProperties[$tvName];
    $tvName = $modx->quote($tvName);

    if (is_array($tvValue)) {
        $conjunction = xPDOQuery::SQL_AND;
        foreach ($tvValue as $value) {
            $c->where("EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON CONCAT('||',tvr.value,'||') LIKE '%||{$value}||%' AND tv.name = {$tvName} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id)",
                $conjunction, null, $cGroup);
            $conjunction = xPDOQuery::SQL_OR;
        }
    } else {
        $c->where("EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON CONCAT('||',tvr.value,'||') LIKE '%||{$tvValue}||%' AND tv.name = {$tvName} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id)",
            xPDOQuery::SQL_AND, null, $cGroup);
    }        
}


}

$c->prepare();
if ($debug) {
    echo $c->toSql() . '
';
}

$sql = $c->toSql();


$stmt = $modx->query($sql);
if ($stmt) {
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $ids[] = $row['res_id'];
    }
}


$scriptProperties['resources'] = implode(',', $ids);
if ($debug) {
    echo 'IDs: ' . $scriptProperties['resources'] . '
';
}

if (!empty($ids)) {
    return $modx->runSnippet($element, $scriptProperties);
}

and try calling it this way:

[[!filterResources? 
&tvNames=`Service` 
&Service=`Website Design`
&tpl=`featuredWork`
]]

[ed. note: Bruno17 last edited this post 14 years, 10 months ago.]

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
2,475 Posts

Everett Reply #5, 14 years, 10 months ago

Did you file a bug for getResources? And does this behavior continue in MODX 2.1.5?

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

37 Posts

Northie Reply #6, 14 years, 10 months ago

@Bruno Thanks for that snippet, I'll give it a go...looks promising

@Everettg_99 I was hoping that it wasn't a bug and that I was just not using the snippet correctly.

Come Monday I might try upgrading to 2.1.5 and see what happens...the only issue for upgrading for me is the way we have our server set up - We have over 20 sites running revo 2.0.8 all from the same code base, all using deprecated parts of the api that have been removed in 2.1+ sad

1,613 Posts

fourroses666 Reply #7, 14 years, 9 months ago

somehow i can't get custom TV's.

##edit

[ [+tv.name]] [ed. note: fourroses666 last edited this post 14 years, 9 months ago.]

Evolution user, I like the back-end speed and simplicity

☆ A M B ☆
2,475 Posts

Everett Reply #8, 14 years, 9 months ago

Did you include the

&includeTVs=`1`

in your Snippet call?

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

433 Posts

michelle84 Reply #9, 12 years, 11 months ago

hmm, third try to post, there seems to be something wrong with my post or the forums!

I know this is an old thread, but I'm trying to use the filterResources snippet as well (thank you Bruno).
I want to add something to the query, to only show resources from a specified parent and only published and undeleted ones.

I added this (got it from another thread):

$parent = isset($parent) ? $parent : $modx->resource->get('id');
$depth= isset($depth) ? $depth : '10';
$sortby = isset($sortby) ? $sortby : 'pagetitle';
$childs = $modx->getChildIds($parent,$depth,array('context' => 'web'));

And then this:

$c->where(array('published'=>1,'deleted:!=' =>'1'));
$c->where(array('id:IN'=>$childs));
$c->sortby($sortby);

My snippet call:

[[!filterResources? 
&tvNames=`articlestags` 
&articlestags=`[[*articlestags]]`
&tpl=`contentItem`
&parent=`2`
&debug=`1`
]]

The debug output is:

SELECT `modResource`.`id` AS `res_id` FROM `modxeh_site_content` AS `modResource` WHERE ( EXISTS (SELECT 1 FROM `modxeh_site_tmplvar_contentvalues` tvr JOIN `modxeh_site_tmplvars` tv ON CONCAT(',',tvr.value,',') LIKE '%,tag1,%' AND tv.name = 'articlestags' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) OR EXISTS (SELECT 1 FROM `modxeh_site_tmplvar_contentvalues` tvr JOIN `modxeh_site_tmplvars` tv ON CONCAT(',',tvr.value,',') LIKE '%,another tag,%' AND tv.name = 'articlestags' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND ( `modResource`.`published` = 1 AND `modResource`.`deleted` != 1 ) AND `modResource`.`id` IN (3,4,5,7) ) ORDER BY pagetitle ASC IDs: 5,6,2,7,4

The snippet does correctly show only published resources, so that part is working. But it's also showing resources that don't belong to the specified parent (namely resource with ID 2 and ID 6).

Should I write the where clause a different way? Or has it got something to do with the alias for the ID column? Thanks for any help!
I also attached my entire snippet to this post.

http://www.vierkante-meter.nl