时间戳

首页 > 解决方案 > 尝试为用户所属的群组中的社交帖子构建 SQL 或 Knex 查询

sql - 尝试为用户所属的群组中的社交帖子构建 SQL 或 Knex 查询

问题描述

我使用原始 sql、knex 和 objection.js 查询的组合查询了 4 个表。用户和组由属于组的成员资格和帖子相关联

主键/外键

用户：id、has_many 组通过会员资格组：id、has_many 用户通过会员资格：id、groups_id、users_id
帖子：id、users_id、groups_id

我正在尝试返回属于用户所属的所有组的所有帖子。

以下查询返回帖子，但未提供我需要的所有字段：

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}

具体来说，查询返回的是id为userId的current_user的头像，而不是返回与具体帖子相关的用户信息。

以下是对请求的响应：

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' },

所以在上面的数据中，这个请求来自usersix，他只属于第1组。正如预期的那样，所有帖子的groups_id = 1，但是所有的avatar_urls都是一样的，用户名也是如此。

最后，这里有一些 json 概述了帖子和用户之间的关系：

{
"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"
}
},

我试图弄清楚如何在连接表中获取相关字段。

标签： sqlpostgresqlexpressknex.jsobjection.js

解决方案

您的加入是问题所在：

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}

通过加入与 的users关系memberships，您实际上是在选择当前用户 - 与您使用WHERE子句过滤的用户相同。相反，您想加入users反对posts作者身份列：

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}

推荐阅读