Inner join, specify column

[Boscop]

Is it possible to specify the column to join on?

#[derive(Queryable, Identifiable, Insertable, new, Debug)]
#[primary_key(pub_id, sub_id)]
#[table_name="follows"]
pub struct Follow {
	pub pub_id: i64,
	pub sub_id: i64,
}

I want to find all followers of a user, the sql query would be:

select users.* from users join follows on id = sub_id where pub_id = $1

It would be useful to have a way to specify which column to join on since I want to join on different columns in different cases, e.g. when getting the followees:

select users.* from users join follows on id = pub_id where sub_id = $1

Also, sometimes there are other constraints than column equality in the join condition.

How can I express these two queries with diesel currently?

[sgrif]

We don't currently support representing a join to the same two tables in different ways. Ultimately the plan for this is to allow giving a table an explicit alias, which you can reference separately. This gets into one of the fundamental impedence mismatches we have where you specify a join between to tables by annotating a random struct, not the table itself (because the table is likely not part of your code).

Ultimately this is something that is not slated for 1.0, and very low on the priority list. What is on the priority list for 1.0 is to provide a better untyped raw SQL escape hatch for the "we don't support this" cases, so the answer to how to do this with Diesel will likely point you towards that API when added.

Also, sometimes there are other constraints than column equality in the join condition.

Yes, ultimately I want the join_to! macro to be more robust. Conditions other than equality aren't likely to be supported by the associations abstraction though.

While I do think this is something to support this eventually, since it's far past 1.0 and I don't have a concrete API in mind yet, I'm closing this as non-actionable.

[Boscop]

I understand.
So currently the only way to do it is like this?

let query = sql(&format!("select users.* from users join follows on id = sub_id where pub_id = {}", self.id));
query.get_results::<User>(&*database::connection().get().unwrap()).map_err(|e| e.into())

[sgrif]

For the time being, yes -- only one of the two joins can be represented by the query builder

[Boscop]

Ok, but I get an error:

error[E0282]: unable to infer enough type information about SE

(It underlines get_results.)
When I write it like sql::<User>(..), I get:

error[E0277]: the trait bound (i64, std::string::String, std::string::String, std::string::String, chrono::NaiveDateTime, chrono::NaiveDateTime, std::option::Option<i64>): diesel::types::FromSqlRow<models::user::User, _> is not satisfied

[sgrif]

You need to specify the SQL type of the query when using the sql function.

[Boscop]

@sgrif Isn't that what I'm doing with sql::<User>(..)?

let query = sql::<User>(&format!("select users.* from users join follows on id = sub_id where pub_id = {}", self.id));
query.get_results::<User>(&*database::connection().get().unwrap()).map_err(|e| e.into())

[sgrif]

User is not a SQL type. You probably want users::SqlType

[Boscop]

Thanks a lot, it works!