2

If I have this schema:

module default {

  type Publisher {
    required property name -> str;
  }

  type Book {
    required property name -> str;
    link publisher -> Publisher;
  }

}

With this data inserted:

insert Publisher { name := 'Dundurn Press' };
insert Book { name := 'Lost Shadow', publisher := assert_single((select Publisher filter .name = 'Dundurn Press')) };
insert Book { name := 'My Unpublished Book' };

Then running the following queries:

select Book { name } filter not exists .publisher;
select Book { name } filter .publisher.name = 'Dundurn Press';
select Book { name } filter .publisher.name = 'Dundurn Press' or not exists .publisher;
  • The first query returns 'My Unpublished Book' as expected
  • The second query returns 'Lost Shadow' as expected
  • However, I intended for the third query to return both books (all books with no publisher, combined with books by one specific publisher)

How should I write the third query to return what is expected?

In SQL, I would do something like this, which would return books with no publisher and books published by 'Dundurn Press':

select b.name
from books b
left join publishers p on p.id = b.publisher_id
where p.name = 'Dundurn Press' or p.id is null;
Richard Gieg
  • 1,276
  • 8
  • 17

1 Answers1

3

I found that it's possible to use a with block to select books with no publisher as unpublished_books and select books published by 'Dundurn Press' as dundurn_press_books and then select name from the union of the two sets:

with
unpublished_books := (select Book filter not exists .publisher),
dundurn_press_books := (select Book filter .publisher.name = 'Dundurn Press'),
select (unpublished_books union dundurn_press_books) { name };
Richard Gieg
  • 1,276
  • 8
  • 17
  • I was looking for a way to do exactly this and wasn't thrilled with the prospect of basically merging the resulting arrays of two queries. To me this feels like a bug, or at least not a great limitation which causes you to rethink your query's structure entirely. Luckily the answer you came upon still provides the ability to sort and aggregate within the query and also allows you to change the fields returned without having to maintain multiple separate queries in code. Nicely done! – Crashspeeder Feb 18 '23 at 17:05