summaryrefslogtreecommitdiff
path: root/lib/VN3/Char/JS.pm
blob: eafda3ade18f6c682849a39f3eeb5e7dde1c2236 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package VN3::Char::JS;

use VN3::Prelude;


my $elm_CharResult = elm_api CharResult => { aoh => {
    id            => { id => 1 },
    name          => {},
    original      => {},
    main          => { type => 'hash', required => 0, keys => {
        id            => { id => 1 },
        name          => {},
        original      => {},
    }},
}};

json_api '/js/char.json', {
    search => { maxlength => 500 }
}, sub {
    my $q = shift->{search};

    # XXX: This query is kinda slow
    my $qs = $q =~ s/[%_]//gr;
    my $r = tuwf->dbAlli(
        'SELECT c.id, c.name, c.original, c.main, c2.name AS main_name, c2.original AS main_original',
        'FROM (',
            # ID search
            $q =~ /^$CID_RE$/ ? ('SELECT 1, id FROM chars WHERE id =', \"$1", 'UNION ALL') : (),
            # exact match
            'SELECT 2, id FROM chars WHERE lower(name) = lower(', \$q, ") OR lower(translate(original,' ', '')) = lower(", \($q =~ s/\s//gr), ')',
            'UNION ALL',
            # prefix match
            'SELECT 3, id FROM chars WHERE name ILIKE', \"$qs%", ' OR original ILIKE', \"$qs%",
            'UNION ALL',
            # substring match
            'SELECT 4, id FROM chars WHERE name ILIKE', \"%$qs%", ' OR original ILIKE', \"%$qs%",
        ') AS ct (ord, id)',
        'JOIN chars c ON c.id = ct.id',
        'LEFT JOIN chars c2 ON c2.id = c.main',
        'WHERE NOT c.hidden',
        'GROUP BY c.id, c.name, c.original, c.main, c2.name, c2.original',
        'ORDER BY MIN(ct.ord), c.name',
        'LIMIT 20'
    );

    for (@$r) {
        $_->{main} = $_->{main} ? { id => $_->{main}, name => $_->{main_name}, original => $_->{main_original} } : undef;
        delete $_->{main_name};
        delete $_->{main_original};
    }

    $elm_CharResult->($r);
};

1;