Difference between revisions of "Opasnet Base UI"
m (→Newest act (upload)) |
(Base UI queries update) |
||
Line 54: | Line 54: | ||
</code> | </code> | ||
− | === Result ids that contain excluded location === | + | === Result ids that contain excluded location (for results filtering) === |
<code> | <code> | ||
SELECT DISTINCT res.id as result_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids separated by comma>) ORDER BY res.id | SELECT DISTINCT res.id as result_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids separated by comma>) ORDER BY res.id | ||
Line 67: | Line 67: | ||
<code> | <code> | ||
SELECT * FROM results WHERE result_id NOT IN(<list of result ids to be excluded, separated by comma>) AND obs <= <sample count> | SELECT * FROM results WHERE result_id NOT IN(<list of result ids to be excluded, separated by comma>) AND obs <= <sample count> | ||
+ | </code> | ||
+ | |||
+ | === Whole Mean and SD row count === | ||
+ | <code> | ||
+ | SELECT COUNT(DISTINCT cell_id) AS cnt, ind.id as index_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id WHERE obj.id = <object id> AND actobj.series_id = <series id> | ||
+ | </code> | ||
+ | |||
+ | === Excluded Mean and SD count === | ||
+ | <code> | ||
+ | SELECT COUNT(DISTINCT cell.id) AS cnt FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids, separated by comma>) | ||
+ | </code> | ||
+ | |||
+ | === Cell ids that contain excluded location (for Mean and SD filtering) === | ||
+ | <code> | ||
+ | SELECT DISTINCT cell.id AS cell_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids, separated by comma>) ORDER BY cell.id | ||
+ | </code> | ||
+ | |||
+ | === Results view for Mean and SD === | ||
+ | <code> | ||
+ | results AS SELECT ind.id AS index_id, actobj.obj_id as obj_id, cell_id, actobj.series_id AS upload, loc.location AS location, obj.unit, mean, sd FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id WHERE obj.id = <object id> AND actobj.series_id = <series id> | ||
+ | </code> | ||
+ | |||
+ | === Final results for Mean and SD === | ||
+ | <code> | ||
+ | SELECT * FROM results WHERE cell_id NOT IN(<list of cell ids to be excluded, separated by comma>) | ||
</code> | </code> | ||
[[category:extension]] | [[category:extension]] |
Revision as of 13:01, 26 March 2010
This special page implements user interface to fetch data from the Opasnet Base -database.
Contents
- 1 Queries
- 1.1 Basic object data
- 1.2 Latest series id (for latest upload of data)
- 1.3 Object cell data
- 1.4 Wiki data
- 1.5 Upload history
- 1.6 Specific upload info
- 1.7 Specific upload dimension count
- 1.8 Dimensions and locations
- 1.9 Whole result row count
- 1.10 Excluded results row count
- 1.11 Result ids that contain excluded location (for results filtering)
- 1.12 Results view
- 1.13 Final results from results view
- 1.14 Whole Mean and SD row count
- 1.15 Excluded Mean and SD count
- 1.16 Cell ids that contain excluded location (for Mean and SD filtering)
- 1.17 Results view for Mean and SD
- 1.18 Final results for Mean and SD
Queries
This chapter describes the queries that Base UI executes.
Basic object data
SELECT * FROM obj WHERE ident = "<object identifier>";
Latest series id (for latest upload of data)
SELECT series_id FROM actobj WHERE obj_id = <object id> ORDER BY series_id DESC LIMIT 0,1;
Object cell data
SELECT n, mean FROM cell LEFT JOIN actobj ON cell.actobj_id = actobj.id WHERE actobj.series_id = <act series id>;
Wiki data
SELECT * FROM wiki WHERE id = <wiki id>;
Upload history
SELECT act.id AS id, who, time, comments, n, acttype FROM act LEFT JOIN actobj ON act.id = actobj.act_id LEFT JOIN acttype ON acttype.id = act.acttype_id LEFT JOIN cell ON cell.actobj_id = actobj.id WHERE actobj.obj_id = <object id> AND (acttype.id = 4 OR acttype.id = 5) ORDER BY time DESC;
Specific upload info
SELECT act.id AS id, who, time, comments, n, acttype FROM act LEFT JOIN actobj ON act.id = actobj.act_id LEFT JOIN acttype ON acttype.id = act.acttype_id LEFT JOIN cell ON cell.actobj_id = actobj.id WHERE actobj.obj_id = <object id> AND (acttype.id = 4 OR acttype.id = 5) AND actobj.series_id = <series id> ORDER BY time DESC;
Specific upload dimension count
SELECT COUNT(DISTINCT ind.id) AS cnt FROM obj LEFT JOIN actobj ON obj.id = actobj.obj_id LEFT JOIN cell ON actobj.id = cell.actobj_id LEFT JOIN loccell ON cell.id = loccell.cell_id LEFT JOIN loc ON loccell.loc_id = loc.id RIGHT JOIN obj AS ind ON loc.obj_id_i=ind.id WHERE actobj.series_id = <series id> AND obj.id = <object id>
Dimensions and locations
SELECT DISTINCT ind.wiki_id AS wiki_id, obj.page AS wiki_page, loc.id AS location_id, location, ind.id AS index_id, ind.name AS index_name FROM obj LEFT JOIN actobj ON obj.id = actobj.obj_id LEFT JOIN cell ON actobj.id = cell.actobj_id LEFT JOIN loccell ON cell.id = loccell.cell_id LEFT JOIN loc ON loccell.loc_id = loc.id RIGHT JOIN obj AS ind ON loc.obj_id_i=ind.id WHERE actobj.series_id = <series id> AND obj.id = <object id> ORDER BY index_name, location
Whole result row count
SELECT COUNT(DISTINCT res.id) AS cnt FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id>
Excluded results row count
SELECT COUNT(DISTINCT res.id) as cnt FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<location ids, separated by comma>)
Result ids that contain excluded location (for results filtering)
SELECT DISTINCT res.id as result_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids separated by comma>) ORDER BY res.id
Results view
results AS SELECT res.id AS result_id, location, ind.id as index_id, obs, result FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> ORDER BY res.id, ind.name
Final results from results view
SELECT * FROM results WHERE result_id NOT IN(<list of result ids to be excluded, separated by comma>) AND obs <= <sample count>
Whole Mean and SD row count
SELECT COUNT(DISTINCT cell_id) AS cnt, ind.id as index_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id WHERE obj.id = <object id> AND actobj.series_id = <series id>
Excluded Mean and SD count
SELECT COUNT(DISTINCT cell.id) AS cnt FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids, separated by comma>)
Cell ids that contain excluded location (for Mean and SD filtering)
SELECT DISTINCT cell.id AS cell_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids, separated by comma>) ORDER BY cell.id
Results view for Mean and SD
results AS SELECT ind.id AS index_id, actobj.obj_id as obj_id, cell_id, actobj.series_id AS upload, loc.location AS location, obj.unit, mean, sd FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id WHERE obj.id = <object id> AND actobj.series_id = <series id>
Final results for Mean and SD
SELECT * FROM results WHERE cell_id NOT IN(<list of cell ids to be excluded, separated by comma>)