I'm looking for a pattern for performing a dynamic search on multiple tables.
我正在寻找一种在多个表上执行动态搜索的模式。
I have no control over the legacy (and poorly designed) database table structure.
我无法控制遗留(和设计不佳)的数据库表结构。
Consider a scenario similar to a resume search where a user may want to perform a search against any of the data in the resume and get back a list of resumes that match their search criteria. Any field can be searched at anytime and in combination with one or more other fields.
考虑类似于简历搜索的场景,其中用户可能想要对简历中的任何数据执行搜索并且返回与其搜索标准匹配的简历列表。可以随时搜索任何字段并与一个或多个其他字段组合搜索。
The actual sql query gets created dynamically depending on which fields are searched. Most solutions I've found involve complicated if blocks, but I can't help but think there must be a more elegant solution since this must be a solved problem by now.
实际的sql查询是根据搜索的字段动态创建的。我发现的大多数解决方案都涉及复杂的块,但我不禁认为必须有一个更优雅的解决方案,因为这一定是一个已解决的问题。
Yeah, so I've started down the path of dynamically building the sql in code. Seems godawful. If I really try to support the requested ability to query any combination of any field in any table this is going to be one MASSIVE set of if statements. shiver
是的,所以我开始在代码中动态构建sql的道路。似乎很神奇。如果我真的试图支持所请求的能力来查询任何表中任何字段的任何组合,那么这将是一组大量的if语句。发抖
I believe I read that COALESCE only works if your data does not contain NULLs. Is that correct? If so, no go, since I have NULL values all over the place.
我相信我读到COALESCE仅在您的数据不包含NULL时才有效。那是对的吗?如果是这样,不要去,因为我到处都有NULL值。
3 个解决方案
#1
5
As far as I understand (and I'm also someone who has written against a horrible legacy database), there is no such thing as dynamic WHERE clauses. It has NOT been solved.
据我所知(我也是一个反对可怕的遗留数据库的人),没有动态WHERE子句这样的东西。它还没有解决。
Personally, I prefer to generate my dynamic searches in code. Makes testing convenient. Note, when you create your sql queries in code, don't concatenate in user input. Use your @variables!
就个人而言,我更喜欢在代码中生成动态搜索。使测试更方便。请注意,在代码中创建SQL查询时,请勿在用户输入中进行连接。使用你的@variables!
The only alternative is to use the COALESCE operator. Let's say you have the following table:
唯一的选择是使用COALESCE运算符。假设你有下表:
Users
-----------
Name nvarchar(20)
Nickname nvarchar(10)
and you want to search optionally for name or nickname. The following query will do this:
并且您希望可选地搜索名称或昵称。以下查询将执行此操作:
SELECT Name, Nickname
FROM Users
WHERE
Name = COALESCE(@name, Name) AND
Nickname = COALESCE(@nick, Nickname)
If you don't want to search for something, just pass in a null. For example, passing in "brian" for @name and null for @nick results in the following query being evaluated:
如果您不想搜索某些内容,只需传入null。例如,为@name传递“brian”和为@nick传入null会导致以下查询被评估:
SELECT Name, Nickname
FROM Users
WHERE
Name = 'brian' AND
Nickname = Nickname
The coalesce operator turns the null into an identity evaluation, which is always true and doesn't affect the where clause.
coalesce运算符将null转换为标识评估,该标识始终为true且不影响where子句。
#2
1
Search and normalization can be at odds with each other. So probably first thing would be to get some kind of "view" that shows all the fields that can be searched as a single row with a single key getting you the resume. then you can throw something like Lucene in front of that to give you a full text index of those rows, the way that works is, you ask it for "x" in this view and it returns to you the key. Its a great solution and come recommended by joel himself on the podcast within the first 2 months IIRC.
搜索和规范化可能相互矛盾。因此,首先可能是获得某种“视图”,该视图显示所有可以作为单行搜索的字段,只需一个键即可获得简历。然后你可以在它前面抛出像Lucene这样的东西,为你提供这些行的全文索引,工作方式是,在这个视图中你要求它为“x”并返回给你关键。这是一个很好的解决方案,并且在前2个月的IIRC中由joel亲自推荐播客。
#3
1
What you need is something like SphinxSearch (for MySQL) or Apache Lucene.
你需要的是像SphinxSearch(适用于MySQL)或Apache Lucene。
As you said in your example lets imagine a Resume that will composed of several fields:
正如你在你的例子中所说,让我们想象一个由几个字段组成的简历:
- List item
- Name,
- Adreess,
- Education (this could be a table on its own) or
- Work experience (this could grow to its own table where each row represents a previous job)
教育(这可能是一个独立的表)或
工作经验(这可能会增长到自己的表,其中每行代表以前的工作)
So searching for a word in all those fields with WHERE rapidly becomes a very long query with several JOINS.
因此,使用WHERE快速搜索所有这些字段中的单词会变成一个带有多个JOINS的非常长的查询。
Instead you could change your framework of reference and think of the Whole resume as what it is a Single Document and you just want to search said document.
相反,您可以更改您的引用框架,并将整个简历视为单个文档,您只想搜索所述文档。
This is where tools like Sphinx Search do. They create a FULL TEXT index of your 'document' and then you can query sphinx and it will give you back where in the Database that record was found.
这是像Sphinx Search这样的工具。他们为您的'文档'创建一个FULL TEXT索引,然后您可以查询sphinx,它会返回数据库中找到记录的位置。
Really good search results.
搜索结果非常好。
Don't worry about this tools not being part of your RDBMS it will save you a lot of headaches to use the appropriate model "Documents" vs the incorrect one "TABLES" for this application.
不要担心这些工具不属于您的RDBMS,它将为您节省很多麻烦,使用适当的模型“文档”与此应用程序的错误“TABLES”。
#1
5
As far as I understand (and I'm also someone who has written against a horrible legacy database), there is no such thing as dynamic WHERE clauses. It has NOT been solved.
据我所知(我也是一个反对可怕的遗留数据库的人),没有动态WHERE子句这样的东西。它还没有解决。
Personally, I prefer to generate my dynamic searches in code. Makes testing convenient. Note, when you create your sql queries in code, don't concatenate in user input. Use your @variables!
就个人而言,我更喜欢在代码中生成动态搜索。使测试更方便。请注意,在代码中创建SQL查询时,请勿在用户输入中进行连接。使用你的@variables!
The only alternative is to use the COALESCE operator. Let's say you have the following table:
唯一的选择是使用COALESCE运算符。假设你有下表:
Users
-----------
Name nvarchar(20)
Nickname nvarchar(10)
and you want to search optionally for name or nickname. The following query will do this:
并且您希望可选地搜索名称或昵称。以下查询将执行此操作:
SELECT Name, Nickname
FROM Users
WHERE
Name = COALESCE(@name, Name) AND
Nickname = COALESCE(@nick, Nickname)
If you don't want to search for something, just pass in a null. For example, passing in "brian" for @name and null for @nick results in the following query being evaluated:
如果您不想搜索某些内容,只需传入null。例如,为@name传递“brian”和为@nick传入null会导致以下查询被评估:
SELECT Name, Nickname
FROM Users
WHERE
Name = 'brian' AND
Nickname = Nickname
The coalesce operator turns the null into an identity evaluation, which is always true and doesn't affect the where clause.
coalesce运算符将null转换为标识评估,该标识始终为true且不影响where子句。
#2
1
Search and normalization can be at odds with each other. So probably first thing would be to get some kind of "view" that shows all the fields that can be searched as a single row with a single key getting you the resume. then you can throw something like Lucene in front of that to give you a full text index of those rows, the way that works is, you ask it for "x" in this view and it returns to you the key. Its a great solution and come recommended by joel himself on the podcast within the first 2 months IIRC.
搜索和规范化可能相互矛盾。因此,首先可能是获得某种“视图”,该视图显示所有可以作为单行搜索的字段,只需一个键即可获得简历。然后你可以在它前面抛出像Lucene这样的东西,为你提供这些行的全文索引,工作方式是,在这个视图中你要求它为“x”并返回给你关键。这是一个很好的解决方案,并且在前2个月的IIRC中由joel亲自推荐播客。
#3
1
What you need is something like SphinxSearch (for MySQL) or Apache Lucene.
你需要的是像SphinxSearch(适用于MySQL)或Apache Lucene。
As you said in your example lets imagine a Resume that will composed of several fields:
正如你在你的例子中所说,让我们想象一个由几个字段组成的简历:
- List item
- Name,
- Adreess,
- Education (this could be a table on its own) or
- Work experience (this could grow to its own table where each row represents a previous job)
教育(这可能是一个独立的表)或
工作经验(这可能会增长到自己的表,其中每行代表以前的工作)
So searching for a word in all those fields with WHERE rapidly becomes a very long query with several JOINS.
因此,使用WHERE快速搜索所有这些字段中的单词会变成一个带有多个JOINS的非常长的查询。
Instead you could change your framework of reference and think of the Whole resume as what it is a Single Document and you just want to search said document.
相反,您可以更改您的引用框架,并将整个简历视为单个文档,您只想搜索所述文档。
This is where tools like Sphinx Search do. They create a FULL TEXT index of your 'document' and then you can query sphinx and it will give you back where in the Database that record was found.
这是像Sphinx Search这样的工具。他们为您的'文档'创建一个FULL TEXT索引,然后您可以查询sphinx,它会返回数据库中找到记录的位置。
Really good search results.
搜索结果非常好。
Don't worry about this tools not being part of your RDBMS it will save you a lot of headaches to use the appropriate model "Documents" vs the incorrect one "TABLES" for this application.
不要担心这些工具不属于您的RDBMS,它将为您节省很多麻烦,使用适当的模型“文档”与此应用程序的错误“TABLES”。