Collaboration Systems

Select the latest posts from a collaboration system or a group of collaboration systems based on location (a.k.a. - lineage). userDefined1 is implemented as synopsis.

SELECT assetData.title, assetData.url, Post.userDefined1 AS synopsis, asset.lineage 
FROM   Post, asset, assetData 
WHERE   asset.assetId=Post.assetId 
AND asset.assetId=assetData.assetId
AND asset.className = 'WebGUI::Asset::Post::Thread'
AND asset.state='published'
AND asset.lineage LIKE '000001000002000026%'  
AND Post.revisionDate = (
    SELECT MAX(maxPostData.revisionDate)       
    FROM Post as maxPostData       
    WHERE Post.assetId=maxPostData.assetId
)
GROUP BY asset.assetId

Lineage

Select a list of titles and urls of page layouts located under a specific page layout (not including the parent page layout).

select MAX(ad.revisionDate), ad.title, ad.url 
FROM asset a, assetData ad 
WHERE a.assetId=ad.assetId 
AND a.className='WebGUI::Asset::Wobject::Layout' 
AND a.lineage LIKE '000001000002000011000005%' 
AND a.assetId != '78lpfLjWugkaNLw9c48abA' 
AND a.state = 'published'
GROUP BY a.assetId

Assets

Get the list of templates. Add `title` to the WHERE clause to filter by name of the template.

select asset.assetId,title,url from asset,assetData where asset.assetId=assetData.assetId and className='WebGUI::Asset::Template' AND assetData.revisionDate = (SELECT MAX(aD.revisionDate)  FROM assetData as aD WHERE assetData.assetId=aD.assetId) GROUP BY asset.assetId;

Replace carriage returns with commas

This code is useful for form controls which tend to save multi-select fields (e.g., checklists, multi-select select fields) into MySQL separated by carriage returns.

SELECT REPLACE(fieldname, '\n', ', ') FROM tablename;

Dates

Most date fields in WebGUI store the epoch time which makes ordering simple. Formatting the date can be done directly in MySQL using from_unixtime and other date functions. Here are some examples:

Order by date

  SELECT * FROM assetData ORDER BY assetData.revisionDate desc;

Format Dates

  SELECT from_unixtime(assetData.revisionDate) as last_updated FROM assetData;