How to get a doctrine loop to find the number of grouped records

Question

I am working on a query that is returning all published records, and grouping and ordering them by the latest updated_version.

Then I am filtering that result to show the results that were updated in the last 24 hrs, week, and month.

All works dandy. However, I would like to add the condition that if no records have been updated in each of these three date criteria, then to echo "No records have been updated".

I am curious if I can isolate these groups in the query and check against that, or possibly set a variable in the loop. The issue with the loop is, I can get the 0 result condition in the loop, but because it is checking INSIDE the loop, I get an echo of "No results found" for each record in the loop.

OK, Here is the query:

//return all unpublished records
$draftEntries = Doctrine::getTable("foo")
    ->createQuery()
    ->where('published = 0')
    ->groupBy('updated_at')
    ->orderBy("updated_at DESC")
    ->execute();

And the loop:

$message .= "These profiles were edited within the last 24 hours: \n";                      
    foreach ($draftEntries as $entry) {
        $currentDay = substr($entry['updated_at'], 0, 10);
        $condition = false;
        if($currentDay == $today) {
            $condition = true;
            $message .= $entry['last_name'] . ", " . $entry['first_name'] . "\n"; 
        else {
            $message .= "There were records updated yesterday";
            echo $condition;
        }
    }

This is just on of the three loops, but I think you get the gist of my intention. Obviously, this is the loop that returns:

There were records updated yesterday. There were records updated yesterday. There were records updated yesterday. ...

which is not desired.

So, from the query, can I check to see if the groupBy is greater than 0? Surely I can do this without setting up three queries to get a result right?

score 0 · Accepted Answer · answered Aug 24 '12 at 19:17

This is how I solved this. If there is a better answer, let me know.

I didi not try to isolate the group in the query, I just used a few conditional statements in the loop:

//Edited in the last 24 hours
    $message .= "<p>These profiles were edited within the last 24 hours: </p><ul>\n";
    $lineitem = "";                     
    foreach ($draftEntries as $draftentry) {
        $currentDay = substr($draftentry['updated_at'], 0, 10);
        if($currentDay == $today) { 
            $listpiece .= $draftentry['id'];
            $listpiece .= "&profile_type=faculty'>".$draftentry['last_name'] . ", " . $draftentry['first_name'] . "</a> / Edited by: ".$draftentry['last_updater']."</li> \n"; 
            $lineitem .= $listpiece;
        }
    }
    if ($lineitem == "") {
        $message .= "No edits were made.";
    }
    else {
        $message .= $lineitem;
    }

I am concatenating the $message, but I needed to formulate a condition for what gets included in the $message outside the loop. $lineitem represents a single entry in the loop. It is instantiated outside the loop, then can be passed data according to the if statement inside the loop.

SO far, it works pretty well.

How to get a doctrine loop to find the number of grouped records

1 Answers1