CREATE OR REPLACE VIEW public.local_person_subscribed_communities AS
 SELECT person.id AS "person.id",
    person.name AS "person.name",
    person.display_name AS "person.display_name",
    person.actor_id AS "person.actor_id",
    community.id AS "community.id",
    community.name AS "community.name",
    community.title AS "community.title",
    community.description AS "community.description",
    community.actor_id AS "community.actor_id",
    community.published AS "community.published",
    community.updated AS "community.updated",
    community.removed AS "community.removed"
   FROM ((community
     JOIN community_follower ON ((community_follower.community_id = community.id)))
     JOIN person ON ((community_follower.person_id = person.id)))
  WHERE (person.instance_id = 1);

This will create a view within the Lemmy database, for reusability. If you just want to run a one-off query or prefer to store it in other form, omit the first line.

  • @ShunkW
    link
    English
    21 year ago

    Why create a view to do so? Seems like you can do an on demand select here

    • MuddybulldogOPM
      link
      fedilink
      English
      1
      edit-2
      1 year ago

      I tend to create broad views and then do drill down queries against the views. Less typing, less eye fatigue.