SOLVED: Rowboat snippet fails when passing 'where' variable as JSON to array of paramaters

118 Posts

chris.dempsey78 Reply #1, 13 years, 10 months ago

I am trying to run the getPage snippet from within a snippet of my own to output details from a custom database table using the Rowboat snippet. It works in part but does not recognise the value I'm passing for the 'where' variable:

This works when the where variable is entered directly:

$params['where']= '{"pro_establishment_main:LIKE":"Retail"}';
$params['element'] = 'Rowboat';
$params['table'] = 'custom_table';
$params['sortBy'] = 'name';
$params['totalVar'] = 'rowboat.total';
$params['tpl'] = 'myTemplate';
$params['cache'] = '0';
$params['cacheResults'] = '0';
$params['limit'] = '10';

return $modx->runSnippet('getPage', $params);

However the where statement needs to be dynamic based on user selection from a form so that only matching records are returned from the database. The version below builds the where statement then runs the snippet call:

if (isset($_POST['btn_submit']) && $_POST['btn_submit'] == 'submit') {

  $where .= '&where={';

  if(!empty($_POST['filter_name_last'])) {
	$where .= '"pers_name_last:LIKE":"'.$_POST['filter_name_last'].'%",';
  }
  if(!empty($_POST['filter_pro_establishment_main'])) {
	$where .= '"pro_establishment_main:LIKE":"'.$_POST['filter_pro_establishment_main'].'",';
  }
  if(!empty($_POST['filter_quali_grade'])) {
	$where .= '"quali_grade:LIKE":"'.$_POST['filter_quali_grade'].'",';
  }

  // remove last comma from $where string as it breaks the snippet call
  $where = rtrim($where, ',');

  $where .= '}';

}

$params['where'] = $where;
$params['element'] = 'Rowboat';
$params['table'] = 'custom_table';
$params['sortBy'] = 'name';
$params['totalVar'] = 'rowboat.total';
$params['tpl'] = 'myTemplate';
$params['cache'] = '0';
$params['cacheResults'] = '0';
$params['limit'] = '10';

return $modx->runSnippet('getPage', $params);

The snippet call outputs data as normal but ignores the $where variable. I'm unsure how to resolve this, can anyone point me in the right direction?

Thanks

Edit: updated title to reflect further investigation pointing to Rowboat as the most likely cause of the issue. [ed. note: chris.dempsey78 last edited this post 13 years, 10 months ago.]

MODX Staff
10,725 Posts

opengeek Reply #2, 13 years, 10 months ago

Are you calling your snippet uncached? Are you sure your $_POST variables are correct?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

118 Posts

chris.dempsey78 Reply #3, 13 years, 10 months ago

Sorry, should have provided more detail. The call to my custom snippet is uncached. I assume the call to $modx->runSnippet('getPage', $params); within my custom snippet is also uncached, I couldn't find any documentation to suggest that such calls are cached.

$_POST variables are correct as when writing the $where variable to screen it returns as I expect it to.

If I change the custom snippet to output a copy of the $where variable which is dynamically built as shown in the second code block above:

return $where.'<br />'.$modx->runSnippet('getPage', $params);

it returns the correct string to pass to the $params array for: $params['where'] = $where; but outputs all rows in the table, not the ones that match the where statement.

If I hard code the values written out the where parameter is used correctly and shows only the rows from the database that match. Combinations of the $where variable that I've written out to screen then hard coded into the $params array are:

{"pers_name_last:LIKE":"U%"}

{"pro_establishment_main":"Ross Hall"}

{"quali_grade":"Consultant"}

{"pers_name_last:LIKE":"U%","pro_establishment_main":"Ross Hall"}

{"pers_name_last:LIKE":"U%","quali_grade":"Consultant"}

{"pro_establishment_main":"Ross Hall","quali_grade":"Consultant"}

{"pers_name_last:LIKE":"U%","pro_establishment_main":"Ross Hall","quali_grade":"Consultant"}

This is on Revolution 2.2.5-pl (traditional), Windows Server 2008, PHP 5.2.13 but I'm not sure that is relevant at the moment.

MODX Staff
10,725 Posts

opengeek Reply #4, 13 years, 10 months ago

This sounds like an issue with Rowboat, but I'm not sure what it might be...

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

118 Posts

chris.dempsey78 Reply #5, 13 years, 10 months ago

I turned on debugging on the Rowboat call. It shows the SQL Query does not include the WHERE statement when setting $params['where'] = $where; but does if I use: $params['where'] = '{"quali_grade":"Consultant"}';

Update: I thought it might have been down to my snippet having a variable called $where and the Rowboat snippet having a variable with the same name, changed mine to $where_filter but no change. [ed. note: chris.dempsey78 last edited this post 13 years, 10 months ago.]

534 Posts

exside Reply #6, 13 years, 10 months ago

Sorry to shoot a maybe stupid question into this discussion =):

[StupidQuestion]
I could not find in http://rtfm.modx.com/display/ADDON/getPage nor in the code at https://github.com/opengeek/getPage that getPage supports a &where param (as for example getResources does and GoogleSiteMap also does [but somehow buggy])?? So why is this working at all?
[/StupidQuestion]

now to the problem:

have you tried to set the where conditions into an array instead of a JSON object? xpdo->where() wants an array and usually the &where params of snippets take both, array or JSON but before it's made a db query it's always converted to an array (via ->fromJSON()). So maybe try that and see if it changes something in the behaviour...

118 Posts

chris.dempsey78 Reply #7, 13 years, 10 months ago

It's the Rowboat snippet that accepts the &where parameter.

I'm away from the computer now but will see if I can figure how to pass the where parameters as an array rather than JSON tomorrow.

118 Posts

chris.dempsey78 Reply #8, 13 years, 10 months ago

This is what the Rowboat snippet does with the where parameter:

if (!empty($where)) {
    $where = $modx->fromJSON($where);
    if (!empty($where)) {
        $c->where($where);
    }
}

So it does convert from JSON.

Think I'll get another look at it in the morning.

☆ A M B ☆
24,524 Posts

sottwell Reply #9, 13 years, 10 months ago

Remember, getPage is only a wrapper for the actual aggregating snippet to paginate said wrapped snippet's results. So a &where property would have no meaning to getPage and would be passed on to the wrapped snippet, which may or may not support the property. There are some snippet that do support it but it's not in the snippet's documentation. Some snippets can accept an array or a JSON string; the Rowboat snippet obviously only accepts a JSON string.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

5,888 Posts

Bruno17 Reply #10, 13 years, 10 months ago

may be there is something wrong with your json.

you can try this way:

<?php

if (isset($_POST['btn_submit']) && $_POST['btn_submit'] == 'submit') {
 
  $wheres = array();
 
  if(!empty($_POST['filter_name_last'])) {
    $wheres[] =  array('pers_name_last:LIKE'=>$_POST['filter_name_last'].'%');
  }
  if(!empty($_POST['filter_pro_establishment_main'])) {
    $wheres[] = array('pro_establishment_main:LIKE'=>$_POST['filter_pro_establishment_main']);
  }
  if(!empty($_POST['filter_quali_grade'])) {
    $wheres[] = array('quali_grade:LIKE'=>$_POST['filter_quali_grade']);
  }
 
  $where = $modx->toJson($wheres);
 
}

 
$params['where'] = $where;
$params['element'] = 'Rowboat';
$params['table'] = 'custom_table';
$params['sortBy'] = 'name';
$params['totalVar'] = 'rowboat.total';
$params['tpl'] = 'myTemplate';
$params['cache'] = '0';
$params['cacheResults'] = '0';
$params['limit'] = '10';
 
return $modx->runSnippet('getPage', $params);

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!