backend/model/entity/
plants_impl.rs

1//! Contains the implementation of [`Plants`].
2
3use diesel::{
4    debug_query, pg::Pg, BoolExpressionMethods, ExpressionMethods, JoinOnDsl,
5    NullableExpressionMethods, QueryDsl, QueryResult,
6};
7use diesel_async::{AsyncPgConnection, RunQueryDsl};
8use log::debug;
9
10use crate::{
11    db::{
12        function::{array_to_string, greatest3, similarity},
13        pagination::Paginate,
14    },
15    model::{
16        dto::{Page, PageParameters, PlantsSummaryDto},
17        entity::PlantsAndFamily,
18    },
19    schema::plants::{self, all_columns, common_name_de, common_name_en, unique_name},
20};
21diesel::alias!(crate::schema::plants as family_plants: FamilyPlants);
22
23use super::Plants;
24
25impl Plants {
26    /// Get the top plants matching the search query.
27    ///
28    /// Uses `pg_trgm` to find matches in `unique_name`, `common_name_de`, `common_name_en` and `edible_uses_en`.
29    /// Ranks them using the `pg_trgm` function `similarity()`.
30    ///
31    /// # Errors
32    /// * Unknown, diesel doesn't say why it might error.
33    pub async fn search(
34        search_query: &str,
35        page_parameters: PageParameters,
36        conn: &mut AsyncPgConnection,
37    ) -> QueryResult<Page<PlantsSummaryDto>> {
38        let p1 = family_plants;
39
40        let score = greatest3(
41            similarity(unique_name, search_query),
42            similarity(array_to_string(common_name_de, " "), search_query),
43            similarity(array_to_string(common_name_en, " "), search_query),
44        );
45        // Find plants with the the highest similarity to the search_query in either unique_name,
46        // common_name_de or common_name_en, joined with their families unique_name.
47        let query = plants::table
48            .left_join(p1.on(plants::family.eq(p1.field(plants::id).nullable())))
49            .select((
50                plants::all_columns,
51                p1.field(plants::unique_name).nullable(),
52            ))
53            .filter(
54                similarity(unique_name, search_query)
55                    .gt(0.1)
56                    .or(similarity(array_to_string(common_name_de, " "), search_query).gt(0.1))
57                    .or(similarity(array_to_string(common_name_en, " "), search_query).gt(0.1)),
58            )
59            .order(score.desc())
60            .paginate(page_parameters.page)
61            .per_page(page_parameters.per_page);
62        debug!("{}", debug_query::<Pg, _>(&query));
63        query
64            .load_page::<PlantsAndFamily>(conn)
65            .await
66            .map(Page::from_entity)
67    }
68
69    /// Get a page of some plants.
70    ///
71    /// # Errors
72    /// * Unknown, diesel doesn't say why it might error.
73    pub async fn find_any(
74        page_parameters: PageParameters,
75        conn: &mut AsyncPgConnection,
76    ) -> QueryResult<Page<PlantsSummaryDto>> {
77        let p1 = family_plants;
78        let query = plants::table
79            .left_join(p1.on(plants::family.eq(p1.field(plants::id).nullable())))
80            .select((all_columns, p1.field(plants::unique_name).nullable()))
81            .into_boxed()
82            .paginate(page_parameters.page)
83            .per_page(page_parameters.per_page);
84        debug!("{}", debug_query::<Pg, _>(&query));
85        query
86            .load_page::<PlantsAndFamily>(conn)
87            .await
88            .map(Page::from_entity)
89    }
90
91    /// Fetch plant by id from the database.
92    ///
93    /// # Errors
94    /// * Unknown, diesel doesn't say why it might error.
95    pub async fn find_by_id(
96        id: i64,
97        conn: &mut AsyncPgConnection,
98    ) -> QueryResult<PlantsSummaryDto> {
99        let p1 = family_plants;
100        let query = plants::table
101            .find(id)
102            .left_join(p1.on(plants::family.eq(p1.field(plants::id).nullable())))
103            .select((
104                plants::all_columns,
105                p1.field(plants::unique_name).nullable(),
106            ));
107        debug!("{}", debug_query::<Pg, _>(&query));
108        query.first::<PlantsAndFamily>(conn).await.map(Into::into)
109    }
110}
backend/model/entity/plants_impl.rs

backend/model/entity/
plants_impl.rs
