Notices where this attachment appears
-
Embed this notice
@j @lanodan @hj this
select ap_id from users join (select following_id from following_relationships where follower_id = (select id from users where nickname = 'CHANGE ME')) as _user on users.id = _user.following_id;
-
Embed this notice
@j @lanodan @hj probably easier to run two queries than complicate the sql futher
select concat(nickname, '@bae.st') from users join (select following_id from following_relationships where follower_id = (select id from users where nickname = 'CHANGEME')) as _user on users.id = _user.following_id where users.local = 't';
select nickname from users join (select following_id from following_relationships where follower_id = (select id from users where nickname = 'CHANGEME')) as _user on users.id = _user.following_id where users.local = 'f';
-
Embed this notice
@i @j @lanodan @hj
>easier to run two queries
Why not just union these queries?
select concat(nickname, '@bae.st')
from users
join (
select following_id
from following_relationships
where follower_id = (
select id
from users
where nickname = 'CHANGEME'
)
) as _user on users.id = _user.following_id
where users.local = 't'
union
select nickname
from users
join (
select following_id
from following_relationships
where follower_id = (
select id
from users
where nickname = 'CHANGEME'
)
) as _user on users.id = _user.following_id
where users.local = 'f';
-
Embed this notice
@Humpleupagus @cassidyclown @lina @mint Aside from 30 days interval and direct references, you may also want to consider following relationships of local users,
with
follows as (
select distinct
ap_id
from following_relationships join users on users.id = following_relationships.following_id
),
ids as (
...
join objects on
...
not (
... or
exists (select 1 from follows where activities.data::text like '%' || ap_id || '%' or objects.data::text like '%' || ap_id || '%')
)
)
...
But it would be very slow. Checking specific fields, e.g. actor/announces/from/to/cc/bcc and using array operators can improve the performance, but it can still be a lot of relations to process for each status.