Finding books without claims in org-roam using org-babel and sqlite

OK, first off: books and claims.

select title, id from nodes where id in (select node_id from tags where tag = '"book"' intersect select node_id from tags where tag = '"read"');

select title, id from nodes where id in (select source from links where dest = '"ba86731e-84ea-4f0a-9dc7-dee4743bd8b2"');

OK, great. How do I use those in a next query though?

##+name: withoutclaims ##+header: :db org-roam.db ##+begin_src sqlite :var claims=claims :results none #create temporary table claims (id, title); #.mode csv claims #.import $claims claims #select id, title from nodes where id not in (select source from links where dest in (select id from claims)) ##+end_src

##+name: bookswithout ##+header: :db org-roam.db ##+begin_src sqlite :var withoutclaims=withoutclaims books=books :results none #create temporary table without (id, title); #.mode csv withoutclaims #.import $withoutclaims withoutclaims #create temporary table books (id, title); #.mode csv books #.import $books books #select * from books inner join withoutclaims on books.id = withoutclaims.id ##+end_src

Hmm, struggling with error: user-error: Region is longer than ‘org-table-convert-region-max-lines’ (999) lines; not converting

I've commented out those blocks as it is also causing my export process to fail.

Heh, OK, as it turns out, if you view sql in Metabase, even on a question where you'd made use of models, you get the full SQL. So I can just cheat and take that.

I like the idea of defining intermediate models like I was trying above in org there, but I'll have to come back to that because of the issue with number of results.

OK, so the SQL works well, outputs as a table which exports as an HTML table. But it's lacking in formatting. Wonder if I can take the output and then process it with emacs to get better formatting?

Nice, yes, I can.