Below is a query, provided by Stuart, which retrieves everything needed to render that page.
The following query retrieves all the information I think is needed to render the entire report. It runs in about a second for bzr, and gives the running hit count rather than the cached count (there is no performance improvement using LibraryFileAlias.hits over SUM(count) ). It should be possible to convert this to Storm syntax if you want. The result will need to be transformed into a data structure usable by the existing template, or the existing template rewritten.
SELECT
Product.name,
ProductSeries.name,
SignatureAlias.id AS signature_id,
Milestone.name,
ProductReleaseFile.description,
LibraryFileAlias.id,
LibraryFileAlias.filename,
LibraryFileContent.filesize,
SUM(count),
MAX(day) AS last_downloaded
FROM
Product
JOIN ProductSeries ON ProductSeries.product = Product.id
JOIN Milestone ON Milestone.productseries = ProductSeries.id
JOIN ProductRelease ON ProductRelease.milestone = Milestone.id
JOIN ProductReleaseFile
ON ProductReleaseFile.productrelease = ProductRelease.id
JOIN LibraryFileAlias
ON LibraryFileAlias.id = ProductReleaseFile.libraryfile
JOIN LibraryFileContent
ON LibraryFileContent.id = LibraryFileAlias.content
LEFT OUTER JOIN LibraryFileDownloadCount
ON LibraryFileDownloadCount.libraryfilealias = LibraryFileAlias.id
LEFT OUTER JOIN LibraryFileAlias AS SignatureAlias
ON SignatureAlias.id = ProductReleaseFile.signature
WHERE
Product.name = 'bzr'
GROUP BY
Product.name,
ProductSeries.name,
signature_id,
Milestone.name,
ProductReleaseFile.description,
LibraryFileAlias.id,
LibraryFileAlias.filename,
LibraryFileContent.filesize
ORDER BY
ProductSeries.name,
Milestone.name,
LibraryFileAlias.filename;
Below is a query, provided by Stuart, which retrieves everything needed to render that page.
The following query retrieves all the information I think is needed to render the entire report. It runs in about a second for bzr, and gives the running hit count rather than the cached count (there is no performance improvement using LibraryFileAlia s.hits over SUM(count) ). It should be possible to convert this to Storm syntax if you want. The result will need to be transformed into a data structure usable by the existing template, or the existing template rewritten.
SELECT es.name, ias.id AS signature_id, aseFile. description, Alias.id, Alias.filename, Content. filesize, product = Product.id productseries = ProductSeries.id milestone = Milestone.id ile.productrele ase = ProductRelease.id ile.libraryfile ent.id = LibraryFileAlia s.content loadCount loadCount. libraryfilealia s = LibraryFileAlias.id ile.signature
Product.name,
ProductSeri
SignatureAl
Milestone.name,
ProductRele
LibraryFile
LibraryFile
LibraryFile
SUM(count),
MAX(day) AS last_downloaded
FROM
Product
JOIN ProductSeries ON ProductSeries.
JOIN Milestone ON Milestone.
JOIN ProductRelease ON ProductRelease.
JOIN ProductReleaseFile
ON ProductReleaseF
JOIN LibraryFileAlias
ON LibraryFileAlias.id = ProductReleaseF
JOIN LibraryFileContent
ON LibraryFileCont
LEFT OUTER JOIN LibraryFileDown
ON LibraryFileDown
LEFT OUTER JOIN LibraryFileAlias AS SignatureAlias
ON SignatureAlias.id = ProductReleaseF
WHERE
Product.name = 'bzr'
GROUP BY es.name, aseFile. description, Alias.id, Alias.filename, Content. filesize
Product.name,
ProductSeri
signature_id,
Milestone.name,
ProductRele
LibraryFile
LibraryFile
LibraryFile
ORDER BY es.name, Alias.filename;
ProductSeri
Milestone.name,
LibraryFile