migrations/2020-04-14-163701_update_views_for_activitypub/up.sql

   1 -- user_view
   2 DROP VIEW user_view CASCADE;
   3
   4 CREATE VIEW user_view AS
   5 SELECT
   6     u.id,
   7     u.actor_id,
   8     u.name,
   9     u.avatar,
  10     u.email,
  11     u.matrix_user_id,
  12     u.bio,
  13     u.local,
  14     u.admin,
  15     u.banned,
  16     u.show_avatars,
  17     u.send_notifications_to_email,
  18     u.published,
  19     (
  20         SELECT
  21             count(*)
  22         FROM
  23             post p
  24         WHERE
  25             p.creator_id = u.id) AS number_of_posts,
  26     (
  27         SELECT
  28             coalesce(sum(score), 0)
  29         FROM
  30             post p,
  31             post_like pl
  32         WHERE
  33             u.id = p.creator_id
  34             AND p.id = pl.post_id) AS post_score,
  35     (
  36         SELECT
  37             count(*)
  38         FROM
  39             comment c
  40         WHERE
  41             c.creator_id = u.id) AS number_of_comments,
  42     (
  43         SELECT
  44             coalesce(sum(score), 0)
  45         FROM
  46             comment c,
  47             comment_like cl
  48         WHERE
  49             u.id = c.creator_id
  50             AND c.id = cl.comment_id) AS comment_score
  51 FROM
  52     user_ u;
  53
  54 CREATE MATERIALIZED VIEW user_mview AS
  55 SELECT
  56     *
  57 FROM
  58     user_view;
  59
  60 CREATE UNIQUE INDEX idx_user_mview_id ON user_mview (id);
  61
  62 -- community_view
  63 DROP VIEW community_aggregates_view CASCADE;
  64
  65 CREATE VIEW community_aggregates_view AS
  66 -- Now that there's public and private keys, you have to be explicit here
  67 SELECT
  68     c.id,
  69     c.name,
  70     c.title,
  71     c.description,
  72     c.category_id,
  73     c.creator_id,
  74     c.removed,
  75     c.published,
  76     c.updated,
  77     c.deleted,
  78     c.nsfw,
  79     c.actor_id,
  80     c.local,
  81     c.last_refreshed_at,
  82     (
  83         SELECT
  84             actor_id
  85         FROM
  86             user_ u
  87         WHERE
  88             c.creator_id = u.id) AS creator_actor_id,
  89     (
  90         SELECT
  91             local
  92         FROM
  93             user_ u
  94         WHERE
  95             c.creator_id = u.id) AS creator_local,
  96     (
  97         SELECT
  98             name
  99         FROM
 100             user_ u
 101         WHERE
 102             c.creator_id = u.id) AS creator_name,
 103     (
 104         SELECT
 105             avatar
 106         FROM
 107             user_ u
 108         WHERE
 109             c.creator_id = u.id) AS creator_avatar,
 110     (
 111         SELECT
 112             name
 113         FROM
 114             category ct
 115         WHERE
 116             c.category_id = ct.id) AS category_name,
 117     (
 118         SELECT
 119             count(*)
 120         FROM
 121             community_follower cf
 122         WHERE
 123             cf.community_id = c.id) AS number_of_subscribers,
 124     (
 125         SELECT
 126             count(*)
 127         FROM
 128             post p
 129         WHERE
 130             p.community_id = c.id) AS number_of_posts,
 131     (
 132         SELECT
 133             count(*)
 134         FROM
 135             comment co,
 136             post p
 137         WHERE
 138             c.id = p.community_id
 139             AND p.id = co.post_id) AS number_of_comments,
 140     hot_rank ((
 141         SELECT
 142             count(*)
 143         FROM community_follower cf
 144         WHERE
 145             cf.community_id = c.id), c.published) AS hot_rank
 146 FROM
 147     community c;
 148
 149 CREATE MATERIALIZED VIEW community_aggregates_mview AS
 150 SELECT
 151     *
 152 FROM
 153     community_aggregates_view;
 154
 155 CREATE UNIQUE INDEX idx_community_aggregates_mview_id ON community_aggregates_mview (id);
 156
 157 CREATE VIEW community_view AS
 158 with all_community AS (
 159     SELECT
 160         ca.*
 161     FROM
 162         community_aggregates_view ca
 163 )
 164 SELECT
 165     ac.*,
 166     u.id AS user_id,
 167     (
 168         SELECT
 169             cf.id::boolean
 170         FROM
 171             community_follower cf
 172         WHERE
 173             u.id = cf.user_id
 174             AND ac.id = cf.community_id) AS subscribed
 175 FROM
 176     user_ u
 177     CROSS JOIN all_community ac
 178 UNION ALL
 179 SELECT
 180     ac.*,
 181     NULL AS user_id,
 182     NULL AS subscribed
 183 FROM
 184     all_community ac;
 185
 186 CREATE VIEW community_mview AS
 187 with all_community AS (
 188     SELECT
 189         ca.*
 190     FROM
 191         community_aggregates_mview ca
 192 )
 193 SELECT
 194     ac.*,
 195     u.id AS user_id,
 196     (
 197         SELECT
 198             cf.id::boolean
 199         FROM
 200             community_follower cf
 201         WHERE
 202             u.id = cf.user_id
 203             AND ac.id = cf.community_id) AS subscribed
 204 FROM
 205     user_ u
 206     CROSS JOIN all_community ac
 207 UNION ALL
 208 SELECT
 209     ac.*,
 210     NULL AS user_id,
 211     NULL AS subscribed
 212 FROM
 213     all_community ac;
 214
 215 -- community views
 216 DROP VIEW community_moderator_view;
 217
 218 DROP VIEW community_follower_view;
 219
 220 DROP VIEW community_user_ban_view;
 221
 222 CREATE VIEW community_moderator_view AS
 223 SELECT
 224     *,
 225     (
 226         SELECT
 227             actor_id
 228         FROM
 229             user_ u
 230         WHERE
 231             cm.user_id = u.id) AS user_actor_id,
 232     (
 233         SELECT
 234             local
 235         FROM
 236             user_ u
 237         WHERE
 238             cm.user_id = u.id) AS user_local,
 239     (
 240         SELECT
 241             name
 242         FROM
 243             user_ u
 244         WHERE
 245             cm.user_id = u.id) AS user_name,
 246     (
 247         SELECT
 248             avatar
 249         FROM
 250             user_ u
 251         WHERE
 252             cm.user_id = u.id), (
 253         SELECT
 254             actor_id
 255         FROM
 256             community c
 257         WHERE
 258             cm.community_id = c.id) AS community_actor_id,
 259     (
 260         SELECT
 261             local
 262         FROM
 263             community c
 264         WHERE
 265             cm.community_id = c.id) AS community_local,
 266     (
 267         SELECT
 268             name
 269         FROM
 270             community c
 271         WHERE
 272             cm.community_id = c.id) AS community_name
 273 FROM
 274     community_moderator cm;
 275
 276 CREATE VIEW community_follower_view AS
 277 SELECT
 278     *,
 279     (
 280         SELECT
 281             actor_id
 282         FROM
 283             user_ u
 284         WHERE
 285             cf.user_id = u.id) AS user_actor_id,
 286     (
 287         SELECT
 288             local
 289         FROM
 290             user_ u
 291         WHERE
 292             cf.user_id = u.id) AS user_local,
 293     (
 294         SELECT
 295             name
 296         FROM
 297             user_ u
 298         WHERE
 299             cf.user_id = u.id) AS user_name,
 300     (
 301         SELECT
 302             avatar
 303         FROM
 304             user_ u
 305         WHERE
 306             cf.user_id = u.id), (
 307         SELECT
 308             actor_id
 309         FROM
 310             community c
 311         WHERE
 312             cf.community_id = c.id) AS community_actor_id,
 313     (
 314         SELECT
 315             local
 316         FROM
 317             community c
 318         WHERE
 319             cf.community_id = c.id) AS community_local,
 320     (
 321         SELECT
 322             name
 323         FROM
 324             community c
 325         WHERE
 326             cf.community_id = c.id) AS community_name
 327 FROM
 328     community_follower cf;
 329
 330 CREATE VIEW community_user_ban_view AS
 331 SELECT
 332     *,
 333     (
 334         SELECT
 335             actor_id
 336         FROM
 337             user_ u
 338         WHERE
 339             cm.user_id = u.id) AS user_actor_id,
 340     (
 341         SELECT
 342             local
 343         FROM
 344             user_ u
 345         WHERE
 346             cm.user_id = u.id) AS user_local,
 347     (
 348         SELECT
 349             name
 350         FROM
 351             user_ u
 352         WHERE
 353             cm.user_id = u.id) AS user_name,
 354     (
 355         SELECT
 356             avatar
 357         FROM
 358             user_ u
 359         WHERE
 360             cm.user_id = u.id), (
 361         SELECT
 362             actor_id
 363         FROM
 364             community c
 365         WHERE
 366             cm.community_id = c.id) AS community_actor_id,
 367     (
 368         SELECT
 369             local
 370         FROM
 371             community c
 372         WHERE
 373             cm.community_id = c.id) AS community_local,
 374     (
 375         SELECT
 376             name
 377         FROM
 378             community c
 379         WHERE
 380             cm.community_id = c.id) AS community_name
 381 FROM
 382     community_user_ban cm;
 383
 384 -- post_view
 385 DROP VIEW post_view;
 386
 387 DROP VIEW post_mview;
 388
 389 DROP MATERIALIZED VIEW post_aggregates_mview;
 390
 391 DROP VIEW post_aggregates_view;
 392
 393 -- regen post view
 394 CREATE VIEW post_aggregates_view AS
 395 SELECT
 396     p.*,
 397     (
 398         SELECT
 399             u.banned
 400         FROM
 401             user_ u
 402         WHERE
 403             p.creator_id = u.id) AS banned,
 404     (
 405         SELECT
 406             cb.id::bool
 407         FROM
 408             community_user_ban cb
 409         WHERE
 410             p.creator_id = cb.user_id
 411             AND p.community_id = cb.community_id) AS banned_from_community,
 412     (
 413         SELECT
 414             actor_id
 415         FROM
 416             user_
 417         WHERE
 418             p.creator_id = user_.id) AS creator_actor_id,
 419     (
 420         SELECT
 421             local
 422         FROM
 423             user_
 424         WHERE
 425             p.creator_id = user_.id) AS creator_local,
 426     (
 427         SELECT
 428             name
 429         FROM
 430             user_
 431         WHERE
 432             p.creator_id = user_.id) AS creator_name,
 433     (
 434         SELECT
 435             avatar
 436         FROM
 437             user_
 438         WHERE
 439             p.creator_id = user_.id) AS creator_avatar,
 440     (
 441         SELECT
 442             actor_id
 443         FROM
 444             community
 445         WHERE
 446             p.community_id = community.id) AS community_actor_id,
 447     (
 448         SELECT
 449             local
 450         FROM
 451             community
 452         WHERE
 453             p.community_id = community.id) AS community_local,
 454     (
 455         SELECT
 456             name
 457         FROM
 458             community
 459         WHERE
 460             p.community_id = community.id) AS community_name,
 461     (
 462         SELECT
 463             removed
 464         FROM
 465             community c
 466         WHERE
 467             p.community_id = c.id) AS community_removed,
 468     (
 469         SELECT
 470             deleted
 471         FROM
 472             community c
 473         WHERE
 474             p.community_id = c.id) AS community_deleted,
 475     (
 476         SELECT
 477             nsfw
 478         FROM
 479             community c
 480         WHERE
 481             p.community_id = c.id) AS community_nsfw,
 482     (
 483         SELECT
 484             count(*)
 485         FROM
 486             comment
 487         WHERE
 488             comment.post_id = p.id) AS number_of_comments,
 489     coalesce(sum(pl.score), 0) AS score,
 490     count(
 491         CASE WHEN pl.score = 1 THEN
 492             1
 493         ELSE
 494             NULL
 495         END) AS upvotes,
 496     count(
 497         CASE WHEN pl.score = - 1 THEN
 498             1
 499         ELSE
 500             NULL
 501         END) AS downvotes,
 502     hot_rank (coalesce(sum(pl.score), 0), (
 503             CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
 504                 p.published -- Prevents necro-bumps
 505             ELSE
 506                 greatest (c.recent_comment_time, p.published)
 507             END)) AS hot_rank,
 508     (
 509         CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
 510             p.published -- Prevents necro-bumps
 511         ELSE
 512             greatest (c.recent_comment_time, p.published)
 513         END) AS newest_activity_time
 514 FROM
 515     post p
 516     LEFT JOIN post_like pl ON p.id = pl.post_id
 517     LEFT JOIN (
 518         SELECT
 519             post_id,
 520             max(published) AS recent_comment_time
 521         FROM
 522             comment
 523         GROUP BY
 524             1) c ON p.id = c.post_id
 525 GROUP BY
 526     p.id,
 527     c.recent_comment_time;
 528
 529 CREATE MATERIALIZED VIEW post_aggregates_mview AS
 530 SELECT
 531     *
 532 FROM
 533     post_aggregates_view;
 534
 535 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
 536
 537 CREATE VIEW post_view AS
 538 with all_post AS (
 539     SELECT
 540         pa.*
 541     FROM
 542         post_aggregates_view pa
 543 )
 544 SELECT
 545     ap.*,
 546     u.id AS user_id,
 547     coalesce(pl.score, 0) AS my_vote,
 548     (
 549         SELECT
 550             cf.id::bool
 551         FROM
 552             community_follower cf
 553         WHERE
 554             u.id = cf.user_id
 555             AND cf.community_id = ap.community_id) AS subscribed,
 556     (
 557         SELECT
 558             pr.id::bool
 559         FROM
 560             post_read pr
 561         WHERE
 562             u.id = pr.user_id
 563             AND pr.post_id = ap.id) AS read,
 564     (
 565         SELECT
 566             ps.id::bool
 567         FROM
 568             post_saved ps
 569         WHERE
 570             u.id = ps.user_id
 571             AND ps.post_id = ap.id) AS saved
 572 FROM
 573     user_ u
 574     CROSS JOIN all_post ap
 575     LEFT JOIN post_like pl ON u.id = pl.user_id
 576         AND ap.id = pl.post_id
 577     UNION ALL
 578     SELECT
 579         ap.*,
 580         NULL AS user_id,
 581         NULL AS my_vote,
 582         NULL AS subscribed,
 583         NULL AS read,
 584         NULL AS saved
 585     FROM
 586         all_post ap;
 587
 588 CREATE VIEW post_mview AS
 589 with all_post AS (
 590     SELECT
 591         pa.*
 592     FROM
 593         post_aggregates_mview pa
 594 )
 595 SELECT
 596     ap.*,
 597     u.id AS user_id,
 598     coalesce(pl.score, 0) AS my_vote,
 599     (
 600         SELECT
 601             cf.id::bool
 602         FROM
 603             community_follower cf
 604         WHERE
 605             u.id = cf.user_id
 606             AND cf.community_id = ap.community_id) AS subscribed,
 607     (
 608         SELECT
 609             pr.id::bool
 610         FROM
 611             post_read pr
 612         WHERE
 613             u.id = pr.user_id
 614             AND pr.post_id = ap.id) AS read,
 615     (
 616         SELECT
 617             ps.id::bool
 618         FROM
 619             post_saved ps
 620         WHERE
 621             u.id = ps.user_id
 622             AND ps.post_id = ap.id) AS saved
 623 FROM
 624     user_ u
 625     CROSS JOIN all_post ap
 626     LEFT JOIN post_like pl ON u.id = pl.user_id
 627         AND ap.id = pl.post_id
 628     UNION ALL
 629     SELECT
 630         ap.*,
 631         NULL AS user_id,
 632         NULL AS my_vote,
 633         NULL AS subscribed,
 634         NULL AS read,
 635         NULL AS saved
 636     FROM
 637         all_post ap;
 638
 639 -- reply_view, comment_view, user_mention
 640 DROP VIEW reply_view;
 641
 642 DROP VIEW user_mention_view;
 643
 644 DROP VIEW user_mention_mview;
 645
 646 DROP VIEW comment_view;
 647
 648 DROP VIEW comment_mview;
 649
 650 DROP MATERIALIZED VIEW comment_aggregates_mview;
 651
 652 DROP VIEW comment_aggregates_view;
 653
 654 -- reply and comment view
 655 CREATE VIEW comment_aggregates_view AS
 656 SELECT
 657     c.*,
 658     (
 659         SELECT
 660             community_id
 661         FROM
 662             post p
 663         WHERE
 664             p.id = c.post_id), (
 665         SELECT
 666             co.actor_id
 667         FROM
 668             post p,
 669             community co
 670         WHERE
 671             p.id = c.post_id
 672             AND p.community_id = co.id) AS community_actor_id,
 673     (
 674         SELECT
 675             co.local
 676         FROM
 677             post p,
 678             community co
 679         WHERE
 680             p.id = c.post_id
 681             AND p.community_id = co.id) AS community_local,
 682     (
 683         SELECT
 684             co.name
 685         FROM
 686             post p,
 687             community co
 688         WHERE
 689             p.id = c.post_id
 690             AND p.community_id = co.id) AS community_name,
 691     (
 692         SELECT
 693             u.banned
 694         FROM
 695             user_ u
 696         WHERE
 697             c.creator_id = u.id) AS banned,
 698     (
 699         SELECT
 700             cb.id::bool
 701         FROM
 702             community_user_ban cb,
 703             post p
 704         WHERE
 705             c.creator_id = cb.user_id
 706             AND p.id = c.post_id
 707             AND p.community_id = cb.community_id) AS banned_from_community,
 708     (
 709         SELECT
 710             actor_id
 711         FROM
 712             user_
 713         WHERE
 714             c.creator_id = user_.id) AS creator_actor_id,
 715     (
 716         SELECT
 717             local
 718         FROM
 719             user_
 720         WHERE
 721             c.creator_id = user_.id) AS creator_local,
 722     (
 723         SELECT
 724             name
 725         FROM
 726             user_
 727         WHERE
 728             c.creator_id = user_.id) AS creator_name,
 729     (
 730         SELECT
 731             avatar
 732         FROM
 733             user_
 734         WHERE
 735             c.creator_id = user_.id) AS creator_avatar,
 736     coalesce(sum(cl.score), 0) AS score,
 737     count(
 738         CASE WHEN cl.score = 1 THEN
 739             1
 740         ELSE
 741             NULL
 742         END) AS upvotes,
 743     count(
 744         CASE WHEN cl.score = - 1 THEN
 745             1
 746         ELSE
 747             NULL
 748         END) AS downvotes,
 749     hot_rank (coalesce(sum(cl.score), 0), c.published) AS hot_rank
 750 FROM
 751     comment c
 752     LEFT JOIN comment_like cl ON c.id = cl.comment_id
 753 GROUP BY
 754     c.id;
 755
 756 CREATE MATERIALIZED VIEW comment_aggregates_mview AS
 757 SELECT
 758     *
 759 FROM
 760     comment_aggregates_view;
 761
 762 CREATE UNIQUE INDEX idx_comment_aggregates_mview_id ON comment_aggregates_mview (id);
 763
 764 CREATE VIEW comment_view AS
 765 with all_comment AS (
 766     SELECT
 767         ca.*
 768     FROM
 769         comment_aggregates_view ca
 770 )
 771 SELECT
 772     ac.*,
 773     u.id AS user_id,
 774     coalesce(cl.score, 0) AS my_vote,
 775     (
 776         SELECT
 777             cf.id::boolean
 778         FROM
 779             community_follower cf
 780         WHERE
 781             u.id = cf.user_id
 782             AND ac.community_id = cf.community_id) AS subscribed,
 783     (
 784         SELECT
 785             cs.id::bool
 786         FROM
 787             comment_saved cs
 788         WHERE
 789             u.id = cs.user_id
 790             AND cs.comment_id = ac.id) AS saved
 791 FROM
 792     user_ u
 793     CROSS JOIN all_comment ac
 794     LEFT JOIN comment_like cl ON u.id = cl.user_id
 795         AND ac.id = cl.comment_id
 796     UNION ALL
 797     SELECT
 798         ac.*,
 799         NULL AS user_id,
 800         NULL AS my_vote,
 801         NULL AS subscribed,
 802         NULL AS saved
 803     FROM
 804         all_comment ac;
 805
 806 CREATE VIEW comment_mview AS
 807 with all_comment AS (
 808     SELECT
 809         ca.*
 810     FROM
 811         comment_aggregates_mview ca
 812 )
 813 SELECT
 814     ac.*,
 815     u.id AS user_id,
 816     coalesce(cl.score, 0) AS my_vote,
 817     (
 818         SELECT
 819             cf.id::boolean
 820         FROM
 821             community_follower cf
 822         WHERE
 823             u.id = cf.user_id
 824             AND ac.community_id = cf.community_id) AS subscribed,
 825     (
 826         SELECT
 827             cs.id::bool
 828         FROM
 829             comment_saved cs
 830         WHERE
 831             u.id = cs.user_id
 832             AND cs.comment_id = ac.id) AS saved
 833 FROM
 834     user_ u
 835     CROSS JOIN all_comment ac
 836     LEFT JOIN comment_like cl ON u.id = cl.user_id
 837         AND ac.id = cl.comment_id
 838     UNION ALL
 839     SELECT
 840         ac.*,
 841         NULL AS user_id,
 842         NULL AS my_vote,
 843         NULL AS subscribed,
 844         NULL AS saved
 845     FROM
 846         all_comment ac;
 847
 848 -- Do the reply_view referencing the comment_mview
 849 CREATE VIEW reply_view AS
 850 with closereply AS (
 851     SELECT
 852         c2.id,
 853         c2.creator_id AS sender_id,
 854         c.creator_id AS recipient_id
 855     FROM
 856         comment c
 857         INNER JOIN comment c2 ON c.id = c2.parent_id
 858     WHERE
 859         c2.creator_id != c.creator_id
 860         -- Do union where post is null
 861     UNION
 862     SELECT
 863         c.id,
 864         c.creator_id AS sender_id,
 865         p.creator_id AS recipient_id
 866     FROM
 867         comment c,
 868         post p
 869     WHERE
 870         c.post_id = p.id
 871         AND c.parent_id IS NULL
 872         AND c.creator_id != p.creator_id
 873 )
 874 SELECT
 875     cv.*,
 876     closereply.recipient_id
 877 FROM
 878     comment_mview cv,
 879     closereply
 880 WHERE
 881     closereply.id = cv.id;
 882
 883 -- user mention
 884 CREATE VIEW user_mention_view AS
 885 SELECT
 886     c.id,
 887     um.id AS user_mention_id,
 888     c.creator_id,
 889     c.creator_actor_id,
 890     c.creator_local,
 891     c.post_id,
 892     c.parent_id,
 893     c.content,
 894     c.removed,
 895     um.read,
 896     c.published,
 897     c.updated,
 898     c.deleted,
 899     c.community_id,
 900     c.community_actor_id,
 901     c.community_local,
 902     c.community_name,
 903     c.banned,
 904     c.banned_from_community,
 905     c.creator_name,
 906     c.creator_avatar,
 907     c.score,
 908     c.upvotes,
 909     c.downvotes,
 910     c.hot_rank,
 911     c.user_id,
 912     c.my_vote,
 913     c.saved,
 914     um.recipient_id,
 915     (
 916         SELECT
 917             actor_id
 918         FROM
 919             user_ u
 920         WHERE
 921             u.id = um.recipient_id) AS recipient_actor_id,
 922     (
 923         SELECT
 924             local
 925         FROM
 926             user_ u
 927         WHERE
 928             u.id = um.recipient_id) AS recipient_local
 929 FROM
 930     user_mention um,
 931     comment_view c
 932 WHERE
 933     um.comment_id = c.id;
 934
 935 CREATE VIEW user_mention_mview AS
 936 with all_comment AS (
 937     SELECT
 938         ca.*
 939     FROM
 940         comment_aggregates_mview ca
 941 )
 942 SELECT
 943     ac.id,
 944     um.id AS user_mention_id,
 945     ac.creator_id,
 946     ac.creator_actor_id,
 947     ac.creator_local,
 948     ac.post_id,
 949     ac.parent_id,
 950     ac.content,
 951     ac.removed,
 952     um.read,
 953     ac.published,
 954     ac.updated,
 955     ac.deleted,
 956     ac.community_id,
 957     ac.community_actor_id,
 958     ac.community_local,
 959     ac.community_name,
 960     ac.banned,
 961     ac.banned_from_community,
 962     ac.creator_name,
 963     ac.creator_avatar,
 964     ac.score,
 965     ac.upvotes,
 966     ac.downvotes,
 967     ac.hot_rank,
 968     u.id AS user_id,
 969     coalesce(cl.score, 0) AS my_vote,
 970     (
 971         SELECT
 972             cs.id::bool
 973         FROM
 974             comment_saved cs
 975         WHERE
 976             u.id = cs.user_id
 977             AND cs.comment_id = ac.id) AS saved,
 978     um.recipient_id,
 979     (
 980         SELECT
 981             actor_id
 982         FROM
 983             user_ u
 984         WHERE
 985             u.id = um.recipient_id) AS recipient_actor_id,
 986     (
 987         SELECT
 988             local
 989         FROM
 990             user_ u
 991         WHERE
 992             u.id = um.recipient_id) AS recipient_local
 993 FROM
 994     user_ u
 995     CROSS JOIN all_comment ac
 996     LEFT JOIN comment_like cl ON u.id = cl.user_id
 997         AND ac.id = cl.comment_id
 998     LEFT JOIN user_mention um ON um.comment_id = ac.id
 999 UNION ALL
1000 SELECT
1001     ac.id,
1002     um.id AS user_mention_id,
1003     ac.creator_id,
1004     ac.creator_actor_id,
1005     ac.creator_local,
1006     ac.post_id,
1007     ac.parent_id,
1008     ac.content,
1009     ac.removed,
1010     um.read,
1011     ac.published,
1012     ac.updated,
1013     ac.deleted,
1014     ac.community_id,
1015     ac.community_actor_id,
1016     ac.community_local,
1017     ac.community_name,
1018     ac.banned,
1019     ac.banned_from_community,
1020     ac.creator_name,
1021     ac.creator_avatar,
1022     ac.score,
1023     ac.upvotes,
1024     ac.downvotes,
1025     ac.hot_rank,
1026     NULL AS user_id,
1027     NULL AS my_vote,
1028     NULL AS saved,
1029     um.recipient_id,
1030     (
1031         SELECT
1032             actor_id
1033         FROM
1034             user_ u
1035         WHERE
1036             u.id = um.recipient_id) AS recipient_actor_id,
1037     (
1038         SELECT
1039             local
1040         FROM
1041             user_ u
1042         WHERE
1043             u.id = um.recipient_id) AS recipient_local
1044 FROM
1045     all_comment ac
1046     LEFT JOIN user_mention um ON um.comment_id = ac.id;
1047