ralberts Posted October 6, 2015 Share Posted October 6, 2015 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! Link to comment Share on other sites More sharing options...
ralberts Posted October 6, 2015 Author Share Posted October 6, 2015 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); Link to comment Share on other sites More sharing options...
Ivan Gretsky Posted October 6, 2015 Share Posted October 6, 2015 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. Link to comment Share on other sites More sharing options...
LostKobrakai Posted October 6, 2015 Share Posted October 6, 2015 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]"); Link to comment Share on other sites More sharing options...
ralberts Posted October 7, 2015 Author Share Posted October 7, 2015 Thanks lads. The workarounds don't seem to work; so I'll head over to github and post the issue there. Link to comment Share on other sites More sharing options...
ryan Posted October 9, 2015 Share Posted October 9, 2015 @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 Link to comment Share on other sites More sharing options...
ralberts Posted October 12, 2015 Author Share Posted October 12, 2015 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now