From MozillaWiki
Jump to: navigation, search

Useful SQL Queries

Add-on Count

Retrieves the number of add-ons that support Firefox, are public, sandboxed, or nominated, and are active.

 select count(*) from addons inner join versions on id from versions where order by id desc limit 1) inner join applications_versions on where addons.inactive=0 and addons.status in (1,3,4) and applications_versions.application_id=1;

Listing of all 3.0 Add-ons without 3.1 compatibility

Also used for a mail merge spreadsheet, hence the inclusion of developer contact info.

 select a.addon_id, translations.localized_string, sum(count), compat.topver, users.firstname, users.lastname, from update_counts a join (select, a.supportemail,, max(e.version) as topver from addons a, versions c, applications_versions d, appversions e where = c.addon_id and and d.max = and a.addontype_id =1 and d.application_id = 1 and e.version >= 3.0 group by as compat on ( = a.addon_id) join translations on ( = join addons_users on (addons_users.addon_id = a.addon_id) join users on (addons_users.user_id = where date = '2009-01-14' and compat.topver < 3.1 and translations.locale = 'en-us' group by 1 order by 3 desc

Listing of all Featured/Category Recommended addons

Used to output a listing of all Featured and Category Recommended addons, with an outer join to show If = null then the addon is only category recommended and not featured.

 select, b.localized_string, b2.localized_string, averagerating,totalreviews, max(e.version), from (addons a, translations b, translations b2, versions c, applications_versions d, appversions e, addons_tags f, tags g, (select id from addons where id in (select addon_id from features) or id in (select addon_id from addons_tags where feature=1)) as h, (select tag_id from addons_tags where feature=1) as i) left join features on ( = features.addon_id) where = and and f.tag_id = and = and b2.locale = 'en-us' and = i.tag_id and and = c.addon_id and and = and b.locale = 'en-US' and a.addontype_id =1 and d.application_id = 1 group by 1 order by sum(weeklydownloads) desc