SOQL Selecting Chatter comments if been created or has been replied to in the last 24 hours

Question

Hey guys I've been experimenting with different queries to try and get all Chatter Posts and their comments within the last 24hr period, but realize that doing this without Apex Triggers is much more difficult with pure SOQL than not.

I've tried using Multi-level Relationships on Chatter Comments example but this doesn't seem to work for me as I am not using objects. I'm hoping someone could give me a hand with creating the correct SOAP soql call to get Chatter Posts (and replies) that have been created or commented on in the last 24 hours.

Thanks guys!

Answer 1

This will Select correctly based on the question above, hope this helps someone else. Cheers.

SELECT 
        Id, 
        LastModifiedDate,
  (
    SELECT 
            Id, 
            CreatedDate

    FROM FeedComments 

    ORDER BY CreatedDate ASC
  )

FROM UserFeed

WHERE (
            (
              ParentId = {$this->uid}
            )

        AND (
              LastModifiedDate > {$this->lastModStamp}
            )
      )

OR    (
            (
              CommentCount > 0 
            )

        AND (
              CreatedDate > {$this->lastModStamp} 
            )
      )

ORDER BY CreatedDate DESC;

A good way to test this is to flip the

OR ((CreatedDate > YESTERDAY) AND (CommentCount > 0))

to

OR ((CreatedDate < YESTERDAY) AND (CommentCount > 0))

and use something like this to run through it

        header('Content-Type: text/plain');

        $query      = str_replace('{/id}', "'".SF_UID."'", $_POST['q']);
        $response   = $mySforceConnection->query($query);
        $sResult    = New QueryResult($response);

        $i=0;
        foreach ($sResult as $splObject)
        {
            echo "ID: {$sResult->records[$i++]->Id[0]}\n";
            print_r($splObject->fields);
            echo "\n\n";

            if (is_array($splObject->queryResult))
            { 


                $fResult    = New QueryResult($splObject->queryResult[0]);
                $j=0;

                echo "\n---------------------------------------------\n";
                foreach ($fResult as $fplObject)
                {
                    echo "ID: {$fResult->records[$j++]->Id[0]}\n";
                    print_r($fplObject->fields);
                    echo "\n\n";
                }
                echo "\n\n\n\n\n\n";
            }
        }
    }