migrations/2020-06-30-135809_remove_mat_views/down.sql

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