pdoResources and WHERE filter

If I use a WHERE filter the results include deleted and unpublished resources.

I even tried adding to the WHERE (AND:published:=":1} but this made no difference

I'm running this as a snippet

$output = "<ul>";
$output .= $modx->runSnippet('pdoPage',array(
    'element' => 'getResources',
    'parents' => $categoryFilter,
    'tpl' => 'rcList-default',
    'pageLimit' => '7',
    'includeTVs' => '1',
    'processTVs' => '1',
    'toPlaceholder' => 'rcListing',
    'where' => $searchFilter,
    'tvFilters' => $tvFilter
));


$output .= "</ul>";

Mark

18 декабря 2013, 20:55

10 641

Комментарии: 7

Сергей

19 декабря 2013, 03:32

In your code you use getResources, not pdoResources. I think this is the problem.

Mark

19 декабря 2013, 11:01

Sorry, I pasted the wrong code. When I changed pdo to get it works — just SLOWER!

Below is the actual code

<?php
$modx->setPlaceholder('genericTitle','All');

if(isset($_GET['resSearch'])) {
    $searchFilter = '{"pagetitle:LIKE":"%' . $_GET['resSearch'] . '%", "OR:description:LIKE":"%' . $_GET['resSearch'] . '%", "OR:longtitle:LIKE":"%' . $_GET['resSearch'] . '%" }';

    $modx->setPlaceholder('genericTitle',$_GET['resSearch']);

} else {
    $searchFilter = '';
}

if(isset($_GET['category']) && is_numeric($_GET['category']) && $_GET['category'] <> '2') {
    $categoryFilter = $_GET['category'];
    $modx->setPlaceholder('genericTitle',$modx->runSnippet('resourceTypes',array('input' => $_GET['category'])));
} else if (isset($_GET['category']) && is_numeric($_GET['category']) && $_GET['category'] == '2') {
    $categoryFilter='9,14,21,29,33,41,120,50,63,66,69,77';
    $modx->setPlaceholder('genericTitle',$modx->runSnippet('resourceTypes',array('input' => $_GET['category'])));
} else {
    $categoryFilter='9,14,21,29,33,41,120,50,63,66,69,77,83,91,93';
}

if(isset($_GET['tag'])) {
    $tvFilter = 'rcTag==%' . $_GET['tag'] . '%';
    $modx->setPlaceholder('genericTitle',$_GET['tag']);
} else if(isset($_GET['service'])) {
    $tvFilter = 'serviceType==%' . $_GET['service'] . '%';
    $modx->setPlaceholder('genericTitle',$_GET['service']);
} else {
    $tvFilter = '';
}
    

$output = "<ul>";
$output .= $modx->runSnippet('pdoPage',array(
    'element' => 'pdoResources',
    'parents' => $categoryFilter,
    'tpl' => 'rcList-default',
    'pageLimit' => '7',
    'includeTVs' => 'Thumbnail,download,downloadText,rcTag,serviceType',
    'processTVs' => '1',
    'toPlaceholder' => 'rcListing',
    'where' => $searchFilter,
    'tvFilters' => $tvFilter
));


$output .= "</ul>";
return;

I'm now using getResources because I can't allow deleted or unpublished resources to show. But I'd really like to use pdoResources because it is much faster!

Василий Наумкин

19 декабря 2013, 11:30

I think, you have an error in $searchFilter or $tvFilter.

Try to debug you call using &showLog=`1`. For example:

[[!pdoResources?
	&parents=`0`
	&where=`{"pagetitle:LIKE":"%", "OR:description:LIKE":"%", "OR:longtitle:LIKE":"%"}`
	&showLog=`1`
]]

Will output:

0.0000741: pdoTools loaded
0.0000288: xPDO query object created
0.0029569: Added selection of modResource: SQL_CALC_FOUND_ROWS `id`, `type`, `contentType`, `pagetitle`, `longtitle`, `description`, `alias`, `link_attributes`, `published`, `pub_date`, `unpub_date`, `parent`, `isfolder`, `introtext`, `richtext`, `template`, `menuindex`, `searchable`, `cacheable`, `createdby`, `createdon`, `editedby`, `editedon`, `deleted`, `deletedon`, `deletedby`, `publishedon`, `publishedby`, `menutitle`, `donthit`, `privateweb`, `privatemgr`, `content_dispo`, `hidemenu`, `class_key`, `context_key`, `content_type`, `uri`, `uri_override`, `hide_children_in_tree`, `show_in_tree`, `properties`
0.0000529: Processed additional conditions
0.0005138: Added where condition: pagetitle:LIKE=%, OR:description:LIKE=%, OR:longtitle:LIKE=%, modResource.published=1, modResource.deleted=0
0.0000181: Sorted by publishedon, DESC
0.0000050: Limited to 10, offset 0
0.0005889: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `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`.`privateweb`, `modResource`.`privatemgr`, `modResource`.`content_dispo`, `modResource`.`hidemenu`, `modResource`.`class_key`, `modResource`.`context_key`, `modResource`.`content_type`, `modResource`.`uri`, `modResource`.`uri_override`, `modResource`.`hide_children_in_tree`, `modResource`.`show_in_tree`, `modResource`.`properties` FROM `modx_site_content` AS `modResource` WHERE  ( `modResource`.`pagetitle` LIKE '%' OR `modResource`.`description` LIKE '%' OR `modResource`.`longtitle` LIKE '%' AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 )  ORDER BY publishedon DESC LIMIT 10 "
0.0001440: SQL executed
0.0001042: Total rows: 45
0.0001040: Rows fetched
0.0005629: Returning processed chunks
0.0054829: Total time
3 407 872: Memory usage

As you see — there is exists published and deleted conditions by default.

Added where condition: pagetitle:LIKE=%, OR:description:LIKE=%, OR:longtitle:LIKE=%, modResource.published=1, modResource.deleted=0

Yana V

19 декабря 2013, 11:16

Hello! I have the same trouble. I use return=`sql`.
I have a mistake in WHERE filter, Than I checked all mistakes and the snippet worked great) Try it.

Mark

19 декабря 2013, 11:20

Hi

The snippet works but when the WHERE filter is used it return unpublished and deleted resources.

If I change pdoResources to getResources it works fine.

So I don't think the problem is in my WHERE filter. I think the problem is that pdoResources forgets to look at the published field when it's processing a WHERE filter

Василий Наумкин

19 декабря 2013, 11:40

Here is correct SQL condition:

$query = $modx->sanitizeString($_GET['resSearch']);
$searchFilter = '["
	published = 1 AND
	deleted = 0 AND
	(
		pagetitle LIKE \"%'.$query.'%\" OR
		description LIKE \"%'.$query.'%\" OR
		longtitle LIKE \"%'.$query.'%\"
	)
"]';

return $modx->runSnippet('pdoResources', array(
	'parents' => 0,
	'showLog' => 1,
	'where' => $searchFilter
));

Mark

19 декабря 2013, 12:02