Jump to content
ralberts

Subfield selector unexpectedly returning result in OR like fashion?

Recommended Posts

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  :rolleyes: 

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!

Share this post


Link to post
Share on other sites

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);

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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]");

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites

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 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...