add a sql query to a pages->find() operation?

[bernhard]

hi,

i have a quite complex find operation to do. i already setup those find operations via $pages->count('my selector')

the problems is, that i have to exclude some pages from this result. its not possible to put this into a sub-selector because its a complex query that has to do some group by and date comparison and so on. i want to stay with the pages->count() on the other hand because changing completely to an sql query would need some refactoring...

what i have now:

<?php
$count = $pages->count(
    'template=rockacp'.
    ',rockacp_start>'.$fromto[0].
    ',rockacp_start<'.$fromto[1]
    );

what i need is something like this:

<?php
$count = $pages->count(
    'template=rockacp'.
    ',rockacp_start>'.$fromto[0].
    ',rockacp_start<'.$fromto[1].
    ',client.id!=1|2|3|4'
    );

In the example it would be fine, i'm just wondering what would happen if the number of pages gets too large? I don't like the idea of creating an pipe-separated id string and using this for my count operation...

any better ideas for this?

thank you

[adrian]

Surely if it's a count operation then you could just subtract the number of pages that need to be excluded - no need to add them to the selector.

Or is count() just an example and you do actually need to return the pages?

[bernhard]

Hmmm... Thank you but that's not what I'm looking for. It's not that easy. I'll try to explain more in detail.

I have clients on the one hand and projects on the other. Then I also have acquisition processes (acp) that connect clients and projects (like acp "get client x for project y" would connect client x and project y).

It's easy to select all acp that started in a given timeframe (first example). But now I need to select only acps that are related to a special type of client. The problem is that this type is dynamically depending on the given year.

I have three types of clients: Clients that  already had some revenue in the years before , clients that had their first revenue in the current year and clients that did not have any revenue at all.

So I cannot count the started processes and subtract the number of clients...

I think I have to go the SQL way, but I wanted to make sure that there is no other magical PW way like subselectors. I guess in my case, as I'm querying a custom dB view it's best to go with SQL

[LostKobrakai]

How about using a $pages->find() to just get the clients of your needed type for the timeframe (might be far less than the number of acps) and then use their id's to limit the count?

[bernhard]

Yes that would be what I need and what I stated in my first post. But still I think it would be the best to get those pages by sql. As I said I'm using a custom dB view. The question is how to exclude the IDS properly.

Create an ID string by foreach-ing all SQL results?

[BitPoet]

Another thought: give the clients a (hidden) field with the year of first revenue, populate it once with existing data and hook into whatever template determines that year to update the field (or update it in a nightly batch if a small delay isn't that much of an issue). This gives you the opportunity to use a simple selector and, most importantly since you're already thinking of growth, scales a lot better than any view.

[bernhard]

Hm. I already had this field in place but I removed it because I didn't want to mess with hooking and having bugs like forgetting to update the field on page delete or the like. Thank you for your input, I'll take a second thought on this

[LostKobrakai]

1 hour ago, bernhard said:

The question is how to exclude the IDS properly

client.id!=1230|1230|1230 should work.

[bernhard]

Yes, that was one part of the question: how scalable is it...?

[LostKobrakai]

I'm sure below 1000 of those clients you shouldn't really notice it. Especially as you're only loading the id's and not their whole pages. You could always improve that part later if you see any real life issues.

[bernhard]

Thank you. Was just wondering if there's a maximum length for a selector string or something like that

[kongondo]

8 minutes ago, bernhard said:

Thank you. Was just wondering if there's a maximum length for a selector string or something like that

There is actually, but can't remember the number 

Edit (not sure if still current; yep, still 500):

 

 

Edited February 26, 2017 by kongondo

[szabesz]

17 hours ago, bernhard said:

Was just wondering if there's a maximum length for a selector string or something like that

Swithcing to "selector arrays" might help to workaround this limitation:

https://processwire.com/blog/posts/processwire-3.0.13-selector-upgrades-and-new-form-builder-version/#selector-engine-array-support

[bernhard]

Thank you szabez!! Your post looks ideal for me and helped a lot selector arrays would be much better in other places of my projects code as well