[Feature request] List users not suscribed to any list

[jorge-vitrubio]

We have had some spam users created as contacts to the listmonk, but because they are opt-in newsletters this has had no effect so far except that they are on the DB but not in any list

Would be good to know how to query or select all contacts that are not subscribed to no list so they could be deleted or filtered for whatever purpouse.

There might be a way usin sql queries that I don´t know

thanks in advance

[MaximilianKohler]

You can remove them under /admin/settings/maintenance -> orphans, but I'd like to be able to view and download them [first]. I don't know of a way to do that though.

[knadh]

NOT EXISTS (SELECT 1 FROM subscriber_lists sl WHERE sl.subscriber_id = subscribers.id)

This should give subscribers who are not in any lists.

[MaximilianKohler]

That didn't work for me.

psql -U listmonk -h localhost -p 5432 listmonk
Password for user listmonk:
psql (15.5)
Type "help" for help.

listmonk=> NOT EXISTS (SELECT 1 FROM subscriber_lists sl WHERE sl.subscriber_id = subscribers.id)
listmonk->

It should be returning at least 2 results:

[MaximilianKohler]

I thought it was just missing the semicolon at the end, but when I added it there's an error:

listmonk=> NOT EXISTS (SELECT 1 FROM subscriber_lists sl WHERE sl.subscriber_id = subscribers.id);
ERROR:  syntax error at or near "NOT"
LINE 1: NOT EXISTS (SELECT 1 FROM subscriber_lists sl WHERE sl.subsc...
        ^
listmonk=>

It looks like the proper command https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-exists/ is:

SELECT *
FROM subscribers
WHERE NOT EXISTS
    (SELECT 1
     FROM subscriber_lists sl 
     WHERE sl.subscriber_id = subscribers.id);

You can copy-paste all those lines at once and hit enter.

EDIT: Oh, knahd's command works from listmonk's UI -- /admin/subscribers -> advanced SQL search.