summaryrefslogtreecommitdiff
path: root/lib/VN3/Producer/JS.pm
blob: 50161ce5d3c0d23401c39815295c028e69382e4e (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
package VN3::Producer::JS;

use VN3::Prelude;


my $elm_ProducerResult = elm_api ProducerResult => { aoh => {
    id       => { id => 1 },
    name     => {},
    original => {},
    hidden   => { anybool => 1 },
}};


json_api '/js/producer.json', {
    search => { type => 'array', scalar => 1, minlength => 1, values => { maxlength => 500 } },
    hidden => { anybool => 1 }
}, sub {
    my $data = shift;

    my $r = tuwf->dbAlli(
        'SELECT p.id, p.name, p.original, p.hidden',
        'FROM (', (sql_join 'UNION ALL', map {
            my $q = $_;
            my $qs = s/[%_]//gr;
            +(
                # ID search
                /^$PID_RE$/ ? (sql 'SELECT 1, id FROM producers WHERE id =', \"$1") : (),
                # exact match
                sql('SELECT 2, id FROM producers WHERE lower(name) = lower(', \$q, ") OR lower(translate(original,' ', '')) = lower(", \($q =~ s/\s//gr), ')'),
                # prefix match
                sql('SELECT 3, id FROM producers WHERE name ILIKE', \"$qs%", ' OR original ILIKE', \"$qs%"),
                # substring match
                sql('SELECT 4, id FROM producers WHERE name ILIKE', \"%$qs%", ' OR original ILIKE', \"%$qs%", ' OR alias ILIKE', \"%$qs%")
            )
         } @{$data->{search}}),
        ') AS pt (ord, id)',
        'JOIN producers p ON p.id = pt.id',
        $data->{hidden} ? () : ('WHERE NOT p.hidden'),
        'GROUP BY p.id',
        'ORDER BY MIN(pt.ord), p.name',
        'LIMIT 20'
    );

    $elm_ProducerResult->($r);
};

1;