26 lines
1.3 KiB
JavaScript
Executable File
26 lines
1.3 KiB
JavaScript
Executable File
|
|
const CREATE_TABLE_IMAGES = "CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY, src TEXT, dataUri TEXT)";
|
|
const CREATE_TABLE_QUESTIONS = "CREATE TABLE IF NOT EXISTS questions (id INTEGER PRIMARY KEY, updated DATETIME DEFAULT CURRENT_TIMESTAMP, text TEXT)";
|
|
const CREATE_TABLE_ANSWERS = "CREATE TABLE IF NOT EXISTS answers (id INTEGER PRIMARY KEY, aid INTEGER, qid INTEGER, text TEXT, correct INTEGER, checked INTEGER)";
|
|
const CREATE_TABLE_ANSWERING = "CREATE TABLE IF NOT EXISTS answering (id INTEGER PRIMARY KEY, created DATETIME DEFAULT CURRENT_TIMESTAMP, version TEXT, href TEXT)";
|
|
|
|
const SELECT_ALL = "SELECT q.id AS qid, q.text AS question, i.dataUri AS image, T2.id AS aid, T2.text AS answer, T2.correct FROM questions q \
|
|
LEFT JOIN (SELECT qid, MAX(aid) AS maxid FROM answers GROUP BY qid) AS T1 ON q.id = T1.qid \
|
|
LEFT JOIN answers AS T2 ON T2.aid = T1.maxid AND T2.qid = q.id \
|
|
LEFT JOIN images AS i ON i.id = q.id ORDER BY T2.id DESC";
|
|
|
|
|
|
|
|
module.exports = {
|
|
SELECT_ALL: SELECT_ALL
|
|
}
|
|
|
|
|
|
app.get('/top-mistakes', function (req, res) {
|
|
db.all("SELECT q.id AS qid, q.text as question, a.acount AS mistakes FROM questions q " +
|
|
"JOIN (SELECT qid, count(*) as acount FROM answers WHERE correct != checked GROUP BY aid,qid) AS a ON a.qid = q.id ORDER BY mistakes DESC",
|
|
function (err, rows) {
|
|
res.render('index', { questions: rows });
|
|
});
|
|
});
|