The assetIndex table is part of WebGUI's search system, also described in some detail in JT's Black Blog. Because the primary purpose is to facilitate searching for current versions of content, each asset gets only one entry in the table, representing the current committed version of the asset. This makes the table very useful, not only for what most of us think of as "searches" on content, but also for SQL Reports. For example, finding the 3 most recent posts to a forum or a list of tomorrow's events.

The important point here is that you can use this table to find the most current versions of assets without the need for SELECT MAX(assetData.revisionDate) subqueries!

First, take a look at what is stored in the assetIndex table (as of WebGUI 7.5):

• assetId
• title
• synopsis
• url
• creationDate
• revisionDate
• ownerUserId
• groupIdView
• groupIdEdit
• className
• isPublic
• keywords
• lineage

There is enough information here to build, for example, an SQL Report that pulls out recent forum posts with as little as one query to this table alone.

For example, if you know the lineage of the forum from which you want to find the {X} most recent posts, and you simply want to provide the title, synopsis and a link to the full post, your query would look something like:

SELECT
title, synopsis, url
FROM assetIndex
WHERE lineage LIKE "000001000006000004%"
AND className LIKE "WebGUI::Asset::Post::Thread"
ORDER BY revisionDate DESC
LIMIT {X}

The "%" wild card allows the query to match all assets in the table with lineages that start out with these digits, which includes all forum posts, but also non-post assets such as the RSS feed. "AND className LIKE 'WebGUI::Asset::Post::Thread'" limits the results to threads.

Note that if you wanted the most recent posts, ignoring recent edits to older posts, you can ORDER BY creationDate instead of revisionDate.

Now, if your site is frequently changing and lineage may not be stable (or if you simply don't want to bother to look up the lineage for your forum) you can use a short subquery to the same assetIndex table to pull out the lineage for the forum based on its assetId field:

SELECT
title, synopsis, url
FROM assetIndex
WHERE lineage LIKE
  (SELECT CONCAT(lineage, "%")
   FROM assetIndex
   WHERE assetId = "8oi66QUuxMNHFKN1dCmUfA")
AND className LIKE "WebGUI::Asset::Post::Thread"
ORDER BY creationDate DESC
LIMIT {X}

Don't forget to add the "%" wildcard to the end of the lineage! The MySQL CONCAT() function is used in this example.

If the assetIndex table does not have the information you would like to present, then you will need to join the assetIndex table to match up records. Make the join using both assetIndex and revisionDate fields from the assetIndex table, and you will be able to retrieve the most recent approved version of the asset data, still without need for a SELECT MAX(assetData.revisionDate) subquery.

For example, to include the thread rating to the above query:

SELECT
a.title, a.synopsis, a.url, t.threadRating
FROM assetIndex AS a, Thread AS t
WHERE lineage LIKE
  (SELECT CONCAT(lineage, "%")
   FROM assetIndex
   WHERE assetId = "8oi66QUuxMNHFKN1dCmUfA")
AND className LIKE "WebGUI::Asset::Post::Thread"
AND a.assetId = t.assetId
AND a.revisionDate = t.revisionDate
ORDER BY creationDate DESC
LIMIT {X}

Finally, one more example to show that you don't need to limit yourself to the {X} most recent posts. Say, for example, you wanted to show all posts made today and yesterday, and you specifically define "yesterday" as beginning at midnight (rather than just searching for all posts made in the last 48 hours), and you're in the Central Standard time zone (CST):

SELECT
title, synopsis, url
FROM assetIndex
WHERE lineage LIKE
  (SELECT CONCAT(lineage, "%")
   FROM assetIndex
   WHERE assetId = "WATmHOT65F6btQj-NqAUyQ")
AND className LIKE "WebGUI::Asset::Post::Thread"
AND creationDate >
    ((SELECT UNIX_TIMESTAMP('^D(%y-%m-%d);'))+6*3600-1*86400)
ORDER BY creationDate DESC

WebGUI doesn't have a built-in macro for doing Unix time calculations, and instead of writing one of your own, you can make use of MySQL's UNIX_TIMESTAMP() function, with WebGUI's ^Date(); macro, to do all your calculations.

• ^D(%y-%m-%d); uses WebGUI's Date macro to present the current day's date (at midnight) in the format UNIX_TIMESTAMP() expects.
• Because we're in the Central timezone, midnight here is 6 hours later than midnight, GMT, so we add 6 times 3600 (the number of seconds in an hour) to the Unix timestamp for midnight, GMT.
• Since we are interested in midnight yesterday, we need to subtract the number of seconds in one day, which is 86,4000. The "1" multiplier is not necessary, but makes it easy for you to change the query to go back additional days. If you just want posts made since midnight, today, leave this part out.