plainblack.com
Username Password
search
Bookmark and Share

    

SQL Report not excluding archived Threads

User vanjwilson
Date 4/30/2012 8:50 am
Views 801
Rating 0    Rate [
|
]
Previous · Next
User Message
vanjwilson

We have a custom SQL Report that is supposed to show upcoming events for a client. We recently started running a nightly script that would automatically "archive" threads when a custom field (not the posting date/time), which held the epoch time of the event, had passed.

This works great on two of their pages, which use the default collaboration system template, but it does not work on the listing page, which is searchable and using the custom SQL Report below. (The last WHERE clause--assetData.status LIKE 'approved'

select
    assetIndex.assetId
from
    assetIndex
left join
    asset using ( assetId )
left join
    Post using( assetId, revisionDate )
left join
    assetData using ( assetId )
where
    ( parentId = ? )
and
    ( ( assetIndex.assetId in (
        select
            assetId
        from
            metaData_values
        where
            fieldId = ?
        and
        value like concat( "%", ?, "%" )
        and
            metaData_values.revisionDate = assetIndex.revisionDate )
      )
      OR
      ( ? is NULL and ? is NULL
      )
     )
and
    (
      ( Post.userDefined1 > ?
        and
        Post.userDefined1 < ?
      )
      OR
      ( ? is NULL and ? is NULL )
    )
and
    (
        assetData.status LIKE 'approved'
    )
order by
    Post.userDefined1 asc;

I've tried clearing the WebGUI cache and the client's browser cache, but those things did not work.

When I look at the individual threads, the old ones have a Status of "Archived", and I see in the logs when our nightly script archives them. It's just this SQL Report that is not honoring that.

--- (Edited on 4/30/2012 8:50 am [GMT-0500] by vanjwilson) ---



Back to Top
Rate [
|
]
 
 
susanb

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



Back to Top
Rate [
|
]
 
 
perlDreamer

Susan is right.

You should only look at the most recent revisionDate of an approved asset.

Then, you need to consider asset state, as well as asset status.  This will prevent you from finding posts and threads that are in the trash, or the clipboard.

--- (Edited on 4/30/2012 11:26 am [GMT-0700] by perlDreamer) ---



Back to Top
Rate [
|
]
 
 
vanjwilson

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



Back to Top
Rate [
|
]
 
 
perlDreamer

First of all, the getLineageIterator API method handles getting the latest revision of an Asset.

The SQL that you posted does not.

getLineageIterator also, by default, ignores assets in the Clipboard and in the Trash.

Again, the SQL that you posted does not.

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

--- (Edited on 5/1/2012 2:06 pm [GMT-0700] by perlDreamer) ---



Back to Top
Rate [
|
]
 
 
rogier

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

http://en.rogiervoogt.com/

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



Back to Top
Rate [
|
]
 
 
rogier

By the way, clever joining will beat the MAX(revisionDate) subquery solution from the cookbook on performance. Should make a noticable difference if the database gets big.

R.

http://en.rogiervoogt.com/

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



Back to Top
Rate [
|
]
 
 
vanjwilson

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



Back to Top
Rate [
|
]
 
 
© 2014 Plain Black Corporation | All Rights Reserved