EXISTS/NOT EXISTS as sub query

[jonathanstowe]

I've worked around this in code but I needed to add a "NOT EXISTS () to something like:

SELECT
   count('*') > 0 as "data_1"
FROM
   "upload"
WHERE
   ("upload".uploaded)::TIMESTAMP > ( SELECT
      max("stats_view_refresh".refresh_time) as "data_1"
   FROM
      "stats_view_refresh" )
LIMIT 1

which is basically what the #514 was for, and is implemented as:

my $last = self.^rs.max(*.refresh-time );
 Upload.^all.grep( *.uploaded > $last ).so;

So it becomes:

SELECT
   count('*') > 0 as "data_1"
FROM
   "upload"
WHERE
   NOT EXISTS ( SELECT id FROM stats_view_refresh) OR
   ("upload".uploaded)::TIMESTAMP > ( SELECT
      max("stats_view_refresh".refresh_time) as "data_1"
   FROM
      "stats_view_refresh" )
LIMIT 1

Which I'd see as being implemented so you'd do something like:

my $last = self.^rs.max(*.refresh-time );
my $exists = self.^rs.map(*.id).exists.not;
 Upload.^all.grep( $exists or *.uploaded > $last ).so;

I thought it could be done like:

my $last = $svr.^rs.max(*.refresh-time );
my $exists = Red::AST::Function.new( args => [$svr.^rs.map(*.id).ast], func => 'NOT EXISTS');

say $upload.^all.grep( -> $v { $exists OR $v.uploaded > $last }).so;

Which is almost there but does something whacky with the generated SQL:

SELECT
   count('*') > 0 as "data_1"
FROM
   "upload"
WHERE
   NOT EXISTS(SELECT
      "stats_view_refresh".id as "data_1"
   FROM
      "stats_view_refresh") OR "upload".uploaded IS NOT NULL > ( SELECT
      max("stats_view_refresh".refresh_time) as "data_1"
   FROM
      "stats_view_refresh" )
LIMIT 1

Not urgent, I only found this when testing with an empty DB and have dealt with it in some slightly less elegant code.

If you don't get around to it I'll try and take a look next week.

[FCO]

Sorry for not seeing that before. I'll try to take a look at it tomorrow. Thanks for opening the issue.

[FCO]

I've been playing with that and getting No such method 'specialise' for invocant of type ... many times... Still working on that... :(