API tools faq
paste
Advertisement
Pandaaaa906

SQL

Pandaaaa906
Sep 20th, 2023
1,296
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 12.51 KB | None | 0 0
raw download clone embed print report diff
  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.         EXISTS (
  221.           SELECT 1
  222.           FROM "following" "following"
  223.           WHERE "following"."followerId" = '8zph36hh1o'
  224.           AND "following"."followeeId" = "note"."userId"
  225.         )
  226.         OR "note"."replyUserId" = '8zph36hh1o'
  227.       )
  228.     )
  229.   )
  230.   AND NOT EXISTS (
  231.     SELECT 1
  232.     FROM "muting" "muting"
  233.     WHERE "muting"."muterId" = '8zph36hh1o'
  234.     AND "muting"."muteeId" != '8zph36hh1o'
  235.     AND "muting"."muteeId" = "note"."userId"
  236.   )
  237.   AND (
  238.     "note"."replyUserId" IS NULL
  239.     OR NOT EXISTS (
  240.       SELECT 1
  241.       FROM "muting" "muting"
  242.       WHERE "muting"."muterId" = '8zph36hh1o'
  243.         AND "muting"."muteeId" != '8zph36hh1o'
  244.         AND "muting"."muteeId" = "note"."replyUserId"
  245.     )
  246.   )
  247.   AND (
  248.     "note"."renoteUserId" IS NULL
  249.     OR NOT EXISTS (
  250.       SELECT 1
  251.       FROM "muting" "muting"
  252.       WHERE "muting"."muterId" = '8zph36hh1o'
  253.         AND "muting"."muteeId" != '8zph36hh1o'
  254.         AND "muting"."muteeId" = "note"."renoteUserId"
  255.     )
  256.   )
  257.   AND (
  258.     "note"."userHost" IS NULL
  259.     OR NOT (
  260.       (
  261.         SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances"
  262.         FROM "user_profile" "user_profile"
  263.         WHERE "user_profile"."userId" = '8zph36hh1o'
  264.       )::jsonb ? "note"."userHost"
  265.     )
  266.   )
  267.   AND (
  268.     "note"."replyUserHost" IS NULL
  269.     OR NOT (
  270.       (
  271.         SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances"
  272.         FROM "user_profile" "user_profile"
  273.         WHERE "user_profile"."userId" = '8zph36hh1o'
  274.       )::jsonb ? "note"."replyUserHost"
  275.     )
  276.   )
  277.   AND (
  278.     "note"."renoteUserHost" IS NULL
  279.     OR NOT (
  280.       (
  281.         SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances"
  282.         FROM "user_profile" "user_profile"
  283.         WHERE "user_profile"."userId" = '8zph36hh1o'
  284.       )::jsonb ? "note"."renoteUserHost"
  285.     )
  286.   )
  287.   AND NOT EXISTS (
  288.     SELECT 1
  289.     FROM "blocking" "blocking"
  290.     WHERE "blocking"."blockeeId" = '8zph36hh1o'
  291.     AND "blocking"."blockerId" = "note"."userId"
  292.   )
  293.   AND (
  294.     "note"."replyUserId" IS NULL
  295.     OR NOT EXISTS (
  296.       SELECT 1
  297.       FROM "blocking" "blocking"
  298.       WHERE "blocking"."blockeeId" = '8zph36hh1o'
  299.       AND "blocking"."blockerId" = "note"."replyUserId"
  300.     )
  301.   )
  302.   AND (
  303.     "note"."renoteUserId" IS NULL
  304.     OR NOT EXISTS (
  305.       SELECT 1
  306.       FROM "blocking" "blocking"
  307.       WHERE "blocking"."blockeeId" = '8zph36hh1o'
  308.       AND "blocking"."blockerId" = "note"."renoteUserId"
  309.     )
  310.   )
  311. ORDER BY "note"."id" DESC
  312. 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!