API tools faq
paste
Advertisement
Guest User

SQL

a guest
Sep 19th, 2023
107
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 12.56 KB | None | 0 0
raw download clone embed print report
  1. SELECT "note"."id" AS "note_id",
  2.   "note"."createdAt" AS "note_createdAt",
  3.   "note"."replyId" AS "note_replyId",
  4.   "note"."renoteId" AS "note_renoteId",
  5.   "note"."threadId" AS "note_threadId",
  6.   "note"."text" AS "note_text",
  7.   "note"."name" AS "note_name",
  8.   "note"."cw" AS "note_cw",
  9.   "note"."userId" AS "note_userId",
  10.   "note"."localOnly" AS "note_localOnly",
  11.   "note"."reactionAcceptance" AS "note_reactionAcceptance",
  12.   "note"."renoteCount" AS "note_renoteCount",
  13.   "note"."repliesCount" AS "note_repliesCount",
  14.   "note"."reactions" AS "note_reactions",
  15.   "note"."visibility" AS "note_visibility",
  16.   "note"."uri" AS "note_uri",
  17.   "note"."url" AS "note_url",
  18.   "note"."fileIds" AS "note_fileIds",
  19.   "note"."attachedFileTypes" AS "note_attachedFileTypes",
  20.   "note"."visibleUserIds" AS "note_visibleUserIds",
  21.   "note"."mentions" AS "note_mentions",
  22.   "note"."mentionedRemoteUsers" AS "note_mentionedRemoteUsers",
  23.   "note"."emojis" AS "note_emojis",
  24.   "note"."tags" AS "note_tags",
  25.   "note"."hasPoll" AS "note_hasPoll",
  26.   "note"."channelId" AS "note_channelId",
  27.   "note"."userHost" AS "note_userHost",
  28.   "note"."replyUserId" AS "note_replyUserId",
  29.   "note"."replyUserHost" AS "note_replyUserHost",
  30.   "note"."renoteUserId" AS "note_renoteUserId",
  31.   "note"."renoteUserHost" AS "note_renoteUserHost",
  32.   "user"."id" AS "user_id",
  33.   "user"."createdAt" AS "user_createdAt",
  34.   "user"."updatedAt" AS "user_updatedAt",
  35.   "user"."lastFetchedAt" AS "user_lastFetchedAt",
  36.   "user"."lastActiveDate" AS "user_lastActiveDate",
  37.   "user"."hideOnlineStatus" AS "user_hideOnlineStatus",
  38.   "user"."username" AS "user_username",
  39.   "user"."name" AS "user_name",
  40.   "user"."followersCount" AS "user_followersCount",
  41.   "user"."followingCount" AS "user_followingCount",
  42.   "user"."movedToUri" AS "user_movedToUri",
  43.   "user"."movedAt" AS "user_movedAt",
  44.   "user"."alsoKnownAs" AS "user_alsoKnownAs",
  45.   "user"."notesCount" AS "user_notesCount",
  46.   "user"."avatarId" AS "user_avatarId",
  47.   "user"."bannerId" AS "user_bannerId",
  48.   "user"."avatarUrl" AS "user_avatarUrl",
  49.   "user"."bannerUrl" AS "user_bannerUrl",
  50.   "user"."avatarBlurhash" AS "user_avatarBlurhash",
  51.   "user"."bannerBlurhash" AS "user_bannerBlurhash",
  52.   "user"."tags" AS "user_tags",
  53.   "user"."isSuspended" AS "user_isSuspended",
  54.   "user"."isLocked" AS "user_isLocked",
  55.   "user"."isBot" AS "user_isBot",
  56.   "user"."isCat" AS "user_isCat",
  57.   "user"."isRoot" AS "user_isRoot",
  58.   "user"."isExplorable" AS "user_isExplorable",
  59.   "user"."isDeleted" AS "user_isDeleted",
  60.   "user"."emojis" AS "user_emojis",
  61.   "user"."host" AS "user_host",
  62.   "user"."inbox" AS "user_inbox",
  63.   "user"."sharedInbox" AS "user_sharedInbox",
  64.   "user"."featured" AS "user_featured",
  65.   "user"."uri" AS "user_uri",
  66.   "user"."followersUri" AS "user_followersUri",
  67.   "user"."token" AS "user_token",
  68.   "reply"."id" AS "reply_id",
  69.   "reply"."createdAt" AS "reply_createdAt",
  70.   "reply"."replyId" AS "reply_replyId",
  71.   "reply"."renoteId" AS "reply_renoteId",
  72.   "reply"."threadId" AS "reply_threadId",
  73.   "reply"."text" AS "reply_text",
  74.   "reply"."name" AS "reply_name",
  75.   "reply"."cw" AS "reply_cw",
  76.   "reply"."userId" AS "reply_userId",
  77.   "reply"."localOnly" AS "reply_localOnly",
  78.   "reply"."reactionAcceptance" AS "reply_reactionAcceptance",
  79.   "reply"."renoteCount" AS "reply_renoteCount",
  80.   "reply"."repliesCount" AS "reply_repliesCount",
  81.   "reply"."reactions" AS "reply_reactions",
  82.   "reply"."visibility" AS "reply_visibility",
  83.   "reply"."uri" AS "reply_uri",
  84.   "reply"."url" AS "reply_url",
  85.   "reply"."fileIds" AS "reply_fileIds",
  86.   "reply"."attachedFileTypes" AS "reply_attachedFileTypes",
  87.   "reply"."visibleUserIds" AS "reply_visibleUserIds",
  88.   "reply"."mentions" AS "reply_mentions",
  89.   "reply"."mentionedRemoteUsers" AS "reply_mentionedRemoteUsers",
  90.   "reply"."emojis" AS "reply_emojis",
  91.   "reply"."tags" AS "reply_tags",
  92.   "reply"."hasPoll" AS "reply_hasPoll",
  93.   "reply"."channelId" AS "reply_channelId",
  94.   "reply"."userHost" AS "reply_userHost",
  95.   "reply"."replyUserId" AS "reply_replyUserId",
  96.   "reply"."replyUserHost" AS "reply_replyUserHost",
  97.   "reply"."renoteUserId" AS "reply_renoteUserId",
  98.   "reply"."renoteUserHost" AS "reply_renoteUserHost",
  99.   "renote"."id" AS "renote_id",
  100.   "renote"."createdAt" AS "renote_createdAt",
  101.   "renote"."replyId" AS "renote_replyId",
  102.   "renote"."renoteId" AS "renote_renoteId",
  103.   "renote"."threadId" AS "renote_threadId",
  104.   "renote"."text" AS "renote_text",
  105.   "renote"."name" AS "renote_name",
  106.   "renote"."cw" AS "renote_cw",
  107.   "renote"."userId" AS "renote_userId",
  108.   "renote"."localOnly" AS "renote_localOnly",
  109.   "renote"."reactionAcceptance" AS "renote_reactionAcceptance",
  110.   "renote"."renoteCount" AS "renote_renoteCount",
  111.   "renote"."repliesCount" AS "renote_repliesCount",
  112.   "renote"."reactions" AS "renote_reactions",
  113.   "renote"."visibility" AS "renote_visibility",
  114.   "renote"."uri" AS "renote_uri",
  115.   "renote"."url" AS "renote_url",
  116.   "renote"."fileIds" AS "renote_fileIds",
  117.   "renote"."attachedFileTypes" AS "renote_attachedFileTypes",
  118.   "renote"."visibleUserIds" AS "renote_visibleUserIds",
  119.   "renote"."mentions" AS "renote_mentions",
  120.   "renote"."mentionedRemoteUsers" AS "renote_mentionedRemoteUsers",
  121.   "renote"."emojis" AS "renote_emojis",
  122.   "renote"."tags" AS "renote_tags",
  123.   "renote"."hasPoll" AS "renote_hasPoll",
  124.   "renote"."channelId" AS "renote_channelId",
  125.   "renote"."userHost" AS "renote_userHost",
  126.   "renote"."replyUserId" AS "renote_replyUserId",
  127.   "renote"."replyUserHost" AS "renote_replyUserHost",
  128.   "renote"."renoteUserId" AS "renote_renoteUserId",
  129.   "renote"."renoteUserHost" AS "renote_renoteUserHost",
  130.   "replyUser"."id" AS "replyUser_id",
  131.   "replyUser"."createdAt" AS "replyUser_createdAt",
  132.   "replyUser"."updatedAt" AS "replyUser_updatedAt",
  133.   "replyUser"."lastFetchedAt" AS "replyUser_lastFetchedAt",
  134.   "replyUser"."lastActiveDate" AS "replyUser_lastActiveDate",
  135.   "replyUser"."hideOnlineStatus" AS "replyUser_hideOnlineStatus",
  136.   "replyUser"."username" AS "replyUser_username",
  137.   "replyUser"."name" AS "replyUser_name",
  138.   "replyUser"."followersCount" AS "replyUser_followersCount",
  139.   "replyUser"."followingCount" AS "replyUser_followingCount",
  140.   "replyUser"."movedToUri" AS "replyUser_movedToUri",
  141.   "replyUser"."movedAt" AS "replyUser_movedAt",
  142.   "replyUser"."alsoKnownAs" AS "replyUser_alsoKnownAs",
  143.   "replyUser"."notesCount" AS "replyUser_notesCount",
  144.   "replyUser"."avatarId" AS "replyUser_avatarId",
  145.   "replyUser"."bannerId" AS "replyUser_bannerId",
  146.   "replyUser"."avatarUrl" AS "replyUser_avatarUrl",
  147.   "replyUser"."bannerUrl" AS "replyUser_bannerUrl",
  148.   "replyUser"."avatarBlurhash" AS "replyUser_avatarBlurhash",
  149.   "replyUser"."bannerBlurhash" AS "replyUser_bannerBlurhash",
  150.   "replyUser"."tags" AS "replyUser_tags",
  151.   "replyUser"."isSuspended" AS "replyUser_isSuspended",
  152.   "replyUser"."isLocked" AS "replyUser_isLocked",
  153.   "replyUser"."isBot" AS "replyUser_isBot",
  154.   "replyUser"."isCat" AS "replyUser_isCat",
  155.   "replyUser"."isRoot" AS "replyUser_isRoot",
  156.   "replyUser"."isExplorable" AS "replyUser_isExplorable",
  157.   "replyUser"."isDeleted" AS "replyUser_isDeleted",
  158.   "replyUser"."emojis" AS "replyUser_emojis",
  159.   "replyUser"."host" AS "replyUser_host",
  160.   "replyUser"."inbox" AS "replyUser_inbox",
  161.   "replyUser"."sharedInbox" AS "replyUser_sharedInbox",
  162.   "replyUser"."featured" AS "replyUser_featured",
  163.   "replyUser"."uri" AS "replyUser_uri",
  164.   "replyUser"."followersUri" AS "replyUser_followersUri",
  165.   "replyUser"."token" AS "replyUser_token",
  166.   "renoteUser"."id" AS "renoteUser_id",
  167.   "renoteUser"."createdAt" AS "renoteUser_createdAt",
  168.   "renoteUser"."updatedAt" AS "renoteUser_updatedAt",
  169.   "renoteUser"."lastFetchedAt" AS "renoteUser_lastFetchedAt",
  170.   "renoteUser"."lastActiveDate" AS "renoteUser_lastActiveDate",
  171.   "renoteUser"."hideOnlineStatus" AS "renoteUser_hideOnlineStatus",
  172.   "renoteUser"."username" AS "renoteUser_username",
  173.   "renoteUser"."name" AS "renoteUser_name",
  174.   "renoteUser"."followersCount" AS "renoteUser_followersCount",
  175.   "renoteUser"."followingCount" AS "renoteUser_followingCount",
  176.   "renoteUser"."movedToUri" AS "renoteUser_movedToUri",
  177.   "renoteUser"."movedAt" AS "renoteUser_movedAt",
  178.   "renoteUser"."alsoKnownAs" AS "renoteUser_alsoKnownAs",
  179.   "renoteUser"."notesCount" AS "renoteUser_notesCount",
  180.   "renoteUser"."avatarId" AS "renoteUser_avatarId",
  181.   "renoteUser"."bannerId" AS "renoteUser_bannerId",
  182.   "renoteUser"."avatarUrl" AS "renoteUser_avatarUrl",
  183.   "renoteUser"."bannerUrl" AS "renoteUser_bannerUrl",
  184.   "renoteUser"."avatarBlurhash" AS "renoteUser_avatarBlurhash",
  185.   "renoteUser"."bannerBlurhash" AS "renoteUser_bannerBlurhash",
  186.   "renoteUser"."tags" AS "renoteUser_tags",
  187.   "renoteUser"."isSuspended" AS "renoteUser_isSuspended",
  188.   "renoteUser"."isLocked" AS "renoteUser_isLocked",
  189.   "renoteUser"."isBot" AS "renoteUser_isBot",
  190.   "renoteUser"."isCat" AS "renoteUser_isCat",
  191.   "renoteUser"."isRoot" AS "renoteUser_isRoot",
  192.   "renoteUser"."isExplorable" AS "renoteUser_isExplorable",
  193.   "renoteUser"."isDeleted" AS "renoteUser_isDeleted",
  194.   "renoteUser"."emojis" AS "renoteUser_emojis",
  195.   "renoteUser"."host" AS "renoteUser_host",
  196.   "renoteUser"."inbox" AS "renoteUser_inbox",
  197.   "renoteUser"."sharedInbox" AS "renoteUser_sharedInbox",
  198.   "renoteUser"."featured" AS "renoteUser_featured",
  199.   "renoteUser"."uri" AS "renoteUser_uri",
  200.   "renoteUser"."followersUri" AS "renoteUser_followersUri",
  201.   "renoteUser"."token" AS "renoteUser_token"
  202. FROM "note" "note"
  203.   INNER JOIN "user" "user" ON "user"."id" = "note"."userId"
  204.   LEFT JOIN "note" "reply" ON "reply"."id" = "note"."replyId"
  205.   LEFT JOIN "note" "renote" ON "renote"."id" = "note"."renoteId"
  206.   LEFT JOIN "user" "replyUser" ON "replyUser"."id" = "reply"."userId"
  207.   LEFT JOIN "user" "renoteUser" ON "renoteUser"."id" = "renote"."userId"
  208. WHERE "note"."userId" = '8zph36hh1o'
  209.   AND (
  210.     (
  211.       "note"."visibility" = 'public'
  212.       OR "note"."visibility" = 'home'
  213.     )
  214.     OR "note"."userId" = '8zph36hh1o'
  215.     OR '8zph36hh1o' = ANY("note"."visibleUserIds")
  216.     OR '8zph36hh1o' = ANY("note"."mentions")
  217.     OR (
  218.       "note"."visibility" = 'followers'
  219.       AND (
  220.         "note"."userId" IN (
  221.           SELECT "following"."followeeId" AS "following_followeeId"
  222.           FROM "following" "following"
  223.           WHERE "following"."followerId" = '8zph36hh1o'
  224.         )
  225.         OR "note"."replyUserId" = '8zph36hh1o'
  226.       )
  227.     )
  228.   )
  229.   AND "note"."userId" NOT IN (
  230.     SELECT "muting"."muteeId" AS "muting_muteeId"
  231.     FROM "muting" "muting"
  232.     WHERE "muting"."muterId" = '8zph36hh1o'
  233.       AND "muting"."muteeId" != '8zph36hh1o'
  234.   )
  235.   AND (
  236.     "note"."replyUserId" IS NULL
  237.     OR "note"."replyUserId" NOT IN (
  238.       SELECT "muting"."muteeId" AS "muting_muteeId"
  239.       FROM "muting" "muting"
  240.       WHERE "muting"."muterId" = '8zph36hh1o'
  241.         AND "muting"."muteeId" != '8zph36hh1o'
  242.     )
  243.   )
  244.   AND (
  245.     "note"."renoteUserId" IS NULL
  246.     OR "note"."renoteUserId" NOT IN (
  247.       SELECT "muting"."muteeId" AS "muting_muteeId"
  248.       FROM "muting" "muting"
  249.       WHERE "muting"."muterId" = '8zph36hh1o'
  250.         AND "muting"."muteeId" != '8zph36hh1o'
  251.     )
  252.   )
  253.   AND (
  254.     "note"."userHost" IS NULL
  255.     OR NOT (
  256.       (
  257.         SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances"
  258.         FROM "user_profile" "user_profile"
  259.         WHERE "user_profile"."userId" = '8zph36hh1o'
  260.       )::jsonb ? "note"."userHost"
  261.     )
  262.   )
  263.   AND (
  264.     "note"."replyUserHost" IS NULL
  265.     OR NOT (
  266.       (
  267.         SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances"
  268.         FROM "user_profile" "user_profile"
  269.         WHERE "user_profile"."userId" = '8zph36hh1o'
  270.       )::jsonb ? "note"."replyUserHost"
  271.     )
  272.   )
  273.   AND (
  274.     "note"."renoteUserHost" IS NULL
  275.     OR NOT (
  276.       (
  277.         SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances"
  278.         FROM "user_profile" "user_profile"
  279.         WHERE "user_profile"."userId" = '8zph36hh1o'
  280.       )::jsonb ? "note"."renoteUserHost"
  281.     )
  282.   )
  283.   AND "note"."userId" NOT IN (
  284.     SELECT "blocking"."blockerId" AS "blocking_blockerId"
  285.     FROM "blocking" "blocking"
  286.     WHERE "blocking"."blockeeId" = '8zph36hh1o'
  287.   )
  288.   AND (
  289.     "note"."replyUserId" IS NULL
  290.     OR "note"."replyUserId" NOT IN (
  291.       SELECT "blocking"."blockerId" AS "blocking_blockerId"
  292.       FROM "blocking" "blocking"
  293.       WHERE "blocking"."blockeeId" = '8zph36hh1o'
  294.     )
  295.   )
  296.   AND (
  297.     "note"."renoteUserId" IS NULL
  298.     OR "note"."renoteUserId" NOT IN (
  299.       SELECT "blocking"."blockerId" AS "blocking_blockerId"
  300.       FROM "blocking" "blocking"
  301.       WHERE "blocking"."blockeeId" = '8zph36hh1o'
  302.     )
  303.   )
  304. ORDER BY "note"."id" DESC
  305. LIMIT 10
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!