Skip to main content

rok_db_core/
join.rs

1//! Typed joins (RFC 0001).
2//!
3//! ```ignore
4//! // Join through a relation, filter and order by the joined model, select columns of both.
5//! let rows: Vec<(String, String)> = Post::query()
6//!     .join(Post::AUTHOR)                       // INNER JOIN "users" ON "users"."id" = "posts"."author_id"
7//!     .filter(User::ROLE.eq("admin"))           // columns of joined models are allowed
8//!     .order_by(User::NAME.asc())
9//!     .select((Post::TITLE, User::NAME))
10//!     .fetch_all(&db)
11//!     .await?;
12//!
13//! // Ad-hoc joins on any two columns; LEFT JOIN keeps rows without a match.
14//! let per_category: Vec<(Option<String>, i64)> = Post::query()
15//!     .left_join(Category::ID.on(Post::CATEGORY_ID))
16//!     .group_by(Category::NAME)
17//!     .select((Category::NAME, Post::ID.count()))
18//!     .fetch_all(&db)
19//!     .await?;
20//!
21//! // Fetching root models: one row per post even through `has_many` joins.
22//! let users_with_hits: Vec<User> = User::query()
23//!     .join(User::POSTS)
24//!     .filter(Post::VIEWS.gt(1000))
25//!     .all(&db)
26//!     .await?;
27//! ```
28//!
29//! The compiler checks that every column used belongs to the root model or
30//! a joined one. Joined models' tenant, soft-delete and default scopes are
31//! applied in the `ON` clause. Joins that may produce several rows per root
32//! row (`has_many` and ad-hoc joins) are de-duplicated with `DISTINCT ON`
33//! the root key when fetching root models; counts use `COUNT(DISTINCT …)`.
34//!
35//! Projections that don't name a model, like `Projection::count_all()`,
36//! need one in a joined `select`: write `Projection::<Post>::count_all()`
37//! or a column aggregate such as `Post::ID.count()`.
38//!
39//! Through joins you can also `cursor_paginate`, `memoize` (invalidated by
40//! writes to any joined table), bulk `update` / `delete` / `restore`, and
41//! fetch whole models as tuples with [`Joined::all_with`].
42//!
43//! Not supported: self-joins (the same table twice).
44
45use std::fmt;
46use std::marker::PhantomData;
47
48use futures_core::stream::BoxStream;
49use sqlx::postgres::PgRow;
50
51use crate::expr::Cond;
52use crate::query::{JoinClause, join_scope};
53use crate::relation::{BelongsTo, HasMany, HasOne};
54use crate::{
55    Column, Executor, Expr, Model, Order, Page, Projected, Projection, Result, Select, Sql, Update,
56};
57
58/// Index markers proving a model is part of a joined query. Inferred by the
59/// compiler; you never write them.
60#[doc(hidden)]
61pub mod scope {
62    use std::marker::PhantomData;
63
64    /// The root model.
65    #[derive(Debug)]
66    pub struct Here;
67    /// The most recently joined model.
68    #[derive(Debug)]
69    pub struct Last;
70    /// A model joined earlier.
71    #[derive(Debug)]
72    pub struct There<I>(PhantomData<I>);
73}
74
75use scope::{Here, Last, There};
76
77/// `Self` is the root or one of the joined models of the query scope `S`
78/// (`(Root, Joins)`, with `Joins` = `()` or `(EarlierJoins, Model)`).
79#[diagnostic::on_unimplemented(
80    message = "`{Self}` is not part of this query",
81    label = "join `{Self}` first (`.join(…)` / `.left_join(…)`)"
82)]
83pub trait InScope<S, I> {}
84
85// The root is found by walking back to the empty join list, so every model
86// has exactly one proof (self-joins, which would make it ambiguous, are not
87// supported).
88impl<M> InScope<(M, ()), Here> for M {}
89impl<M, Rest, N> InScope<(M, (Rest, N)), Last> for N {}
90impl<X, M, Rest, N, I> InScope<(M, (Rest, N)), There<I>> for X where X: InScope<(M, Rest), I> {}
91
92/// The `ON` condition of a join (opaque).
93#[doc(hidden)]
94#[derive(Debug, Clone)]
95pub struct JoinCondition(pub(crate) Cond);
96
97/// Something that can be joined: a relation constant or `ColumnA.on(ColumnB)`.
98pub trait IntoJoin {
99    /// The model already in the query.
100    type From: Model;
101    /// The model being joined.
102    type To: Model;
103    #[doc(hidden)]
104    fn condition(&self) -> JoinCondition;
105    #[doc(hidden)]
106    fn multiplies(&self) -> bool;
107}
108
109fn eq_columns(
110    left: (&'static str, &'static str),
111    right: (&'static str, &'static str),
112) -> JoinCondition {
113    JoinCondition(Cond::Columns {
114        left,
115        op: "=",
116        right,
117    })
118}
119
120impl<C: Model, P: Model> IntoJoin for BelongsTo<C, P> {
121    type From = C;
122    type To = P;
123    fn condition(&self) -> JoinCondition {
124        eq_columns(
125            (P::TABLE, P::PRIMARY_KEY),
126            (C::TABLE, self.foreign_key().name()),
127        )
128    }
129    fn multiplies(&self) -> bool {
130        false
131    }
132}
133
134impl<P: Model, C: Model> IntoJoin for HasMany<P, C> {
135    type From = P;
136    type To = C;
137    fn condition(&self) -> JoinCondition {
138        eq_columns(
139            (C::TABLE, self.foreign_key().name()),
140            (P::TABLE, P::PRIMARY_KEY),
141        )
142    }
143    fn multiplies(&self) -> bool {
144        true
145    }
146}
147
148impl<P: Model, C: Model> IntoJoin for HasOne<P, C> {
149    type From = P;
150    type To = C;
151    fn condition(&self) -> JoinCondition {
152        eq_columns(
153            (C::TABLE, self.foreign_key().name()),
154            (P::TABLE, P::PRIMARY_KEY),
155        )
156    }
157    fn multiplies(&self) -> bool {
158        false
159    }
160}
161
162/// An ad-hoc join condition `to = from`, created with [`Column::on`].
163pub struct JoinOn<From, To> {
164    to: &'static str,
165    from: &'static str,
166    _models: PhantomData<fn() -> (From, To)>,
167}
168
169impl<From: Model, To: Model> IntoJoin for JoinOn<From, To> {
170    type From = From;
171    type To = To;
172    fn condition(&self) -> JoinCondition {
173        eq_columns((To::TABLE, self.to), (From::TABLE, self.from))
174    }
175    /// Unknown cardinality: assume it may multiply rows.
176    fn multiplies(&self) -> bool {
177        true
178    }
179}
180
181impl<From, To> fmt::Debug for JoinOn<From, To> {
182    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
183        f.debug_struct("JoinOn")
184            .field("to", &self.to)
185            .field("from", &self.from)
186            .finish()
187    }
188}
189
190impl<To: Model> Column<To> {
191    /// An ad-hoc join condition: join `To` where this column equals `other`
192    /// (a column of a model already in the query).
193    ///
194    /// ```ignore
195    /// Post::query().join(Category::ID.on(Post::CATEGORY_ID))
196    /// ```
197    pub fn on<From: Model>(self, other: Column<From>) -> JoinOn<From, To> {
198        JoinOn {
199            to: self.name(),
200            from: other.name(),
201            _models: PhantomData,
202        }
203    }
204}
205
206/// A value usable in a joined `select(…)`: a column or projection of some
207/// model in the query.
208pub trait ScopedItem {
209    /// The model the item belongs to.
210    type Model;
211    #[doc(hidden)]
212    fn into_projection(self) -> Projection<Self::Model>;
213}
214
215impl<X: Model> ScopedItem for Column<X> {
216    type Model = X;
217    fn into_projection(self) -> Projection<X> {
218        self.into()
219    }
220}
221
222impl<X> ScopedItem for Projection<X> {
223    type Model = X;
224    fn into_projection(self) -> Projection<X> {
225        self
226    }
227}
228
229/// The select list of a joined query: one [`ScopedItem`] or a tuple of up
230/// to 8, each from a model in the query scope `S`.
231pub trait ScopedProjections<S, I> {
232    #[doc(hidden)]
233    fn into_items<R>(self) -> Vec<Projection<R>>;
234}
235
236impl<S, I, A> ScopedProjections<S, (I,)> for A
237where
238    A: ScopedItem,
239    A::Model: InScope<S, I>,
240{
241    fn into_items<R>(self) -> Vec<Projection<R>> {
242        vec![self.into_projection().retag()]
243    }
244}
245
246macro_rules! scoped_tuples {
247    ($(($($t:ident $i:ident),+)),+ $(,)?) => {$(
248        #[allow(non_snake_case)]
249        impl<S, $($i,)+ $($t),+> ScopedProjections<S, ($($i,)+)> for ($($t,)+)
250        where
251            $($t: ScopedItem, $t::Model: InScope<S, $i>,)+
252        {
253            fn into_items<R>(self) -> Vec<Projection<R>> {
254                let ($($t,)+) = self;
255                vec![$($t.into_projection().retag()),+]
256            }
257        }
258    )+};
259}
260
261scoped_tuples! {
262    (A IA, B IB),
263    (A IA, B IB, C IC),
264    (A IA, B IB, C IC, D ID),
265    (A IA, B IB, C IC, D ID, E IE),
266    (A IA, B IB, C IC, D ID, E IE, F IF),
267    (A IA, B IB, C IC, D ID, E IE, F IF, G IG),
268    (A IA, B IB, C IC, D ID, E IE, F IF, G IG, H IH),
269}
270
271/// Joined models fetched next to the root model with [`Joined::all_with`]:
272/// a model `N`, `Option<N>` (`None` when a `LEFT JOIN` found no match), or
273/// a tuple of up to 8 of those, each part of the query scope `S`.
274#[diagnostic::on_unimplemented(
275    message = "`{Self}` can't be fetched from this query",
276    label = "fetch joined models, `Option`s of them or tuples of those"
277)]
278pub trait JoinedModels<S, I> {
279    /// What each row decodes to.
280    type Output;
281    #[doc(hidden)]
282    fn write_columns(sql: &mut Sql);
283    #[doc(hidden)]
284    fn decode(row: &PgRow, offset: &mut usize) -> Result<Self::Output>;
285}
286
287impl<S, I, N: Model + InScope<S, I>> JoinedModels<S, I> for N {
288    type Output = N;
289    fn write_columns(sql: &mut Sql) {
290        sql.push(", ");
291        crate::model::push_columns::<N>(sql);
292    }
293    fn decode(row: &PgRow, offset: &mut usize) -> Result<N> {
294        let model = N::from_row_at(row, *offset)?;
295        *offset += N::COLUMNS.len();
296        Ok(model)
297    }
298}
299
300impl<S, I, N: Model + InScope<S, I>> JoinedModels<S, I> for Option<N> {
301    type Output = Option<N>;
302    fn write_columns(sql: &mut Sql) {
303        <N as JoinedModels<S, I>>::write_columns(sql);
304    }
305    fn decode(row: &PgRow, offset: &mut usize) -> Result<Option<N>> {
306        use sqlx::{Row, ValueRef};
307        let mut missing = true;
308        for key in N::PRIMARY_KEYS {
309            let at = N::COLUMNS.iter().position(|c| c == key).unwrap_or(0);
310            missing &= row.try_get_raw(*offset + at)?.is_null();
311        }
312        if missing {
313            *offset += N::COLUMNS.len();
314            return Ok(None);
315        }
316        <N as JoinedModels<S, I>>::decode(row, offset).map(Some)
317    }
318}
319
320macro_rules! joined_model_tuples {
321    ($(($($t:ident $i:ident),+)),+ $(,)?) => {$(
322        impl<S, $($i,)+ $($t),+> JoinedModels<S, ($($i,)+)> for ($($t,)+)
323        where
324            $($t: JoinedModels<S, $i>,)+
325        {
326            type Output = ($($t::Output,)+);
327            fn write_columns(sql: &mut Sql) {
328                $($t::write_columns(sql);)+
329            }
330            fn decode(row: &PgRow, offset: &mut usize) -> Result<Self::Output> {
331                Ok(($($t::decode(row, offset)?,)+))
332            }
333        }
334    )+};
335}
336
337joined_model_tuples! {
338    (A IA, B IB),
339    (A IA, B IB, C IC),
340    (A IA, B IB, C IC, D ID),
341    (A IA, B IB, C IC, D ID, E IE),
342    (A IA, B IB, C IC, D ID, E IE, F IF),
343    (A IA, B IB, C IC, D ID, E IE, F IF, G IG),
344    (A IA, B IB, C IC, D ID, E IE, F IF, G IG, H IH),
345}
346
347/// A `SELECT` over root model `M` with joined models `J`, created with
348/// [`Select::join`] or [`Select::left_join`]. See the [module docs](self).
349pub struct Joined<M, J> {
350    select: Select<M>,
351    _joins: PhantomData<fn() -> J>,
352}
353
354impl<M: Model> Select<M> {
355    /// `INNER JOIN` a related model (see [`join`](crate::join)).
356    pub fn join<R, I>(self, join: R) -> Joined<M, ((), R::To)>
357    where
358        R: IntoJoin,
359        R::From: InScope<(M, ()), I>,
360    {
361        Joined::<M, ()>::root(self).push_join(join, false)
362    }
363
364    /// `LEFT JOIN` a related model: root rows without a match are kept,
365    /// with `NULL`s for the joined model's columns.
366    pub fn left_join<R, I>(self, join: R) -> Joined<M, ((), R::To)>
367    where
368        R: IntoJoin,
369        R::From: InScope<(M, ()), I>,
370    {
371        Joined::<M, ()>::root(self).push_join(join, true)
372    }
373}
374
375impl<M: Model, J> Joined<M, J> {
376    fn from_select<J2>(select: Select<M>) -> Joined<M, J2> {
377        Joined {
378            select,
379            _joins: PhantomData,
380        }
381    }
382
383    fn root(select: Select<M>) -> Joined<M, ()> {
384        Joined {
385            select,
386            _joins: PhantomData,
387        }
388    }
389
390    fn push_join<R: IntoJoin, J2>(mut self, join: R, left: bool) -> Joined<M, J2> {
391        self.select.joins.push(JoinClause {
392            left,
393            table: <R::To as Model>::TABLE,
394            on: join.condition().0,
395            scope: join_scope::<R::To>,
396            multiplies: join.multiplies(),
397        });
398        Joined::<M, J>::from_select(self.select)
399    }
400
401    /// `INNER JOIN` another model related to one already in the query.
402    pub fn join<R, I>(self, join: R) -> Joined<M, (J, R::To)>
403    where
404        R: IntoJoin,
405        R::From: InScope<(M, J), I>,
406    {
407        self.push_join(join, false)
408    }
409
410    /// `LEFT JOIN` another model related to one already in the query.
411    pub fn left_join<R, I>(self, join: R) -> Joined<M, (J, R::To)>
412    where
413        R: IntoJoin,
414        R::From: InScope<(M, J), I>,
415    {
416        self.push_join(join, true)
417    }
418
419    /// Add a `WHERE` condition on any model in the query.
420    pub fn filter<X, I>(mut self, expr: Expr<X>) -> Self
421    where
422        X: InScope<(M, J), I>,
423    {
424        self.select = self.select.filter(expr.retag());
425        self
426    }
427
428    /// Append an `ORDER BY` term on any model in the query.
429    pub fn order_by<X, I>(mut self, order: impl Into<Order<X>>) -> Self
430    where
431        X: InScope<(M, J), I>,
432    {
433        self.select = self.select.order_by(order.into().retag::<M>());
434        self
435    }
436
437    /// Append a `GROUP BY` column of any model in the query.
438    pub fn group_by<X, I>(mut self, column: Column<X>) -> Self
439    where
440        X: Model + InScope<(M, J), I>,
441    {
442        self.select.group_by.push((X::TABLE, column.name()));
443        self
444    }
445
446    /// Add a `HAVING` condition.
447    pub fn having<X, I>(mut self, expr: Expr<X>) -> Self
448    where
449        X: InScope<(M, J), I>,
450    {
451        self.select.having.push(expr.retag::<M>().cond);
452        self
453    }
454
455    /// Limit the number of returned rows.
456    pub fn limit(mut self, limit: u64) -> Self {
457        self.select = self.select.limit(limit);
458        self
459    }
460
461    /// Skip the first `offset` rows.
462    pub fn offset(mut self, offset: u64) -> Self {
463        self.select = self.select.offset(offset);
464        self
465    }
466
467    /// Include soft-deleted root rows.
468    pub fn with_trashed(mut self) -> Self {
469        self.select = self.select.with_trashed();
470        self
471    }
472
473    /// Run on the primary even when read replicas are configured.
474    pub fn on_primary(mut self) -> Self {
475        self.select = self.select.on_primary();
476        self
477    }
478
479    /// Select columns or aggregates from any model in the query, decoded
480    /// into tuples or a `FromRow` type with [`Projected::fetch_all`] & co.
481    pub fn select<P, I>(self, items: P) -> Projected<M>
482    where
483        P: ScopedProjections<(M, J), I>,
484    {
485        Projected {
486            select: self.select,
487            items: items.into_items(),
488        }
489    }
490
491    /// Turn into a bulk `UPDATE` of the matching root rows (`SET` root
492    /// columns only):
493    ///
494    /// ```ignore
495    /// Post::query().join(Post::AUTHOR).filter(User::BANNED.eq(true))
496    ///     .update().set(Post::HIDDEN, true).exec(&db).await?;
497    /// ```
498    ///
499    /// Rendered as `UPDATE posts SET … WHERE id IN (SELECT posts.id FROM
500    /// posts JOIN … WHERE …)`.
501    pub fn update(self) -> Update<M> {
502        self.select.key_subselect().update()
503    }
504
505    /// Delete the matching root rows (soft delete for soft-delete models).
506    pub async fn delete<'e, E: Executor<'e>>(self, executor: E) -> Result<u64> {
507        self.select.key_subselect().delete(executor).await
508    }
509
510    /// Permanently delete the matching root rows.
511    pub async fn force_delete<'e, E: Executor<'e>>(self, executor: E) -> Result<u64> {
512        self.select.key_subselect().force_delete(executor).await
513    }
514
515    /// Restore the matching soft-deleted root rows (combine with
516    /// [`with_trashed`](Self::with_trashed)).
517    pub async fn restore<'e, E: Executor<'e>>(self, executor: E) -> Result<u64> {
518        self.select.key_subselect().restore(executor).await
519    }
520
521    /// Cache this query's results (see [`Select::memoize`]); writes to the
522    /// root **or any joined table** invalidate them.
523    pub fn memoize(self, ttl: std::time::Duration) -> crate::Memoized<M> {
524        self.select.memoize(ttl)
525    }
526
527    #[doc(hidden)]
528    pub fn into_select(self) -> Select<M> {
529        self.select
530    }
531
532    /// Render the `SELECT` of root models.
533    pub fn to_sql(&self) -> Sql {
534        self.select.to_sql()
535    }
536
537    /// Fetch the matching root models (one per root row).
538    pub async fn all<'e, E: Executor<'e>>(self, executor: E) -> Result<Vec<M>> {
539        self.select.all(executor).await
540    }
541
542    /// Fetch the first matching root model.
543    pub async fn first<'e, E: Executor<'e>>(self, executor: E) -> Result<Option<M>> {
544        self.select.first(executor).await
545    }
546
547    /// Fetch the first matching root model, failing with `NotFound`.
548    pub async fn one<'e, E: Executor<'e>>(self, executor: E) -> Result<M> {
549        self.select.one(executor).await
550    }
551
552    /// Count matching root rows (distinct root keys for multiplying joins).
553    pub async fn count<'e, E: Executor<'e>>(self, executor: E) -> Result<i64> {
554        self.select.count(executor).await
555    }
556
557    /// `true` if anything matches.
558    pub async fn exists<'e, E: Executor<'e>>(self, executor: E) -> Result<bool> {
559        self.select.exists(executor).await
560    }
561
562    /// One page of root models with the total count, in one round trip.
563    pub async fn paginate<'e, E: Executor<'e>>(
564        self,
565        executor: E,
566        page: u64,
567        per_page: u64,
568    ) -> Result<Page<M>> {
569        self.select.paginate(executor, page, per_page).await
570    }
571
572    /// Fetch the root models together with joined ones (RFC 0004): one
573    /// tuple per joined row, so a `has_many` join repeats the root model.
574    ///
575    /// ```ignore
576    /// let rows: Vec<(Post, User)> = Post::query()
577    ///     .join(Post::AUTHOR)
578    ///     .all_with::<User, _>(&db)
579    ///     .await?;
580    /// let rows: Vec<(Post, (User, Option<Category>))> = Post::query()
581    ///     .join(Post::AUTHOR)
582    ///     .left_join(Category::ID.on(Post::CATEGORY_ID))
583    ///     .all_with::<(User, Option<Category>), _>(&db)
584    ///     .await?;
585    /// ```
586    pub async fn all_with<'e, T, I>(
587        self,
588        executor: impl Executor<'e>,
589    ) -> Result<Vec<(M, T::Output)>>
590    where
591        T: JoinedModels<(M, J), I>,
592    {
593        let sql = self.with_sql::<T, I>();
594        let rows = crate::exec::fetch_rows(executor, &sql, &[], self.select.use_replica()).await?;
595        rows.iter()
596            .map(|row| {
597                let root = M::from_row_at(row, 0)?;
598                let mut offset = M::COLUMNS.len();
599                Ok((root, T::decode(row, &mut offset)?))
600            })
601            .collect()
602    }
603
604    /// Render the statement of [`all_with`](Self::all_with).
605    pub fn with_sql<T, I>(&self) -> Sql
606    where
607        T: JoinedModels<(M, J), I>,
608    {
609        let mut sql = self.select.new_sql();
610        sql.push("SELECT ");
611        crate::model::push_columns::<M>(&mut sql);
612        T::write_columns(&mut sql);
613        self.select.write_tail(&mut sql);
614        sql
615    }
616
617    /// Keyset pagination (see [`Select::cursor_paginate`]); sort keys may
618    /// come from joined models, and must be `NOT NULL`.
619    pub async fn cursor_paginate<'e, E: Executor<'e>>(
620        self,
621        executor: E,
622        after: Option<&crate::Cursor>,
623        limit: u64,
624    ) -> Result<crate::CursorPage<M>> {
625        self.select.cursor_paginate(executor, after, limit).await
626    }
627
628    /// Stream the matching root models.
629    pub fn stream<'e, E: Executor<'e> + 'e>(self, executor: E) -> BoxStream<'e, Result<M>> {
630        self.select.stream(executor)
631    }
632}
633
634impl<M, J> Clone for Joined<M, J> {
635    fn clone(&self) -> Self {
636        Self {
637            select: self.select.clone(),
638            _joins: PhantomData,
639        }
640    }
641}
642
643impl<M: Model, J> fmt::Debug for Joined<M, J> {
644    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
645        f.debug_tuple("Joined").field(&self.to_sql()).finish()
646    }
647}