Have you seen:

http://www.concrete5.org/community/forums/customizing_c5/how-i-lowe...

I think you may have been working in parallel and come up with similar solutions.

I can sort of follow what is going on, but would feel more confident working with this if the examples contained comments to explain the what and why of all the configuration tables they are setting up to build a query.

Thanks for alerting me to the other solution. I feel a bit better in a sense that it is a common issue, and maybe we can pool our resources. I'll work on commenting the code to make it cleaner (the base class is at least fully phpdoc'd, since there is a LOT going on there).

I'll try to add explanations on more of the tables/queries, and a summary rundown on what the examples are trying to do in the readme.

It IS a big class, it almost needed to be with so many different things that may be needed (for example, I WAS imploding with AND until only a week ago, when I ran into the possible need for an OR query, at which point I had to rip that all apart).

This is definitely something that sticks out like a sore, poorly-performing thumb when you start grabbing large page-lists and reading attributes. My solution was much more specific to that situation, though I'm investigating a different design approach to the one you chose.

I think this is something that could be generalized and built into the existing PageList, Page, and Attribute classes. Each attribute could be configured for 'prefetchability', where it would return a LEFT JOIN that the PageList could use when fetching. You call PageList while setting $pl->prefetchAttribute('attribute_handle'), and that would load the value into Page. If $page->getAttribute('attribute_handle') has a prefetch value stored, it'd load that prefetched value, otherwise just run along its regular getAttribute() codepath.

As a plus, that PageList::get() could be cleaned up at the same time, so all that foreach(DatabaseItemList::get()) logic it does to load the Pages from the DIL, to instead load all the Pages in one big query. Should be a massive perf improvement for any sites with big page lists, especially those with remote DBs.

Your last paragraph is pretty much what Remo is lookin into:

https://github.com/Remo/concrete5/commit/c7d335a49b96204a3a6b8b60cd1...

My solution was geared toward a more generic situation than just the C5 base, hence the method I chose (forgoing the base almost entirely).

I mean, I happened to need to deal with two of the three attribute types, but that was more coincidental than anything else.

My method is best for a single page, data driven website style, which is exactly what the code was crafted for. I had an event need, pro-events didn't fit their needs, so I had to create my own.

What's key to this is the flexibility. It can work with any attribute type (including user created ones). It can filter really easily (which is great for me since this site has lots of search filter options). So, for people who want to use this as part of a data driven style site, this is a major timesaver (excepting the minimal time needed to understand the code).

I'm still learning the "C5 way" of doing things, but for this, an extensible, robust, and flexible query builder seemed the right choice. It's great for those member list blocks that seem to be needed on so many sites.

I'm sure the gurus on here have a better idea of how to deal with this in the C5 internals. I'm sure I could hack it, but it seems people like remo are already doing it, and they know the code way better than I do. Hence why my pull requests all are form centric for now....

Side note: I see that prepared statements are used in the PageList workaround. That works for that situation, but in this one, there are so many filter variables, so generated sql is the only way to deal with it.

Also bear in mind that Page Lists are a special case because the searchable attributes are added to the page list search index, thus providing an quick and efficient route to attribute based searching (as long as the index is up to date), as used by the existing page list.

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:

$safeUIDs = array_map <http://www.php.net/array_map>('intval', $userIDsArray); //ensure all UID's are integers, otherwise we're vulnerable to SQL injection attacks!
$safeUIDString = implode <http://www.php.net/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!

http://www.concrete5.org/community/forums/customizing_c5/how-to-do-...




On 06/04/2014 00:44, concrete5 Community wrote:

How would you licence use of this code? Supposing a developer wanted to use it in a marketplace addon?

I'll be honest, I don't know licenses, and I'll look into which ones are appropriate. Likely a gpl style license.

I made this for a project, but my hope is that it will help others struggling with this kind of issue who doesn't want to have to do all the background work (or who wants to come up with some cool tweak to make it better).

Much like I use jordanlev's great crud class (on the same site actually), I want this class to be free to use. I mean, if someone wants to pay me, great, but let's be honest, not likely to happen :)

If you are looking for a big uptake of this, the best licence to use is probably the same as the c5 core or MIT.

Making it GPL would mean that anything that integrates on top of this also needs a free option (EDIT: and may also preclude this code ever making it into the core).

Browsing through the main class code I could see this is a necessarily big class. It pulls queries together that are spread over so many classes in the c5 core.

MIT license added, thanks for the hint (I did some looking at other licenses, and for sure MIT is what I was going for, I have no patents to worry about).

Also, I did a major rework of all the example classes today, cleaning out extraneous code that was making it harder to understand, and documenting things to make it clearer. Additionally, I added a db.xml file to show the example classes table structure.

Let me know if that makes things cleaner! Thanks for your input!

I think I am starting to get it, especially with the user list as it has an analogue in the core I am already familiar with, so I know what it is supposed to be doing.