Fixing slow joins to post_read, post_saved, and comment_saved . (#2738)

[lemmy.git] / crates / db_views / src / post_view.rs

diff --git a/crates/db_views/src/post_view.rs b/crates/db_views/src/post_view.rs
index f5c8100fd186afd3dece252fcb1bce4dd581e80d..88ff904b4bb50778857dd60df714a939ccf2e0a1 100644 (file)
--- a/crates/db_views/src/post_view.rs
+++ b/crates/db_views/src/post_view.rs
@@ -324,17 +324,16 @@ impl<'a> PostQuery<'a> {
         }
       }
     }
-
-    if let Some(community_id) = self.community_id {
+    if self.community_id.is_none() && self.community_actor_id.is_none() {
+      query = query.then_order_by(post_aggregates::featured_local.desc());
+    } else if let Some(community_id) = self.community_id {
       query = query
         .filter(post::community_id.eq(community_id))
-        .then_order_by(post_aggregates::stickied.desc());
-    }
-
-    if let Some(community_actor_id) = self.community_actor_id {
+        .then_order_by(post_aggregates::featured_community.desc());
+    } else if let Some(community_actor_id) = self.community_actor_id {
       query = query
         .filter(community::actor_id.eq(community_actor_id))
-        .then_order_by(post_aggregates::stickied.desc());
+        .then_order_by(post_aggregates::featured_community.desc());
     }
 
     if let Some(url_search) = self.url_search {
@@ -366,17 +365,17 @@ impl<'a> PostQuery<'a> {
     };
 
     if self.saved_only.unwrap_or(false) {
-      query = query.filter(post_saved::id.is_not_null());
+      query = query.filter(post_saved::post_id.is_not_null());
     }
     // Only hide the read posts, if the saved_only is false. Otherwise ppl with the hide_read
     // setting wont be able to see saved posts.
     else if !self.local_user.map(|l| l.show_read_posts).unwrap_or(true) {
-      query = query.filter(post_read::id.is_null());
+      query = query.filter(post_read::post_id.is_null());
     }
 
     if self.local_user.is_some() {
       // Filter out the rows with missing languages
-      query = query.filter(local_user_language::id.is_not_null());
+      query = query.filter(local_user_language::language_id.is_not_null());
 
       // Don't show blocked communities or persons
       query = query.filter(community_block::person_id.is_null());
@@ -755,7 +754,10 @@ mod tests {
     let pool = &build_db_pool_for_tests().await;
     let data = init_data(pool).await;
 
-    let spanish_id = Language::read_id_from_code(pool, "es").await.unwrap();
+    let spanish_id = Language::read_id_from_code(pool, Some("es"))
+      .await
+      .unwrap()
+      .unwrap();
     let post_spanish = PostInsertForm::builder()
       .name("asffgdsc".to_string())
       .creator_id(data.inserted_person.id)
@@ -777,7 +779,10 @@ mod tests {
     // no language filters specified, all posts should be returned
     assert_eq!(3, post_listings_all.len());
 
-    let french_id = Language::read_id_from_code(pool, "fr").await.unwrap();
+    let french_id = Language::read_id_from_code(pool, Some("fr"))
+      .await
+      .unwrap()
+      .unwrap();
     LocalUserLanguage::update(pool, vec![french_id], data.inserted_local_user.id)
       .await
       .unwrap();
@@ -795,7 +800,10 @@ mod tests {
     assert_eq!(1, post_listing_french.len());
     assert_eq!(french_id, post_listing_french[0].post.language_id);
 
-    let undetermined_id = Language::read_id_from_code(pool, "und").await.unwrap();
+    let undetermined_id = Language::read_id_from_code(pool, Some("und"))
+      .await
+      .unwrap()
+      .unwrap();
     LocalUserLanguage::update(
       pool,
       vec![french_id, undetermined_id],
@@ -860,7 +868,6 @@ mod tests {
         removed: false,
         deleted: false,
         locked: false,
-        stickied: false,
         nsfw: false,
         embed_title: None,
         embed_description: None,
@@ -869,6 +876,8 @@ mod tests {
         ap_id: inserted_post.ap_id.clone(),
         local: true,
         language_id: LanguageId(47),
+        featured_community: false,
+        featured_local: false,
       },
       my_vote: None,
       unread_comments: 0,
@@ -919,10 +928,11 @@ mod tests {
         score: 0,
         upvotes: 0,
         downvotes: 0,
-        stickied: false,
         published: agg.published,
         newest_comment_time_necro: inserted_post.published,
         newest_comment_time: inserted_post.published,
+        featured_community: false,
+        featured_local: false,
       },
       subscribed: SubscribedType::NotSubscribed,
       read: false,