Skip to content

High performance self-hosted photo and video management solution.

1.7 KB
Raw Blame
-- NOTE: This file is auto generated by ./sql-generator

-- ActivityRepository.search
select
  "activity".*,
  to_json("user") as "user"
from
  "activity"
  inner join "user" as "user2" on "user2"."id" = "activity"."userId"
  and "user2"."deletedAt" is null
  inner join lateral (
    select
      "user2"."id",
      "user2"."name",
      "user2"."email",
      "user2"."avatarColor",
      "user2"."profileImagePath",
      "user2"."profileChangedAt"
    from
      (
        select
          1
      ) as "dummy"
  ) as "user" on true
  left join "asset" on "asset"."id" = "activity"."assetId"
where
  "activity"."albumId" = $1
  and "asset"."deletedAt" is null
order by
  "activity"."createdAt" asc

-- ActivityRepository.create
insert into
  "activity" ("albumId", "userId")
values
  ($1, $2)
returning
  *,
  (
    select
      to_json(obj)
    from
      (
        select
          "id",
          "name",
          "email",
          "avatarColor",
          "profileImagePath",
          "profileChangedAt"
        from
          "user"
        where
          "user"."id" = "activity"."userId"
      ) as obj
  ) as "user"

-- ActivityRepository.delete
delete from "activity"
where
  "id" = $1::uuid

-- ActivityRepository.getStatistics
select
  count(*) filter (
    where
      "activity"."isLiked" = $1
  ) as "comments",
  count(*) filter (
    where
      "activity"."isLiked" = $2
  ) as "likes"
from
  "activity"
  inner join "user" on "user"."id" = "activity"."userId"
  and "user"."deletedAt" is null
  left join "asset" on "asset"."id" = "activity"."assetId"
where
  "activity"."assetId" = $3
  and "activity"."albumId" = $4
  and (
    (
      "asset"."deletedAt" is null
      and "asset"."visibility" != 'locked'
    )
    or "asset"."id" is null
  )