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

31

Northie Reply #1, 5 months, 3 weeks ago

Link to this post#1

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?

1,626

xdbedit - create your own CMP to edit custom-tables with TV-input-types
MIGX (multiItemsGridTV, sortable multirecord-TV)
formit2db with autocreate schema&classes
formit2resource
custom-tv-input-type for revo:imagelistTV
quickCMP - CMP for creating simple backend-'modules' per snippets
bloX/Xedit - Ressource-Listing and frontend-sorting/editing....

Bruno17 Reply #2, 5 months, 3 weeks ago

Link to this post#2

did you try

&parents=`0`

31

Northie Reply #3, 5 months, 3 weeks ago

Link to this post#3

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

1,626

xdbedit - create your own CMP to edit custom-tables with TV-input-types
MIGX (multiItemsGridTV, sortable multirecord-TV)
formit2db with autocreate schema&classes
formit2resource
custom-tv-input-type for revo:imagelistTV
quickCMP - CMP for creating simple backend-'modules' per snippets
bloX/Xedit - Ressource-Listing and frontend-sorting/editing....

Bruno17 Reply #4, 5 months, 3 weeks ago

Link to this post#4

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`
]]

1,899

Hosting and Dev stuff: http://fireproofsocks.com/
Articles and Reviews: http://tipsfor.us/

Everett Reply #5, 5 months, 3 weeks ago

Link to this post#5

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

31

Northie Reply #6, 5 months, 3 weeks ago

Link to this post#6

@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+