Subfield selector unexpectedly returning result in OR like fashion?

[ralberts]

Hi there,

According to http://processwire.com/api/selectors/#subfield a subfield selector defined as...

template=accommodationtype,availability.column=1194,availability.row=1889,availability.value=1

...should return all accommodationtypes where all availability restrictions match:

availablity.column = 1194 AND availability.row=1887 AND availability.value=1

What actually happends (looking at the querylog), is that the availability field is joined 3 times, matching all 3 subfields seperately, then grouping the result and thus returning all accommodationtypes where either of the restrictions are met:

availablity.column = 1194 OR availability.row=1887 OR availability.value=1

Is this intended behaviour, a bug or am I just missing something here? Perhaps using a different selector would do the trick? I can probably fix this in PageFinder::___getQuery(), but without a decent testsuite I'm not comfortable in fiddling about there   

The actual query executed is:

SELECT 
  pages.id,
  pages.parent_id,
  pages.templates_id 
FROM
  `pages` 
  JOIN field_availability AS field_availability 
    ON field_availability.pages_id = pages.id 
    AND (((field_availability.matrix_column = '1194'))) 
  JOIN field_availability AS field_availability1 
    ON field_availability1.pages_id = pages.id 
    AND (((field_availability1.data = '1187'))) 
  JOIN field_availability AS field_availability2 
    ON field_availability2.pages_id = pages.id 
    AND (((field_prijstabel2.matrix_value = '1'))) 
WHERE (pages.templates_id = 57) 
  AND (pages.status < 2048) 
GROUP BY pages.id

Any help is much appreciated!

[ralberts]

Something that might work is the code below. But that might break several other selectors. I can't really oversee the effects of changing this.

PageFinder::___getQuery()

				// use actual table name if first instance, if second instance of table then add a number at the end
				if ($subfield !== '' && $subfield !== 'count') {
					$tableAlias = $field->table;
				} else {
					$tableAlias = $field->table . ($fieldCnt[$field->table] ? $fieldCnt[$field->table] : '');
				}
				$tableAlias = $database->escapeTable($tableAlias);

Original:

				// use actual table name if first instance, if second instance of table then add a number at the end
				$tableAlias = $field->table . ($fieldCnt[$field->table] ? $fieldCnt[$field->table] : '');
				$tableAlias = $database->escapeTable($tableAlias);

[Ivan Gretsky]

You can probably workaround by using subsequent selectors like this:

$p1 = $pages->find('template=accommodationtype,availability.column=1194');
$p2 = $p1->find('availability.row=1889');
$p3 = $p2->find('availability.value=1');

A few more db queries...

But if you are right it seems like an unwanted behaviour. I recommend making a github issue or even a PR.

[LostKobrakai]

This is certainly not how it's intended to work. Like Ivan I'd suggest posting that on github, too.

@Ivan Your version doesn't query the db more often. It's a single db query and two runtime queries. What could be a workaround is a subselector.

$pages->find("template=accommodationtype, availability=[column=1194, row=1889, value=1]");

[ralberts]

Thanks lads. The workarounds don't seem to work; so I'll head over to github and post the issue there.

[ryan]

@ralberts, I haven't been able to duplicate the issue here. Though it will depend on what Fieldtypes these fields are using, so my test case may not be the same as yours. But I am wondering if you might be looking for field.subfield grouping to force match of the same record in a multi-value field? See here: https://processwire.com/blog/posts/processwire-2.5-changelog/#selectors-and-finding-pages

For example, I'm thinking the selector you were wanting is instead this, so that "availability" is always referring to the same record in the set: 

@availability.column=1194, @availability.row=1889, @availability.value=1 

More details here too: http://processwire.com/api/selectors/#subfield

[ralberts]

Hi Ryan,

The grouping does sound like exactly what I need (totally missed out on that feature!), but does not seem to work: it produces the exact same query as without the grouping. Probably because I'm using the non-native Matrixfieldtype. It stores data in a table set up like this:

Table:

CREATE TABLE `field_availability` (
  `pages_id` int(10) unsigned NOT NULL,
  `data` int(11) NOT NULL DEFAULT '0',
  `sort` int(10) unsigned NOT NULL,
  `matrix_column` int(11) NOT NULL DEFAULT '0',
  `matrix_value` varchar(255) DEFAULT '',
  PRIMARY KEY (`pages_id`,`sort`),
  KEY `data` (`data`),
  KEY `matrix_column` (`matrix_column`),
  FULLTEXT KEY `matrix_value` (`matrix_value`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Testcase:

$pages->find('template=accommodationtype,@pricetable.matrix_value=1,@pricetable.matrix_column=1178,@pricetable.data=1127');
$pages->find('template=accommodationtype,pricetable.matrix_value=1,pricetable.matrix_column=1178,pricetable.data=1127');

$queries = $database->getQueryLog();
foreach($queries as $query){
    if (strpos($query, 'pricetable')) {
        echo '<pre>' . $query . '</pre>';
    }
}

Output:

SELECT pages.id,pages.parent_id,pages.templates_id 
FROM `pages` 
JOIN field_pricetable AS field_pricetable ON field_pricetable.pages_id=pages.id AND (((field_pricetable.matrix_value='1' ) )) 
JOIN field_pricetable AS field_pricetable1 ON field_pricetable1.pages_id=pages.id AND (((field_pricetable1.matrix_column='1178' ) )) 
JOIN field_pricetable AS field_pricetable2 ON field_pricetable2.pages_id=pages.id AND (((field_pricetable2.data='1127' ) )) 
WHERE (pages.templates_id=44) 
AND (pages.status<1024) 
GROUP BY pages.id 

SELECT pages.id,pages.parent_id,pages.templates_id 
FROM `pages` 
JOIN field_pricetable AS field_pricetable ON field_pricetable.pages_id=pages.id AND (((field_pricetable.matrix_value='1' ) )) 
JOIN field_pricetable AS field_pricetable1 ON field_pricetable1.pages_id=pages.id AND (((field_pricetable1.matrix_column='1178' ) )) 
JOIN field_pricetable AS field_pricetable2 ON field_pricetable2.pages_id=pages.id AND (((field_pricetable2.data='1127' ) )) 
WHERE (pages.templates_id=44) 
AND (pages.status<1024) 
GROUP BY pages.id