根据另一个表中的多个行搜索表

Basically I have three MySQL tables:

基本上我有三个MySQL表:

Users - contains base information on users
Fields - describes additional fields for said users (e.g. location, dob etc.)
Data - Contains user data described via links to the fields table

用户 - 包含有关用户的基本信息字段 - 描述所述用户的其他字段(例如位置,dob等)。数据 - 包含通过字段表链接描述的用户数据

With the basic design as follows (the below is a stripped down version)

基本设计如下(下面是精简版)

Users:

 ID | username | password | email | registered_date

Fields

 ID | name | type

Data:

 ID | User_ID | Field_ID | value

what I want to do is search Users by the values for the fields they have, e.g. example fields might be:

我想要做的是通过他们拥有的字段的值搜索用户,例如示例字段可能是:

Full Name
Town/City
Postcode
etc.

全名城镇邮政编码等

I've got the following, which works when you're only wanting to search by one field:

我有以下内容,当您只想搜索一个字段时,它会起作用:

SELECT `users`.`ID`,
       `users`.`username`,
       `users`.`email`,
       `data`.`value`,
       `fields`.`name`

FROM `users`,
     `fields`,
     `data`

WHERE `data`.`Field_ID` = '2'
AND `data`.`value` LIKE 'london'
AND `users`.`ID` = `data`.`User_ID`
AND `data`.`Field_ID` = `fields`.`ID`

GROUP BY `users`.`ID`

But what about if you want to search for Multiple fields? e.g. say I want to search for Full Name "Joe Bloggs" With Town/City set to "London"? This is the real sticking point for me.

但是如果要搜索多个字段呢?例如说我要搜索全名“Joe Bloggs”,城镇/城市设置为“伦敦”?这对我来说是一个真正的问题。

Is something like this possible with MySQL?

这样的事情可能与MySQL有关吗?

3 个解决方案

#1

I'm going with the assumption that "searching multiple fields" is talking about the Entity-Attribute-Value structure.

我假设“搜索多个字段”正在讨论实体 - 属性 - 值结构。

In that case, I propose that the first step is to create a derived query - basically, we want to limit the "EAV data joined" to only include the records that have the values we are interested in finding. (I've altered some column names, but the same premise holds.)

在这种情况下,我建议第一步是创建一个派生查询 - 基本上,我们希望将“EAV数据加入”限制为仅包含具有我们感兴趣的值的记录。 (我改变了一些列名,但同样的前提也有。)

SELECT d.userId
FROM data d
JOIN fields f
  ON f.fieldId = d.fieldId
-- now that we establish data/field relation, filter rows
WHERE f.type = "location" AND d.value = "london"
   OR f.type = "job" AND d.value = "programmer"

This resulting rows are derived from the filtered EAV triplets that match our conditions. Only the userId is selected in this case (as it will be used to join against the user relation), but it is also possible to push fieldId/value/etc through.

这些结果行来自符合我们条件的过滤EAV三元组。在这种情况下只选择userId(因为它将用于连接用户关系),但也可以通过推送fieldId / value / etc。

Then we can use all of this as a derived query:

然后我们可以将所有这些用作派生查询:

SELECT * 
FROM users u
JOIN (
  -- look, just goes in here :)
  SELECT DISTINCT d.userId
  FROM data d
  JOIN fields f
    ON f.fieldId = d.fieldId
  WHERE f.type = "location" AND d.value = "london"
     OR f.type = "job" AND d.value = "programmer"
) AS e
ON e.userId = u.userId

Notes:

The query planner will figure all the RA stuff out peachy keen; don't worry about this "nesting" as there is no dependent subquery.

查询计划程序会将所有RA内容都计算出来,这些内容非常敏锐;不要担心这种“嵌套”,因为没有依赖子查询。

I avoid the use of implicit cross-joins as I feel they muddle most queries, this case being a particularly good example.

我避免使用隐式交叉连接,因为我觉得它们混淆了大多数查询,这种情况是一个特别好的例子。

I've "cheated" and added a DISTINCT to the derived query. This will ensure that at most one record will be joined/returned per user and avoids the use of GROUP BY.

我已经“欺骗”并在派生查询中添加了DISTINCT。这将确保每个用户最多只能加入/返回一条记录,并避免使用GROUP BY。

While the above gets "OR" semantics well (it's both easier and I may have misread the question), modifications are required to get "AND" semantics. Here are some ways that the derived query can be written to get such. (And at this point I must apologize to Tony - I forget that I've already done all the plumbing to generate such queries trivially in my environment.)

虽然上面得到了很好的“OR”语义(它更容易,我可能误解了这个问题),但需要修改才能获得“AND”语义。以下是一些可以编写派生查询的方法。 (此时我必须向Tony道歉 - 我忘了我已经完成了所有的管道工作,以便在我的环境中轻松地生成这些查询。)

Count the number of matches to ensure that all rows match. This will only work if each entity is unique per user. It also eliminates the need for DISTINCT to maintain correct multiplicity.

计算匹配数以确保所有行匹配。这仅在每个实体每个用户都是唯一的情况下才有效。它还消除了DISTINCT保持正确多样性的需要。

SELECT d.userId
FROM data d
JOIN fields f
  ON f.fieldId = d.fieldId
-- now that we establish data/field relation, filter rows
WHERE f.type = "location" AND d.value = "london"
   OR f.type = "job" AND d.value = "programmer"
GROUP BY d.userId
HAVING COUNT(*) = 2

Find the intersecting matches:

找到相交的匹配:

SELECT d.userId
FROM data d
JOIN fields f ON f.fieldId = d.fieldId  
WHERE f.type = "location" AND d.value = "london"
INTERSECT
SELECT d.userId
FROM data d
JOIN fields f ON f.fieldId = d.fieldId  
WHERE f.type = "job" AND d.value = "programmer"

Using JOINS (see Tony's answer).

使用JOINS(参见Tony的回答)。

SELECT d1.userId
FROM data d1
JOIN data d2   ON d2.userId = d1.userId
JOIN fields f1 ON f1.fieldId = d1.fieldId
JOIN fields f2 ON f2.fieldId = d2.fieldId
-- requires AND here across row
WHERE f1.type = "location" AND d1.value = "london"
  AND f2.type = "job"      AND d2.value = "programmer"

An inner JOIN itself provides conjunction semantics when applied outside of the condition. In this case I show "re-normalize" the data. This can also be written such that [sub-]selects appear in the select clause.

当在条件之外应用时,内部JOIN本身提供连接语义。在这种情况下,我显示“重新规范化”数据。这也可以写成使得[sub-]选择出现在select子句中。

SELECT userId
FROM (
  -- renormalize, many SO questions on this
  SELECT q1.userId, q1.value as location, q2.value as job
  FROM (SELECT d.userId, d.value
     FROM data d
     JOIN fields f ON f.fieldId = d.fieldId
     WHERE f.type = "location") AS q1
  JOIN (SELECT d.userId, d.value
     FROM data d
     JOIN fields f ON f.fieldId = d.fieldId
     WHERE f.type = "job") AS q2
  ON q1.userId = q2.userId
) AS q
WHERE location = "london"
  AND job = "programmer"

The above duplicity is relatively easy to generate via code and some databases (such as SQL Server) support CTEs which make writing such much simpler. YMMV.

通过代码可以相对容易地生成上述重复性,并且一些数据库(例如SQL Server)支持CTE,这使得编写更加简单。因人而异。

#2

If I understood you right, this is what you want:

如果我理解你,这就是你想要的:

FROM `users`,
     `fields`,
     `data` `location`
     `data` `name`

WHERE `location`.`Field_ID` = '2'
AND `location`.`value` LIKE 'london'
AND `location`.`Field_ID` = `fields`.`ID`
AND `name`.`Field_ID` = 'whathere? something for its name'
AND `name`.`value` LIKE 'london'
AND `name`.`Field_ID` = `fields`.`ID`
AND `users`.`ID` = `data`.`User_ID`

I'd prefer joins though

我更喜欢加入

#3

Well here you hit one of the downsides of the EAV you are using

那么你在这里遇到了你正在使用的EAV的一个缺点

SELECT u.ID, u.username,u.email, d1.value, f1.Name, d2.Value, f2.name
FROM `users` u,
inner join data d1 On d1.User_id = u.id
inner join data d2 On d2.User_id = u.id
inner join fields f1 on f1.id = d1.field_id
inner join fields f2 on f2.id = d2.field_id
WHERE d1.Field_id = '2' and d1.Value = 'london'
and d2.field_id = '??' and d2.value = 'Joe Bloggs' 
GROUP BY `users`.`ID`

Messy isn't it? Bet you can't wait to go for, four or five values. Or think about (Forename = Joe Or surname = Bloggs) and City = London...

凌乱不是吗?打赌你迫不及待想要四五个值。或者考虑(Forename = Joe或surname = Bloggs)和City = London ...

#1