Trying to structure a SQL or Knex query for social posts from groups where user is a member

Question

I have 4 tables queried using a combination of raw sql, knex and objection.js queries. Users and Groups are associated by Memberships and Posts which belong to Groups

Primary/Foreign Keys

Users: id, has_many Groups via Memberships Groups: id, has_many Users via Memberships Memberships: id, groups_id, users_id
Posts: id, users_id, groups_id

I am attempting to return all of the Posts that Belong to all of the Groups that a User is a Member of.

The following query, returns the posts, but does not provide all of the fields I need:

SELECT users.username, users.avatar_url, posts.id, posts.body, posts.users_id, posts.groups_id FROM posts INNER JOIN memberships ON posts.groups_id = memberships.groups_id JOIN users on memberships.users_id = users.id WHERE memberships.users_id = ${userId}

Specifically, the query returns the avatar for the current_user with id: userId, instead of returning the user information related to the specific post.

Here is the response to the request:

Result {
  command: 'SELECT',
  rowCount: 9,
  oid: null,
  rows:
   [ { username: 'usersix',
       avatar_url: 'https://image.flaticon.com/icons/svg/145/145864.svg',
       id: 1,
       body: 'user 1 comments to group 1',
       users_id: 1,
       groups_id: 1 },
     { username: 'usersix',
       avatar_url: 'https://image.flaticon.com/icons/svg/145/145864.svg',
       id: 3,
       body: 'user 1 comments to group 1 #2',
       users_id: 1,
       groups_id: 1 },
     { username: 'usersix',
       avatar_url: 'https://image.flaticon.com/icons/svg/145/145864.svg',
       id: 4,
       body: 'user 2 comments to group 1',
       users_id: 2,
       groups_id: 1 },
     { username: 'usersix',
       avatar_url: 'https://image.flaticon.com/icons/svg/145/145864.svg',
       id: 6,
       body: 'user 3 comments to group 1',
       users_id: 3,
       groups_id: 1 },
     { username: 'usersix',
       avatar_url: 'https://image.flaticon.com/icons/svg/145/145864.svg',
       id: 10,
       body: 'user 5 comments to group 1',
       users_id: 5,
       groups_id: 1 },
     { username: 'usersix',
       avatar_url: 'https://image.flaticon.com/icons/svg/145/145864.svg',
       id: 12,
       body: 'user 6 comments to group 1',
       users_id: 6,
       groups_id: 1 },
     { username: 'usersix',
       avatar_url: 'https://image.flaticon.com/icons/svg/145/145864.svg',
       id: 16,
       body: 'user 8 comments to group 1',
       users_id: 8,
       groups_id: 1 },
     { username: 'usersix',
       avatar_url: 'https://image.flaticon.com/icons/svg/145/145864.svg',
       id: 18,
       body: 'test\n',
       users_id: 8,
       groups_id: 1 },
     { username: 'usersix',
       avatar_url: 'https://image.flaticon.com/icons/svg/145/145864.svg',
       id: 19,
       body: 'another test',
       users_id: 8,
       groups_id: 1 } ],
  fields:
   [ Field {
       name: 'username',
       tableID: 31280,
       columnID: 2,
       dataTypeID: 1043,
       dataTypeSize: -1,
       dataTypeModifier: 259,
       format: 'text' },

So in the data above, this request is from usersix, who belongs only to group 1. As expected, all the posts have groups_id =1, however all of the avatar_urls are the same, as is username.

Lastly, here is some json outlining the relationship between posts and users:

{
"id": 1,
"body": "user 1 comments to group 1",
"users_id": 1,
"created_at": null,
"updated_at": null,
"groups_id": 1,
"user": {
"id": 1,
"username": "userone",
"email": "userone@gmail.com",
"password": "$2a$10$tiTQXusc4msyM0/73YfZAensmiCEI3FrTjP/JLS9nRctnut9x7od.",
"created_at": null,
"updated_at": null,
"avatar_url": "https://image.flaticon.com/icons/svg/145/145867.svg",
"bio": "Lorem ipsum dolor amet vexillologist bitters pickled 90's put a bird on it mlkshk street art squid lyft butcher prism. Kombucha tousled chicharrones keytar semiotics flexitarian, pinterest copper mug asymmetrical VHS. Chartreuse vaporware copper mug raclette, PBR&B pinterest keytar farm-to-table bicycle rights pitchfork poke kogi. Cornhole flexitarian deep v, post-ironic art party artisan edison bulb etsy. Schlitz VHS shabby chic lomo.",
"location": "New York, NY"
}
},

I am trying to figure out how to get the associated fields in the join table.

Answer 1

Your joins are the problem:

SELECT
  users.username, users.avatar_url, posts.id, posts.body, posts.users_id, posts.groups_id
FROM
  posts
  INNER JOIN memberships ON posts.groups_id = memberships.groups_id
  JOIN users ON memberships.users_id = users.id
WHERE memberships.users_id = ${userId}

By joining the users relation against the memberships, you essentially are selecting the current user - the same one that you filter for with the WHERE clause. Instead, you want to join the users against the posts by the authorship column:

SELECT
  users.username, users.avatar_url, posts.id, posts.body, posts.users_id, posts.groups_id
FROM
  posts
  INNER JOIN memberships ON posts.groups_id = memberships.groups_id
  JOIN users ON posts.users_id = users.id
--              ^^^^^
WHERE memberships.users_id = ${userId}