I think you're using a version of webgui that is newer than what I'm familiar with. But could those other posts be showing up because previous revisions do not have the status of archived? You're matching revisionDate, but don't seem to be limiting to the Max revision date anywhere.

Hope this helps,

Susan B

--- (Edited on 4/30/2012 10:45 [GMT-0700] by susanb) ---

@susanb:

Thanks for the suggestion. I think this clause from the WebGUI SQL Cookbook (http://www.webgui.org/wiki/sql-cookbook) is similar, or exactly, what I need:

AND Post.revisionDate = (
    SELECT MAX(maxPostData.revisionDate)       
    FROM Post as maxPostData       
    WHERE Post.assetId=maxPostData.assetId
)

@perldreamer:

I think assetData.status is the right field, because I am archiving the threads using the same routine that the "ArchiveOldThreads" activity uses, but basing it on a custom date field instead of how old the thread is.

This discussion of "state" vs. "status" supports this tack:

http://www.webgui.org/webgui/dev/discuss/state-and-status-for-assets/

William suggested I check to make sure that my code isn't accidentally spawning a second version tag of the thread. I don't think it is, because it's based on /data/WebGUI/lib/WebGUI/Workflow/ActivityArchiveOldThreads.pm.

Here are the relevent lines, with all the error-checking omitted:

   my $cs = WebGUI::Asset::Wobject::Collaboration->new( $session, $collab_id );
...
    my $threadIter = $cs->getLineageIterator(['children'],{
        includeOnlyClasses      => [ 'WebGUI::Asset::Post::Thread' ],
        statusToInclude         => [ 'approved' ],
    } );

(loop)
        my $thread;
        eval { $thread = $threadIter->() };
...
        if ( $thread->get('userDefined1') < $now ) {
            $thread->archive;
...

--- (Edited on 5/1/2012 2:49 pm [GMT-0500] by vanjwilson) ---

The SQL Report, with that SQL, will report multiple revisions of assets, and assets in the Trash and Clipboard.  To fix both of those, you need to check asset.state (in addition to asset.status), and to find the max(revisionDate).

I think you can fix this very easily: assets in assetIndex have the right state and status and are unique, so you don't need the subquery or any additional conditions. Always a headache to figure those out... To make use of all the clever work that has already been done building the index, you should change the joins on assetData and Post and use the double primary key as criterium, like so:

...
FROM
assetIndex
JOIN
asset USING (assetId)
JOIN
assetData USING (assetId, revisionDate)
JOIN
Post USING (assetId, revisionDate)
...

This will ensure you have only Posts, which are published and approved and only the latest revision of each (assuming that assetIndex is up to date). Note: without either an (inner) join on Threads, also using assetId and revisionDate, you will still get replies (= posts that aren't threads) as well as threads. Alternatively, you could test if the asset.className equals WebGUI::Asset::Post::Thread to prevent that.

Hope this helps, good luck,
Rogier

--- (Edited on 02-05-2012 00:41 [GMT+0200] by rogier) ---

@rogier,

Thanks for the suggestions about the joins. I took the MAX revision date subquery out, and arranged the joins the way you suggested. It seems to have fixed the duplicate-revisions problem.

I don't have to worry about replies, because these Threads are being used to announce upcoming events, and commenting is not allowed.

--- (Edited on 5/2/2012 6:05 am [GMT-0500] by vanjwilson) ---

--- (Edited on 5/2/2012 6:06 am [GMT-0500] by vanjwilson) ---