Jump to content

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


bernhard
 Share

Recommended Posts

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 :)

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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 by kongondo
  • Like 2
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...