How to do Page List filtering IF this AND that?
Permalink
I have a hard-coded page list search filter where I am filtering by a certain page type and then by the page owner, like this:
Works great, but now I have a new challenge... have to generate search results for ALL pages, but if the page is of type "blog_post" then the page owner's id needs to be in the $uIDs array. I imagine this could be done using a SQL query in $pl->filter() but I don't even know where to start with the SQL query... or maybe there is a better way?
Here's the documentation fromhttp://www.concrete5.org/documentation/developers/pages/searching-a... :
Can anyone give me a pointer?
$pl->filterByCollectionTypeHandle('blog_post'); $pl->filter('p1.uID', $uIDs, '='); // $uIDs is an array of id numbers $pl->filterByKeywords($query); // $query is a keyword search $pl->sortByRelevance();
Works great, but now I have a new challenge... have to generate search results for ALL pages, but if the page is of type "blog_post" then the page owner's id needs to be in the $uIDs array. I imagine this could be done using a SQL query in $pl->filter() but I don't even know where to start with the SQL query... or maybe there is a better way?
Here's the documentation fromhttp://www.concrete5.org/documentation/developers/pages/searching-a... :
$pl->filter($column, $value, $comparison) Advanced users: Passes a fliter directly to the "WHERE" clause. The value of $column must be a valid database column that's referenced in the PageList query. Setting the value of $column to false will allow you to pass complex SQL into the $value field ex: $pl->filter(false, '(ak_age = 10 OR ak_age IN (13,17,25) OR ak_age > 23)'); where 'age' would be the handle of a numeric page attribute
Can anyone give me a pointer?
Thanks for your response, John.
I'm not sure if I communicated the intent clearly or maybe I misunderstood your reply.
In the resulting list there might be:
pages that are not blog posts ("blog_post" page type) and have ANY page owner
pages that ARE blog posts and MUST ONLY be owned by certain users
So the filtering in pseudo-code is:
1 - is the page NOT a blog_post? ... include
2 - is the page a blog_post AND owned by a particular user ... include
3 - if not #1 or #2 ... EXclude (i.e. if a blog_post *and* NOT owned by a particular user)
So it's both an IF and an AND that I need.
Is that the use case you're describing?
p.s. — I'm not averse to buying a block, but this is only part of the requirements so I'd rather use code for flexibility's sake.
I'm not sure if I communicated the intent clearly or maybe I misunderstood your reply.
In the resulting list there might be:
pages that are not blog posts ("blog_post" page type) and have ANY page owner
pages that ARE blog posts and MUST ONLY be owned by certain users
So the filtering in pseudo-code is:
1 - is the page NOT a blog_post? ... include
2 - is the page a blog_post AND owned by a particular user ... include
3 - if not #1 or #2 ... EXclude (i.e. if a blog_post *and* NOT owned by a particular user)
So it's both an IF and an AND that I need.
Is that the use case you're describing?
p.s. — I'm not averse to buying a block, but this is only part of the requirements so I'd rather use code for flexibility's sake.
OK, clearer now. My addon based solution wouldn't make any difference to what @Jordanlev says, you would just be running and combining 2 lists using Magic Data rather than using php and the page list class.
If you have a big list to so need the fastest possible query, there have recently been some posts on enhanced page lists or general query builders that may make it easier to do with some custom SQL.
http://www.concrete5.org/community/forums/customizing_c5/new-query-...
If you have a big list to so need the fastest possible query, there have recently been some posts on enhanced page lists or general query builders that may make it easier to do with some custom SQL.
http://www.concrete5.org/community/forums/customizing_c5/new-query-...
Okay, thanks for the clarification, John.
And the link to the other thread(s)... I love to see people optimizing core features and hope that leads to making the system better for everyone.
And the link to the other thread(s)... I love to see people optimizing core features and hope that leads to making the system better for everyone.
Hey Kirk,
I don't think there's an easy way to combine clauses in the way you describe. I think you need to either create your own SQL string (as you suggested), or you could run two different PageList queries and combine the results at the end.
The latter approach (combining two query results) would be simpler, but if you are relying on the pagination feature then it won't work well with that. But if you don't need pagination, you can just run two separate PageList things... one for all the pagetypes *other than* 'blog_post' (which you'd probably need to do by making multiple calls to $pl->filterByCollectionTypeHandle() for each other pagetype), then one that combines 'blog_post' with user id. Then after you have the two results (via $pl->get()), combine the two arrays via php's array_merge() function.
Now that I've written all that out, it actually doesn't sound so simple anymore :) So if you want to go the SQL route, you do this:
Hope that helps!
-Jordan
I don't think there's an easy way to combine clauses in the way you describe. I think you need to either create your own SQL string (as you suggested), or you could run two different PageList queries and combine the results at the end.
The latter approach (combining two query results) would be simpler, but if you are relying on the pagination feature then it won't work well with that. But if you don't need pagination, you can just run two separate PageList things... one for all the pagetypes *other than* 'blog_post' (which you'd probably need to do by making multiple calls to $pl->filterByCollectionTypeHandle() for each other pagetype), then one that combines 'blog_post' with user id. Then after you have the two results (via $pl->get()), combine the two arrays via php's array_merge() function.
Now that I've written all that out, it actually doesn't sound so simple anymore :) So if you want to go the SQL route, you do this:
$safeUIDs = array_map('intval', $userIDsArray); //ensure all UID's are integers, otherwise we're vulnerable to SQL injection attacks! $safeUIDString = implode(',', $safeUIDs); $where = "(pt.ctHandle <> 'blog_post' OR (pt.ctHandle = 'blog_post' AND (p1.uID IN ({$safeUIDString}) OR p2.uID IN ({$safeUIDString}))))"; $pl->filter(false, $where);
Hope that helps!
-Jordan
Thanks, Jordan!
I was definitely hoping to preserve the pagination features, so combining two sets was considered and initially discarded. May have to go back to that, but this:
is exactly the kind of prompt I was hoping for! I'll see if I can make it work now.
I'm curious, though: if the query uID is, say, 8 does the filtering "okay" a page that has 8 as a partial in the safeUIDString, such as 18 or 84?
I was definitely hoping to preserve the pagination features, so combining two sets was considered and initially discarded. May have to go back to that, but this:
$where = "(pt.ctHandle <> 'blog_post' OR (pt.ctHandle = 'blog_post' AND (p1.uID IN ({$safeUIDString}) OR p2.uID IN ({$safeUIDString}))))";
is exactly the kind of prompt I was hoping for! I'll see if I can make it work now.
I'm curious, though: if the query uID is, say, 8 does the filtering "okay" a page that has 8 as a partial in the safeUIDString, such as 18 or 84?
The SQL as I wrote it will compare the full numbers only (so "8" will only match "8", not "18" or "84"). The "IN" comparison is for checking if a value exists within a comma-separated list of possibilities (as opposed to LIKE, which checks portions of strings).
-Jordan
> On May 8, 2014, at 12:07 PM, concrete5 Community <discussions@concretecms.com> wrote:
-Jordan
> On May 8, 2014, at 12:07 PM, concrete5 Community <discussions@concretecms.com> wrote:
Great, thank you Jordan. Very helpful, as always.
Finally got back to this project and just wanted to check back in to say Jordan's SQL worked perfectly. Thanks again, Jordan!
If you want to avoid code, you can build such lists without any php using my Uber List and Magic Data addons.