Using custom sql statements in smidata.
Release 1.3 - ...
The SmiData macro allows you to use custom sql statements to query the Smi database. This gives you a large amount of freedom in querying the database. This example shows you how to retrieve the top 10 pages of the content type FAQ with the most pageviews.
| Smartsite SXML |
Copy Code
|
|---|---|
<se:smidata
sitename="mysite"
sql="select top 10 c.Nr, c.Title, sum(ppd.Total) as Pageviews from contents c
inner join Page p on p.SmartsiteId=c.Nr
inner join PageviewsPerDay ppd on p.Nr=ppd.PageId and ppd.LogDate >= '2010/10/01' and ppd.LogDate {char.lt()}= '2010/10/31'
where c.ContentType='FAQ'
group by c.Nr, c.Title
order by Pageviews desc"
save="data"
resulttype="datatable"
/>
{datatable.simpleformat($data)}
|
|
Note that the Contents table in the Smi database has a column ContentType which contains the code of the content type as stored in the Smartsite database.