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
modx.pro
1
10 251
0

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

Сергей
19 декабря 2013, 03:32
0
In your code you use getResources, not pdoResources. I think this is the problem.
    Mark
    19 декабря 2013, 11:01
    0
    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
      1
      0
      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
      0
      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
        0
        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
          0
          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
          0
          I've set 'ShowLog' => 1 but it doesn't get shown. Is it on screen or stored. I've looked in /var/www/html/core/cache/logs
            Авторизуйтесь или зарегистрируйтесь, чтобы оставлять комментарии.
            7