1-n 关系的 MySQL n 部分查询

Question

(几天前才开始学习 SQL，如果这是一个愚蠢的问题，请见谅!)

我有三个表，Users、Addresses 和 AddressCategories。每个用户有多个地址，但每个地址类别不超过 1 个地址。我想进行一个查询，根据每个 AddressCategory 的不同条件搜索用户。

表结构如下:

用户:

id
1
2

地址类别:

category
HomeAddress
WorkAddress

地址:

userId   category     address
1        HomeAddress  1 Washington Street
1        WorkAddress  53 Elm Avenue
2        HomeAddress  7 Bernard Street

假设我要搜索家庭地址包含单词“街道”且工作地址包含单词“大道”的所有用户。我可以使用查询:

SELECT * FROM Users 
INNER JOIN Addresses a1 ON Users.id=a1.userId
INNER JOIN Addresses a2 ON Users.id=a2.userId
WHERE a1.category='HomeAddress' AND a1.address LIKE '%Street%'
AND a2.category='WorkAddress' AND a2.address LIKE '%Avenue%'

如果我想查询任意数量的 AddressCategories，我可以使用上述相同的原则动态构建查询:

// dictionary of query parts
var q_parts = {HomeAddress: 'Street',
                WorkAddress: 'Avenue'
                ...}

// build the query string piece by piece
let q_str1="", q_str2="";
let i=0;
for (q in q_parts) {
    i++;
    q_str1 += "INNER JOIN Addresses a${i} ON Users.id=a${1}.userId ";
    q_str2 += (i==1) ? "WHERE " : "AND ";
    q_str2 += "a${i}.category='${q}' AND a${i}.address LIKE '%${q_parts[q]}%' ";
}

// complete query string
let q_str = "SELECT * FROM Users "+q_str1+q_str2;

我现在的做法是可行的，但是构建查询字符串很容易出错，并且随着类别数量的增加，最终的字符串很快就会变得庞大。似乎必须有更好的方法。在 MySQL 中执行此类查询的正确方法是什么？ (或者我组织表格的方式有问题吗？)

Answer 1

您可以使用这个来构建查询。
官网:https://knexjs.org/
Npm 链接:https://www.npmjs.com/package/knex

不必多次连接的示例 SQL。它没有经过测试，只是一个想法。您可以在 Where 子句 中使用 When/then 来逐个验证。最后，根据用户的总类别进行过滤(分组依据)。

SELECT *
FROM
Users Inner Join
  (SELECT userId,
          count(category) AS categoryCount
   WHERE address LIKE '%Street%' LIKE CASE
                                          WHEN category = 'HomeAddress' THEN '%Street%'
                                          WHEN category = 'WorkAddress' THEN '%Avenue%'
                                      END
   GROUP BY userId) a ON Users.id = a.userId 
WHERE categoryCount = ? -- inject your count of all categories here, maybe get from another query