Azure搜索 - 嵌套SQL查询的复制结果 [英] Azure Search- replicating result of nested SQL query

问题描述

我有一个数据库，包括以下模式，描述了与多个顾问连接的个人之间的联系，这些顾问与多个组织有联系。

I have a database comprising of the following schema depicting the linkage between individuals who connect with multiple Advisors and these Advisors have affiliations with multiple organizations

Individuals--> Advisors (m:n relationship)
Advisors --> Enterprises (m:n relationship)

业务需要是对所有这些概念进行搜索并组织结果在AdvisorIds周围。作为示例，显示搜索结果可以如下

The business need is to enable search on all these concepts and organize results around AdvisorIds. As an example, display of a search result could be as follows

a) Advisor1-> connected to Individuals A,B,C; and linked to Enterprises X,Y

b) Advisor2-> connected to Individuals A, E; and linked to Enterprises M,X,Z

为此，我们在这些概念上创建了一个展平表，他们之间的关系。因此，相同的AdvisorId将显示在多行

Towards this, we created a flattened table on these concepts and the relationship between them. Hence the same AdvisorId would appear in multiple rows

当我搜索字符串时，我想确保AdvisorId周围的全部记录为不管个人记录的搜索分数如何，一起返回。

When I search for a string, I want to ensure that ALL records around an AdvisorId to be returned together irrespective of search score of the individual records.

一种方法可以是

 a) first run an Azure Search and get a result of AdvisorId, ordered by search score of each record. This will repeat Advisor Ids

b) take a distinct set of  AdvisorIds (across pages) via standard SQL

c) for each AdvisorId, pick all the related records via standard SQL

2个问题

这里（b）和（c）中的很多处理将在Azure之外进行，导致延迟。另外，如果我使用（a）的分页，我永远不会确定AdvisorId的数量，我在不同的操作之后结束了

Here a lot of processing in (b) and (c) will be done outside Azure leading to delays. Also, if I were to use pagination for (a), I am never sure of number of AdvisorId's, I end up with after the distinct operation

我想检查有一种方法可以在Azure中实现嵌套的搜索（a），（b）和（c）作为一个API调用

I wanted to check if there is a way to get the nested search implemented in Azure to do (a), (b) and (c) as a single API call

如果我要使用处理（a）和（b）一起的方面，我如何确保订购是根据一个方面的最佳搜索分数文件

If I were to use facets for handling (a) and (b) together, how do I ensure that the ordering is based on the best search-score document within a facet

推荐答案

除非您对数据进行不同的建模，否则无法在单个请求中实现所需的内容。而不是对个人顾问和顾问 - 企业关系进行非规范化，可能会有每个顾问有一个文件，并使用集合来存储有关个人和企业的信息。这可能或可能不适用于您，具体取决于您是否需要支持与Advisor相关的个人和企业的相关过滤。有一个白皮书这里，应该可以帮助您评估这种方法是否适用于您。

There isn't a way to achieve what you want in a single request unless you model your data differently. Instead of denormalizing the Individuals-Advisors and Advisors-Enterprises relationships, it may be possible instead to have one document per Advisor and use collections to store information about the related Individuals and Enterprises. This may or may not work for you depending on whether you need to supported correlated filtering on Individuals and Enterprises that are related to an Advisor. There is a whitepaper here that should help you evaluate whether this approach would work for you.

另一种选择可能是将Individuals，Advisors和Enterprises作为单独的索引，发出三个查询，并进行客户端连接。但是，这受到个人和企业查询中需要发送的Advisor ID数量的限制。 Azure Search具有限制在大小的过滤器可以使这不切实际，除非您的查询有低回调。

Another option might be to model Individuals, Advisors, and Enterprises as separate indexes, issue three queries, and do a client-side join. However, this is limited by the number of Advisor IDs you'd need to send in the queries on Individuals and Enterprises. Azure Search has limits on the size of filters that can make this impractical unless your queries have low recall.

我们正在努力使Azure搜索更好地适应您的场景。例如，我们正在努力添加对复杂类型。请投票支持用户声音，并随时提出有助于其他功能。

We are working on making Azure Search better for scenarios like yours. For example, we're currently working on adding support for complex types. Please vote on User Voice and feel free to suggest other features that would help.

这篇关于Azure搜索 - 嵌套SQL查询的复制结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！