From: Mischa POSLAWSKY Date: Tue, 31 May 2022 00:43:20 +0000 (+0200) Subject: word: category references (recursive selection) X-Git-Tag: v1.14~74 X-Git-Url: http://git.shiar.nl/sheet.git/commitdiff_plain/c7530c743eb2d8f45167ae0a9ed641559a4d550e?hp=b5b60a0ee0f90c2895e1e4e27926840ea29c2be7 word: category references (recursive selection) Ordered links instead of javascript calculation from full inventory. --- diff --git a/tools/mkwordlist b/tools/mkwordlist index 78e36b6a..6555246 100755 --- a/tools/mkwordlist +++ b/tools/mkwordlist @@ -13,8 +13,9 @@ use Data::Dump 'pp'; my %rows; if (my $lang = shift @ARGV) { my %filter = (lang => $lang); - my $cols = "ref, array_to_string(form || alt, '/'), prio + coalesce(grade, 90) / 100::float, id, cat"; - %rows = $db->select(_word_ref => $cols, \%filter)->map_arrays; + my $cols = "ref, array_to_string(form || alt, '/'), prio, id, sub"; + %rows = $db->select(_word => $cols, \%filter)->map_arrays; + defined $_->[-1] or pop @$_ for values %rows; say pp \%rows =~ s/\\x\{([0-9A-F]+)\}/chr hex $1/ger; exit; diff --git a/tools/word.pg.sql b/tools/word.pg.sql index 821814d..69ae03c 100644 --- a/tools/word.pg.sql +++ b/tools/word.pg.sql @@ -78,6 +78,29 @@ CREATE OR REPLACE VIEW _word_ref AS FROM word r LEFT JOIN word w ON w.id = r.ref; +CREATE OR REPLACE VIEW _word_tree AS + WITH RECURSIVE r AS ( + SELECT w.ref, w.lang, w.cat, w.grade, NULL::int trans + FROM _word_ref w + UNION ALL + SELECT r.ref, r.lang, w.cat, + CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN r.grade ELSE w.grade END, + CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN w.id END + FROM r JOIN word w ON w.id = r.cat + LEFT JOIN word t ON w.id = t.ref AND t.lang = r.lang + WHERE r.trans IS NULL + ) + SELECT ref, lang, trans cat, grade FROM r WHERE trans IS NOT NULL + ORDER BY cat, grade, ref; + +CREATE OR REPLACE VIEW _word AS + SELECT + (SELECT array_agg(coalesce(ref, id)) FROM _word_tree + WHERE cat = w.ref AND lang = w.lang) sub, + w.* + FROM _word_ref w + ; + CREATE OR REPLACE VIEW _cat_words AS SELECT exportform(word.*) form, sub.*, word.lang, word.ref FROM word RIGHT JOIN ( diff --git a/word/finder.js b/word/finder.js index a45dee4..ea1781d 100644 --- a/word/finder.js +++ b/word/finder.js @@ -2,7 +2,7 @@ class WordFinder extends WordQuiz { add(parentitem, rows) { const catitem = put(parentitem, 'ul'); rows.forEach(ref => { - const [title, level, imgid] = this.data[ref]; + const [title, level, imgid, subs] = this.data[ref]; const worditem = put(catitem, 'li'); const figitem = put(worditem, 'figure'); if (imgid) { @@ -14,18 +14,13 @@ class WordFinder extends WordQuiz { innerHTML: html, }); } - let levelpart = level <= 1 && this.cats[ref] && this.cats[ref].length > 1; - if (levelpart) { + if (level <= 1 && subs.length >= 2) { put(worditem, '.large'); } - if (this.cats[ref]) { + if (subs.length) { // delve into subcategory put(worditem, '.parent'); - this.add(worditem, this.cats[ref].sort((a, b) => { - const [worda, wordb] = [this.data[a], this.data[b]]; - return (worda[1] % 1) - (wordb[1] % 1) - || worda[0].localeCompare(wordb[0]); - })); + this.add(worditem, subs); } }); } @@ -33,7 +28,7 @@ class WordFinder extends WordQuiz { setup() { this.gallery = document.getElementById('gallery'); put(this.gallery, '-p', 'Under construction.'); - this.add(this.gallery, this.preset.cat ? [this.preset.cat] : this.cats[null]); + this.add(this.gallery, this.preset.cat ? [this.preset.cat] : this.data[''][3]); } stop() {} diff --git a/word/quiz.js b/word/quiz.js index d32368b..1aaab61 100644 --- a/word/quiz.js +++ b/word/quiz.js @@ -6,56 +6,62 @@ Array.prototype.shuffle = function () { return this; }; +Set.prototype.filter = function (f) { + return new Set([...this].filter(f)); +}; + class WordQuiz { dataselect(json) { - this.data = json; - this.cats = {}; // category lookup - for (let i in json) { - let cat = json[i][3]; - if (this.cats[cat]) { - this.cats[cat].push(i); - } - else { - this.cats[cat] = [i]; - } - } - return this.datafilter(json); + this.data = this.datafilter(json); + let rows = Object.values(this.data); + rows = rows.filter(row => !row[3].length); // remove referenced categories + return rows.shuffle(); } datafilter(json) { // find viable rows from json data - let rows = Object.values(json); + let ids = new Set(Object.keys(json)); + const selection = {...json}; // clone if (this.preset.cat !== undefined) { - rows = []; - let children = this.cats[this.preset.cat]; + ids.clear(); + let children = [this.preset.cat]; for (let loop = 0; children.length && loop < 20; loop++) { - rows.push(...children); - children = children.map(cat => this.cats[cat]).filter(is => is).flat(); + for (let child of children) ids.add(child.toString()); + children = children.map(cat => json[cat][3]).filter(is => is).flat() } - rows = rows.map(row => json[row]).filter(row => row[2]); + } + if (this.preset.image) { + ids = ids.filter(id => json[id][2]); } if (this.preset.level !== undefined) { - rows = rows.filter(row => row[1] <= this.preset.level); + ids = ids.filter(id => json[id][1] <= this.preset.level); } - { - let cats = new Set(); - let subcats = rows.map(row => row[3]); // direct parents - for (let loop = 0; subcats.length && loop < 20; loop++) { - subcats.forEach(cat => cats.add(cat)); - subcats = subcats.map(row => json[row] && json[row][3]).filter(val => val); // recurse grandparents + // keep only wanted ids + for (let id in selection) { + if (id && !ids.has(id)) { + delete selection[id]; } - rows = rows.filter(row => !cats.has(row[2])); // remove referenced categories } - return rows.shuffle(); + + // retain orphaned references in grandparent categories + for (let id in selection) { + selection[id][3] = function subresolve(subs) { + //console.log(subs); + return (subs || []).flatMap(sub => + sub in selection ? [sub] : subresolve(json[sub][3]) + ); + }(selection[id][3]); + } + return selection; } load(dataurl) { this.preset = {}; let input; if (input = window.location.hash.match(/\d+/)) { - this.preset.cat = input[0]; + this.preset.cat = parseInt(input[0]); } if (window.location.hash.match(/a/)) { this.preset.level = 3;