Joint pain

[Boscop]

I want to express this query efficiently with diesel:

select count(*) from likes inner join posts on posts.id = likes.post_id where posts.user_id = $1;

I tried:

likes::table.inner_join(posts::table).filter(user_id.eq(uid)).count().get_result(conn)

But I get:

error: no method named filter found for type diesel::query_source::InnerJoinSource<models::schema::__diesel_infer_schema::infer_likes::likes::table, models::schema::__diesel_infer_schema::infer_posts::posts::table> in the current scope

Then I tried this:

let data: Vec<(Like, Post)> = likes::table.inner_join(posts::table).load(conn)?;

But I get:

error: no method named load found for type diesel::query_source::InnerJoinSource<models::schema::__diesel_infer_schema::infer_likes::likes::table, models::schema::__diesel_infer_schema::infer_posts::posts::table> in the current scope

Even though the doc has this example:

let data: Vec<(User, Post)> = users::table.inner_join(posts::table).load(&connection);

What is the correct way to express the above SQL query in diesel?

[golddranks]

You have to derive the trait Associations for the structs corresponding to the joined tables. You have to also annotate the structs using belongs_to and has_many attributes. Without those annotations the tables won't implement the JoinSource traits.

[Boscop]

@golddranks I have:

#[derive(Queryable, new, Debug, Associations)]
#[belongs_to(User)]
#[belongs_to(Post)]
#[derive(Insertable)]
#[table_name="likes"]
pub struct Like {
	pub user_id: i64,
	pub post_id: i64,
}
...
#[derive(Queryable, Identifiable, Clone, Associations, Debug)]
#[belongs_to(User)]
// #[belongs_to(Post, foreign_key="parent")]
pub struct Post {
	pub id: i64,
	pub user_id: i64,
	pub parent: Option<i64>,
	...
}

If I add #[has_many(Like)] to Post, I get:

error[E0223]: ambiguous associated type
    |
130 | #[derive(Queryable, Identifiable, Clone, Associations, Debug)]
    | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ambiguous associated type
    |
    = note: specify the type using the syntax `<models::like::Like as Trait>::table`
    = note: this error originates in a macro outside of the current crate

What now?

[golddranks]

I'm not 100% sure about the problem because the error message isn't too descriptive, but at least has_many annotation should have the name of the DB table, not the name of the struct. (belongs_to has the name of the struct, as you currently have.) I don't know why, and I know it's annoying that they are asymmetric in that way, but try fixing that. (@sgrif and @killercup: is there any intuitive reason that helps us to be convinced like "of course it should be that way"?)

[Boscop]

@golddranks Thanks, I changed it to #[has_many(likes)], but now I get:

error[E0308]: mismatched types
    |
194 | 		likes::table.inner_join(posts::table).filter(user_id.eq(uid)).count()
    | 		                        ^^^^^^^^^^^^ expected struct `models::schema::__diesel_infer_schema::infer_users::users::table`, found struct `models::schema::__diesel_infer_schema::infer_posts::posts::table`
    |
    = note: expected type `models::schema::__diesel_infer_schema::infer_users::users::table`
               found type `models::schema::__diesel_infer_schema::infer_posts::posts::table`

[golddranks]

You might want to add foreign_key annotations too. The table likes having no own id field may cause it to be confused about which fields to use.

Also, the error message sounds like it's infering the types wrong. Is there anything external that might make it expect users instead of posts? What is the type of user_id in the filter clause?

[Boscop]

Ah, nevermind, I had changed this:

#[belongs_to(User)]
#[belongs_to(Post)]

to this:

#[belongs_to(User, Post)]

because I assumed it would work, just like:

#[derive(Queryable, Insertable, new, Debug, Associations)]

Now that I changed it back to the former version, it compiles, yay!
Thanks!

[killercup]

I'm only reading this via email. The problem was adding the belongs_to attribute twice? Does the documentation mention this? (If not, PR welcome!)

…

Am 07.02.2017 um 12:21 schrieb Boscop ***@***.***>: Ah, nevermind, I had changed this: #[belongs_to(User)] #[belongs_to(Post)] to this: #[belongs_to(User, Post)] because I assumed it would work, just like: #[derive(Queryable, Insertable, new, Debug, Associations)] Now that I changed it back to the former version, it compiles, yay! Thanks! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[Boscop]

@killercup The issue (at the end) was that I thought one could specify multiple structs that a struct belongs to in one #[belongs_to()] attribute, because it works with #[derive()]..

Btw, now this works:

pub fn get_likes_count(&self) -> Result<i64, error::MyError> {
	use diesel::prelude::*;
	use models::schema::likes;
	use models::schema::posts;
	use models::schema::posts::dsl::*;

	likes::table.inner_join(posts::table).filter(user_id.eq(self.id)).count()
		.get_result(&*database::connection().get().unwrap()).map_err(|e| e.into())
}

But this doesn't:

pub fn get_liked_posts(&self) -> Result<Vec<models::post::Post>, error::MyError> {
	use diesel::prelude::*;
	use models::schema::likes;
	use models::schema::posts;
	use models::schema::posts::dsl::*;

	likes::table.inner_join(posts::table).filter(user_id.eq(self.id))
		.get_results(&*database::connection().get().unwrap()).map_err(|e| e.into())
}

the trait diesel::types::FromSqlRow<((diesel::types::BigInt, diesel::types::BigInt), (diesel::types::BigInt, diesel::types::BigInt, diesel::types::Timestamp, diesel::types::Timestamp, diesel::types::Nullable<diesel::types::BigInt>, diesel::types::Text, diesel::types::Text, diesel::types::Nullable<diesel::types::Timestamp>, diesel::types::Integer, diesel::types::Nullable<diesel::types::BigInt>, diesel::types::Nullable<diesel::types::BigInt>, diesel::types::Nullable<diesel::types::BigInt>)), diesel::pg::Pg> is not implemented for (i64, i64, chrono::NaiveDateTime, chrono::NaiveDateTime, std::option::Option<i64>, std::string::String, std::string::String, std::option::Option<chrono::NaiveDateTime>, i32, std::option::Option<i64>, std::option::Option<i64>, std::option::Option<i64>)

Because the output has the columns of likes and posts, but it should only have the columns of posts.
What's the correct way to do this?

[Boscop]

I also tried splitting it up into two queries like this:

pub fn get_liked_posts(&self) -> Result<Vec<models::post::Post>, error::MyError> {
	use diesel::prelude::*;
	use models::schema::likes;
	use models::schema::posts;
	use models::schema::posts::dsl::*;

	let conn = &*database::connection().get().unwrap();
	let likes = models::like::Like::belonging_to(self).load(conn)?;
	models::post::Post::belonging_to(&likes).order(created_at.desc()).load(conn)?
}

But I get:

error: no associated item named belonging_to found for type models::like::Like in the current scope
note: the method belonging_to exists but the following trait bounds were not satisfied: models::like::Like : diesel::associations::HasTable, models::like::Like : diesel::associations::HasTable, models::like::Like : diesel::associations::HasTable, models::like::Like : diesel::associations::HasTable, models::like::Like : diesel::BelongingToDsl<&[_]>, ......

But I have:

#[derive(Queryable, Insertable, Associations, new, Debug)]
#[belongs_to(User)]
#[belongs_to(Post)]
#[table_name="likes"]
pub struct Like {
	pub user_id: i64,
	pub post_id: i64,
}

#[derive(Queryable, Identifiable, Associations, Debug, Clone)]
#[has_many(likes)]
pub struct User {
	pub id: i64,
	...
}

So why doesn't Like have the method belonging_to()?

[sgrif]

You need to add #[derive(Identifiable)] to Like, which will also require adding #[primary_key(user_id, post_id)]

[Boscop]

@sgrif Thanks!
I did that now:

#[derive(Queryable, Identifiable, Insertable, Associations, new, Debug)]
#[belongs_to(User)]
#[belongs_to(Post)]
#[primary_key(user_id, post_id)]
#[table_name="likes"]
pub struct Like {
    ....
}

#[derive(Queryable, Identifiable, Associations, Clone, Debug)]
#[belongs_to(User)]
#[has_many(likes)]
pub struct Post {
     ...
}

but I still get errors:

pub fn get_liked_posts(&self) -> Result<Vec<models::post::Post>, error::MyError> {
	use diesel::prelude::*;
	use models::schema::likes;
	use models::schema::posts;
	use models::schema::posts::dsl::*;
	use models::like::Like;
	use models::post::Post;

	let conn = &*database::connection().get().unwrap();
	let likes: Vec<Like> = Like::belonging_to(self).load(conn).map_err(|e| e.into())?;
	Post::belonging_to(&likes).order(created_at.desc()).load(conn).map_err(|e| e.into())
}

error[E0277]: the trait bound models::post::Post: diesel::BelongingToDsl<&std::vec::Vec<models::like::Like>> is not satisfied

This version also doesn't work; how can I get rid of the columns of likes in the join result?

pub fn get_liked_posts(&self) -> Result<Vec<models::post::Post>, error::MyError> {
	use diesel::prelude::*;
	use models::schema::likes;
	use models::schema::posts;
	use models::schema::posts::dsl::*;
	use models::like::Like;
	use models::post::Post;

	let conn = &*database::connection().get().unwrap();
	likes::table.inner_join(posts::table).filter(user_id.eq(self.id))
		.get_results(conn).map_err(|e| e.into())
}

error[E0277]: the trait bound (i64, i64, chrono::NaiveDateTime, chrono::NaiveDateTime, std::option::Option<i64>, std::string::String, std::string::String, std::option::Option<chrono::NaiveDateTime>, i32, std::option::Option<i64>, std::option::Option<i64>, std::option::Option<i64>): diesel::types::FromSqlRow<((diesel::types::BigInt, diesel::types::BigInt), (diesel::types::BigInt, diesel::types::BigInt, diesel::types::Timestamp, diesel::types::Timestamp, diesel::types::Nullable<diesel::types::BigInt>, diesel::types::Text, diesel::types::Text, diesel::types::Nullable<diesel::types::Timestamp>, diesel::types::Integer, diesel::types::Nullable<diesel::types::BigInt>, diesel::types::Nullable<diesel::types::BigInt>, diesel::types::Nullable<diesel::types::BigInt>)), diesel::pg::Pg> is not satisfied

[Boscop]

This seems to work:

pub fn get_liked_posts(&self) -> Result<Vec<models::post::Post>, error::MyError> {
	use diesel::prelude::*;
	use models::schema::likes;
	use models::schema::likes::dsl::user_id;
	use models::schema::posts;
	use models::schema::posts::dsl::created_at;
	use models::like::Like;

	let conn = &*database::connection().get().unwrap();
	Ok(posts::table.inner_join(likes::table).filter(user_id.eq(self.id)).order(created_at.desc()).get_results(conn)?.into_iter().unzip::<_, _, _, Vec<Like>>().0)
}

But with get_results() I'm getting a Vec<(Post, Like)>, but I only need the Vec so I unzip it and throw away the Vec, which is wasteful..
Is there a better way to get all the posts that this user likes?

[Boscop]

Ah, I found a way that works:

pub fn get_liked_posts(&self) -> Result<Vec<models::post::Post>, error::MyError> {
	use diesel::prelude::*;
	use models::schema::likes;
	use models::schema::likes::dsl::user_id;
	use models::schema::posts;
	use models::schema::posts::dsl::created_at;

	let conn = &*database::connection().get().unwrap();
	Ok(posts::table.inner_join(likes::table).filter(user_id.eq(self.id))
		.select(posts::all_columns).order(created_at.desc()).get_results(conn)?)
}

Is this the best way to do it?

[sgrif]

Yes, that looks fine to me.

[Boscop]

Thanks!

[mckinley-olsen-oc]

I ran into the issue in the first post, where diesel::query_source::InnerJoinSource has no method named load

My issue was that I had not specified the #[belongs_to] annotation with the name of the column referenced in the foreign key; I had only done this in the #[has_many] annotation.
On closer read of the documentation, it does mention the ability to specify this on #[has_many] as well as #[belongs_to]; in my oversight, I hadn't realized both were required, as the example in the documentation is only on the #[has_many]