Commit | Line | Data |
c42752fc |
1 | use strict; |
2 | use warnings; |
3 | use Test::More; |
4 | use SQL::Abstract::Test import => [ qw(is_same_sql_bind) ]; |
5 | use SQL::Abstract::ExtraClauses; |
6 | |
7 | my $sqlac = SQL::Abstract::ExtraClauses->new; |
8 | |
9 | my ($sql, @bind) = $sqlac->select({ |
10 | select => [ qw(artist.id artist.name), { -func => [ json_agg => 'cd' ] } ], |
11 | from => [ |
b99e9a14 |
12 | { artists => { -as => 'artist' } }, |
13 | -join => [ cds => as => 'cd' => on => { 'cd.artist_id' => 'artist.id' } ], |
c42752fc |
14 | ], |
15 | where => { 'artist.genres', => { '@>', { -value => [ 'Rock' ] } } }, |
16 | order_by => 'artist.name', |
17 | group_by => 'artist.id', |
18 | having => { '>' => [ { -func => [ count => 'cd.id' ] }, 3 ] } |
19 | }); |
20 | |
21 | is_same_sql_bind( |
22 | $sql, \@bind, |
23 | q{ |
24 | SELECT artist.id, artist.name, JSON_AGG(cd) |
b99e9a14 |
25 | FROM artists AS artist JOIN cds AS cd ON cd.artist_id = artist.id |
c42752fc |
26 | WHERE artist.genres @> ? |
27 | ORDER BY artist.name |
28 | GROUP BY artist.id |
29 | HAVING COUNT(cd.id) > ? |
30 | }, |
31 | [ [ 'Rock' ], 3 ] |
32 | ); |
33 | |
34 | done_testing; |