using :IN with an array in a criteria array

☆ A M B ☆
450 Posts

jrotering Reply #1, 16 years, 2 months ago

I’m trying to process arrays that are coming from select-multiple elements in my web form, and build :IN criteria for a getCollection retrieval. Here’s my code:

foreach($_POST as $k=>$v) {
if (is_array($v)) { // multi-select field
  $criteria["${k}:IN"] = $v;
...

I’m expecting this to become the equivalent of:

$criteria = array(
...
'myfield:IN'=>array('val1', 'val2','val3'),
...
);

It isn’t working though. The :IN clauses are having no effect on the number of results being returned. My other form elements, including some that set min and max values, are all working properly and affecting the results accordingly.

What am I doing wrong?

24,544 Posts

BobRay Reply #2, 16 years, 2 months ago

I’m not sure, but it here’s an example from the core code that might help:

$query = $modx->newQuery('modTemplateVar');
$query->where('tv.name IN (\'' . $tvIdentifiers . '\')');

Something like this might do it too:

$query->where(array(  
    $k . ':IN' => $v,  
 ));

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

☆ A M B ☆
450 Posts

jrotering Reply #3, 16 years, 2 months ago

I was following the example here:
http://svn.modxcms.com/docs/display/xPDO20/xPDOQuery

$query->where(array(  
    'Owner.name:LIKE' => '%a%',  
    'Box.width:>=' => 10,  
    'Box.height:!=' => 2,  
    'Box.color:IN' => array('red','green','blue'),  
));

It looks like your examples (at least the first one) might not be using the array() syntax for the list of possible values. Should it/can it just be a list in parentheses?

What’s weird is that it isn’t restricting/reducing my number of results... it seems like it ought to cause zero matches if it is a botched syntax or something... but maybe it is just getting ignored? Is there a way to see what my full criteria is, after I add all the form fields to the array?

☆ A M B ☆
450 Posts

jrotering Reply #4, 16 years, 2 months ago

I did a var_dump on $query->query, and these :IN statements aren’t getting put in there at all. It must be a syntax problem.

4,149 Posts

splittingred Reply #5, 16 years, 2 months ago

You’re using 2.0.0-pl?

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

☆ A M B ☆
450 Posts

jrotering Reply #6, 16 years, 2 months ago

This site’s still RC-3 actually.

MODX Staff
730 Posts

netProphET Reply #7, 16 years, 2 months ago

${k} looks a little weird to me.. did you mean to use {$k}?

Quote from: jrotering at Jul 28, 2010, 02:52 AM

foreach($_POST as $k=>$v) {
if (is_array($v)) { // multi-select field
  $criteria["${k}:IN"] = $v;
...

Mike Schell
Lead Developer, MODX Cloud
Email: [email protected]
GitHub: https://github.com/netProphET/
Twitter: @mkschell

☆ A M B ☆
450 Posts

jrotering Reply #8, 16 years, 2 months ago

Those are mostly-interchangeable PHP syntaxes. The only difference I know of is with array indices: {$foo[’bar’]} will work but ${foo[’bar’]} will not.

At any rate, I tried it as {$k} just for grins, but it had no effect.

4,149 Posts

splittingred Reply #9, 16 years, 2 months ago

Ah, upgrade to PL; RC-3 doesn’t support the param:IN clause.

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

☆ A M B ☆
450 Posts

jrotering Reply #10, 16 years, 2 months ago

Ah. I’ll do the upgrade.

Interesting though - when I switched approaches and tried to assign a parentheses-enclosed list of the array values (rather than the native php array $v):

foreach($_POST as $k=>$v) {
if (is_array($v)) {
   $list = '';
   foreach ($v as $val) { $list .= "'$val',"; }
   $list = substr($list,0,-1); //get rid of last comma
  $query["{$k}:IN"]= '(' . $list . ')';
  //$query["{$k}:IN"]= 'array(' . $list . ')'; // also tried this...

Then the IN clause DOES seem to come through... but it must be malformed, because I get zero results.

object(xPDOQueryCondition)#24 (3) {
          ["sql"]=>
          string(32) "`Extraprofiles`.`ethnicity` IN ?"
          ["binding"]=>
          array(3) {
            ["value"]=>
            string(37) "('mixed','arctic','caucasian_indian')"
            ["type"]=>
            int(2)
            ["length"]=>
            int(0)
          }

Not sure if this means anything either, but the ’type’ shouldn’t be ’int’ for that field... dbtype is set to varchar and phptype to string in my schema and class files.

I guess this is all a moot point, if the upgrade fixes it.