I want to call pdoResources based on filters from $_SESSION.
So far I can easily use them inside the snippet (i mean filters).
However the pdoResources give no results if i include more than one [‘where’] field - please see below in code snippet.
BTW: each filter: $status, $sectors, $funds is an associated PHP array so “IN” should work well i guess?
If I leave only one where closure pdoRsource will return correct result based on this one where query.
Expected behavior
I want to be able mix/max a lot of where closures like above, not sure how many filters i will have in the end. Can anybody show/tell me right approach to do that? or maybe use &tvFilters instead?
Apart from that your code should work.
As you are using 'showLog' => 1,, you could output the placeholder [[!+pdoResourcesLog]] in the resource and check if the SQL query is correct.
Thanks you your response.
I think the values seems fine as it is.
Apart from that i have found other issue that is resolved now, however cant get my where structure right…
if ($template == 10) {
$status = isset($_SESSION['selectedFilters']['status']) ? $_SESSION['selectedFilters']['status'] : '';
$sectors = isset($_SESSION['selectedFilters']['sector']) ? $_SESSION['selectedFilters']['sector'] : '';
$funds = isset($_SESSION['selectedFilters']['fund']) ? $_SESSION['selectedFilters']['fund'] : '';
// Build your pdoNeighbors call dynamically
$whereConditions = [];
// // Add conditions based on the query parameters
// if (!empty($status)) {
// $pdoParams['where']['portfolioStatus:IN'] = $status;
// }
if (!empty($sectors)) {
// Special case for Multi Select list - in database stored like that: value||value2
foreach ($sectors as $sector) {
$whereConditions[] = [
'portfolioSector:LIKE' => '%' . $sector . '%'
];
}
}
if (!empty($funds)) {
foreach ($funds as $fund) {
$whereConditions[] = [
'portfolioFund:LIKE' => '%' . $fund . '%'
];
}
}
print_r($whereConditions);
$pdoParams = array(
'parents' => $resource->get('parent'),
'depth' => 0,
'limit' => 0,
// 'showHidden' => 1,
'showHidden' => 1,
// 'tplNext' => 'nextButton',
// 'tplWrapper' => 'pdoNeighborsNavigation',
'includeTVs' => 'portfolioStatus,portfolioSector,portfolioFund,portfolioRealisationDate,portfolioInvestmentDate',
// 'sortby' => '{"portfolioStatus":"ASC","portfolioRealisationDate": "DESC","portfolioInvestmentDate":"DESC"}',
'where' => $whereConditions,
'showLog' => 1,
'tpl' => '@INLINE [[+id]],'
);
// Use pdoNeighbors to fetch neighboring resources
$output = $modx->runSnippet('pdoResources', $pdoParams);
return $output;
}
I want to be able to some how group querys for Sectors and Funds, to compare them this way:
Sector LIKE value OR Sector LIKE value2 AND Fund LIKE value3 OR Fund LIKE value
In my query everything is AND, want to be able to control it somehow. The question is how to make is customizable, please bare in mind I’m doing this in the loops.
Oh you are right!
I understand now, however I’m still not getting right result because:
WHERE ( `TVportfoliostatus`.`value` = 'realised' AND `TVportfoliofund`.`value` = 'fund-ii' AND `TVportfoliosector`.`value` LIKE '%logistics%' OR `TVportfoliosector`.`value` LIKE '%utilities%' AND `modResource`.`parent` IN (16) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 )
if I have more than one ‘Sector’ then there is some other resources added even if they don’t match rest of query, is that something I’m missing, like Where its not strict enough?
WHERE (
`TVportfoliostatus`.`value` = 'realised'
AND `TVportfoliofund`.`value` = 'fund-ii'
AND `TVportfoliosector`.`value` LIKE '%logistics%'
OR `TVportfoliosector`.`value` LIKE '%utilities%'
AND `modResource`.`parent` IN (16)
AND `modResource`.`published` = 1
AND `modResource`.`deleted` = 0
)
This query is missing the brackets for the “TVportfoliosector” part.
It would be correct like this:
WHERE (
`TVportfoliostatus`.`value` = 'realised'
AND `TVportfoliofund`.`value` = 'fund-ii'
AND (
`TVportfoliosector`.`value` LIKE '%logistics%'
OR `TVportfoliosector`.`value` LIKE '%utilities%'
)
AND `modResource`.`parent` IN (16)
AND `modResource`.`published` = 1
AND `modResource`.`deleted` = 0
)
To get this result, you must put all the where conditions for the “TVportfoliosector” in its own array (as shown in my code above).
No, I have never seen such a syntax (with only AND and OR as keys in the array → 'AND' => [ ...] , 'OR' => [...]).
If you’re using xPDO directly, you could add OR conditions with a different syntax (e.g. $query->where(['width' => 10],xPDOQuery::SQL_OR); as described in the documentation), but I don’t think this would work with pdoResources.
This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.
