Can the $pl->filter() function filter out a page by its page ID# ?
Permalink 1 user found helpful
Hey friends,
I read this in the help docs:
$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 explain how to use this function?
Specifically, to filter OUT a page by ID?
( so far I've been able to hack together a different solution but there has to be an easier way and this filter function seems to be the key )
I'm not even sure how to determine what columns the page list has access to? I wish I could find more info regarding the use of the filter method it seems like a powerful feature that deserves a few tutorials.
thanks in advance
I read this in the help docs:
$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 explain how to use this function?
Specifically, to filter OUT a page by ID?
( so far I've been able to hack together a different solution but there has to be an easier way and this filter function seems to be the key )
<?php // filter OUT page with id 401 $pl->filter('cID', 401, '!='); //error // ...2nd try... $pl->filter(false, '(cID != 401)'); //error ?>
I'm not even sure how to determine what columns the page list has access to? I wish I could find more info regarding the use of the filter method it seems like a powerful feature that deserves a few tutorials.
thanks in advance
Hi Ollie, the page list model is loaded, thanks for clarifying as I'm sure that it's common gotcha.
The error is this ugly thing
and after reading Jordans post below I now understand what all these references to "p1' are actually dynamic references to the page in question ( something like that? )
The error is this ugly thing
mysql error: [1052: Column 'cID' in where clause is ambiguous] in EXECUTE("select p1.cID, pt.ctHandle from Pages p1 left join Pages p2 on (p1.cPointerID = p2.cID) left join PageTypes pt on (pt.ctID = (if (p2.cID is null, p1.ctID, p2.ctID))) left join PagePaths on (PagePaths.cID = p1.cID and PagePaths.ppIsCanonical = 1) left join PageSearchIndex psi on (psi.cID = if(p2.cID is null, p1.cID, p2.cID)) inner join CollectionVersions cv on (cv.cID = if(p2.cID is null, p1.cID, p2.cID) and cvID = (select cvID from CollectionVersions where cvIsApproved = 1 and cID = cv.cID)) inner join Collections c on (c.cID = if(p2.cID is null, p1.cID, p2.cID)) left join CollectionSearchIndexAttributes on (CollectionSearchIndexAttributes.cID = if (p2.cID is null, p1.cID, p2.cID)) where 1=1 and cID != '401' and cvIsApproved = '1' and (p1.cIsTemplate = 0 or p2.cIsTemplate = 0) and ((select count(cID) from PagePermissions pp1 where pp1.cID = if(p2.cID is null, p1.cInheritPermissionsFromCID, p2.cInheritPermissionsFromCID) and ((pp1.cgPermissions like 'r%' and cv.cvIsApproved = 1) or (pp1.cgPermissions like '%rv%')) and ( (pp1.gID in (1) or pp1.uID = -1) and (pp1.cgStartDate is null or pp1.cgStartDate <= '2011-10-01 17:29:31') and (pp1.cgEndDate is null or pp1.cgEndDate >= '2011-10-01 17:29:31') )) > 0 or (p1.cPointerExternalLink !='' AND p1.cPointerExternalLink IS NOT NULL )) and ak_blogga_post = '1' and (p1.cID not in (5,6,7,8,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,43,44,45,46,47,48,49,50,51,52,53,54,55,77,78,79,80,81,116,117,118,119,348,347,346,345) or p2.cID not in (5,6,7,8,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,43,44,45,46,47,48,49,50,51,52,53,54,55,77,78,79,80,81,116,117,118,119,348,347,346,345)) order by cvDatePublic desc limit 0,3 ")
and after reading Jordans post below I now understand what all these references to "p1' are actually dynamic references to the page in question ( something like that? )
In SQL they're called "aliases", which is just a shorthand way to make the query text easier to deal with (and also a way to include the same table more than once in a query but apply different logic to it in different places -- yeah it gets real tricky sometimes).
For example, if you query was this:
...you could use aliases to shorten that up to this:
That's a contrived example of course, but makes a big difference in ridiculously complicated queries like the one C5 uses for the PageList.
For example, if you query was this:
SELECT * FROM Pages INNER JOIN CollectionVersions ON Pages.cID = CollectionVersions.cID;
...you could use aliases to shorten that up to this:
SELECT * FROM Pages p INNER JOIN CollectionVersions cv ON p.cID = cv.cID;
That's a contrived example of course, but makes a big difference in ridiculously complicated queries like the one C5 uses for the PageList.
I feel enlightened ... MySQL, I need to get to know you better :)
So that explains what all this is at around line 315 in the page_list model:
Cool, I'm ganna go get my filter on, thanks again for the help, I'm curious if anyone else has examples of using the filter function in creative ways? I love examples :)
-Aaron
So that explains what all this is at around line 315 in the page_list model:
$this->setQuery('select p1.cID, pt.ctHandle ' . $ik . $additionalFields . ' from Pages p1 left join Pages p2 on (p1.cPointerID = p2.cID) left join PageTypes pt on (pt.ctID = (if (p2.cID is null, p1.ctID, p2.ctID))).........
Cool, I'm ganna go get my filter on, thanks again for the help, I'm curious if anyone else has examples of using the filter function in creative ways? I love examples :)
-Aaron
Check out the "getRelatedContentPages" function in the block controller of the attached package -- stuff gets crazy when you want to filter on "select" type attributes!
This is really cool, especially line 60 comments re filter function.
I probably should invest a little time tinkering with this before asking more questions... but, at a glance I am still a little confused by the alias values for c.cID & p1.cID ... I understand that 'p1' is an alias for 'Pages', and I can see that 'c' is an alias for 'Collections' , um, silly question, aren't Pages the same thing as Collections?
I probably should invest a little time tinkering with this before asking more questions... but, at a glance I am still a little confused by the alias values for c.cID & p1.cID ... I understand that 'p1' is an alias for 'Pages', and I can see that 'c' is an alias for 'Collections' , um, silly question, aren't Pages the same thing as Collections?
They are conceptually the same thing but for whatever reason deep in the bowels of the system they are somewhat different. Well, not different, but it seems that Pages are a superset of Collections -- like you could have a collection that isn't a page but a page is always a wrapper around a collection that provides some additional functionality. I have no idea why this is -- perhaps it's just a historical artifact of how the system was developed or something. But if you look in the database you'll see that there's both a Collections and a Pages table. Also if you look in the concrete/models/ directory, you'll see two separate model files -- collection.php and page.php
I should remember to ask this question to Andrew next time they do the UStream show as I've always wondered why this is set up this way.
I should remember to ask this question to Andrew next time they do the UStream show as I've always wondered why this is set up this way.
Yeah, would definitely be nice if this were better documented.
There's no built-in filter for CID, but if you pass in "false" as the first argument of the $pl->filter() method, then the 2nd argument becomes a portion of a SQL WHERE clause. But as you surmised, there's no way to know which tables it's using in the query. What I've done is step through the code with a debugger to see what it's doing, and for most fields you can use the "p1" alias on the pages table. So this should work:
If you look through the code of concrete/models/page_list.php, you'll see a bunch of example "WHERE" clauses that the system uses for various filters, which will give you some idea of what's available. Also, in Concrete5.4.2.1, look at lines #332 and #334 to see the "FROM" portion of the entire query.
There's no built-in filter for CID, but if you pass in "false" as the first argument of the $pl->filter() method, then the 2nd argument becomes a portion of a SQL WHERE clause. But as you surmised, there's no way to know which tables it's using in the query. What I've done is step through the code with a debugger to see what it's doing, and for most fields you can use the "p1" alias on the pages table. So this should work:
$pl->filter(false, 'p1.cID != 401');
If you look through the code of concrete/models/page_list.php, you'll see a bunch of example "WHERE" clauses that the system uses for various filters, which will give you some idea of what's available. Also, in Concrete5.4.2.1, look at lines #332 and #334 to see the "FROM" portion of the entire query.
thanks man, I was I guess confused by these references to 'p1' in the page_list model ... now I get it, and thanks for that hint, does the trick works as expected. Sweet!
//exclude page from page list by page id value $pl->filter('p1.cID', 401, '!='); // works!!! //...or... $pl->filter(false, '(p1.cID != 401)'); // works!!!
I tend to not use the aliased table names, since they just confuse me :P
Hmm... I'm not sure that would work in this case because the page list query includes some tables more than once, so you'd still wind up with ambiguous column name errors in the case of the Pages table, for example (I think... haven't tested though).
Looking at the methods in that class I'd have said your second example should work?
Is it returning without the filter. And have you instantiated the object using this code first.