Map a custom struct while making join.

[uwejan]

create table firms_users(
  firm_id integer references firms(id),
  user_id integer references users(id),
  primary key (firm_id, user_id)
);

How would I join on this table note the intended result would be some of users attributes + some of firm attributes not the whole model.

pub struct UserWithFirm {
    pub(crate) user: SlimUser,
    pub(crate) firm: SlimFirm,
}

Thank you.

[kurtbuilds]

Great question.

Right now there's only support for many-to-1 joins, though support for other types is planned.

If I'm understanding right, you're asking about a many-to-many join. You can work around the limitation mentioned by operating on the many to many table, rather than the User table or the Firm table. It looks something like this:

#[derive(Model)]
pub struct UserWithFirm {
    id: Uuid,
    user_id: Uuid,
    #[ormlite(many_to_one_key = user_id)]
    pub(crate) user: Join<SlimUser>,
    firm_id: Uuid,
    #[ormlite(many_to_one_key = firm_id)]
    pub(crate) firm: Join<SlimFirm>,
}

Once you have that struct, you should be able to apply select filters on the joined objects. Something like:

let results = UserWithFirm::select()
.join(UserWithFirm::organization())
.join(UserWithFirm::user())
.where_bind("user.active = ?", true)
.fetch_all(&pool)
.await?;

for uf in results {
    println!("user: {:?}", uf.user);
    println!("firm: {:?}", uf.firm);
}

[uwejan]

Thank you for taking a look into it.

Will the result be Vec, I noticed the loop. Well the intended result is only UserWithFirm struct, no Vec, becasue each user belongs only to one firm. Thus user: will be some of users attributes, firm: will be some of firms attributes.

[kurtbuilds]

Loop is because of the fetch_all. For one, try something like:

let uf = UserWithFirm::select()
.join(UserWithFirm::organization())
.join(UserWithFirm::user())
.where_bind("user.id= ?", 100)
.fetch_one(&pool)
.await?;

println!("user: {:?}", uf.user);
println!("firm: {:?}", uf.firm);

[uwejan]

Waht about runtime, I notice only runtime-tokio-rustls , Is there any plans for actix?

[kurtbuilds]

Actix runs on tokio. The runtime-actix is deprecated.

See this thread: https://www.reddit.com/r/rust/comments/qc3wc2/sqlx_exist_a_problem_if_pick_runtimeactix_vs/

[uwejan]

I confirm it does not compile for me while using actix-web.

ormlite = { version = "0.12.5", features = ["runtime-tokio-rustls","default-postgres", "time", "chrono"] }
   Compiling ormlite-attr v0.12.5
error: only one of ['runtime-actix-native-tls', 'runtime-async-std-native-tls', 'runtime-tokio-native-tls', 'runtime-actix-rustls', 'runtime-async-std-rustls', 'runtime-tokio-rustls'] can be enabled
  --> /home/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-rt-0.6.3/src/lib.rs:23:1
   |
23 | / compile_error!(
24 | |     "only one of ['runtime-actix-native-tls', 'runtime-async-std-native-tls', \
25 | |      'runtime-tokio-native-tls', 'runtime-actix-rustls', 'runtime-async-std-rustls', \
26 | |      'runtime-tokio-rustls'] can be enabled"
27 | | );
   | |_^

error: could not compile `sqlx-rt` due to previous error

[kurtbuilds]

This issue seems to be resolved. If there's a separate issue about the runtime options, let's put that in its own issue.