英文:
Combining 2 different queries in flexible search
问题
我们计划在我们的自定义索引器中创建一个删除操作。这个索引器中的文档包括多个类别和产品。当我们删除一个类别时，我们将其存储在一个名为SolrDeletedItem的新表中。对于产品，我们只将批准状态设置为未批准。我们希望将两个不相关的查询合并，以获取存储在SolrDeletedItem和Product表中的所有已删除项目。以下是这些查询。
- 从{SolrDeletedItem as sdi join ComposedType as ct on {sdi.deletedItemType} = {ct.pk}}中选择{deletedItemPK}，其中{ct.code} = 'Category'且{sdi.modifiedtime} >= ?lastIndexTime
- 从{Product as p join ArticleApprovalStatus as aas on {aas.pk} = {p.approvalStatus}}中选择{p.PK}作为pk，其中{aas.code} = 'unapproved'且{p.modifiedtime} >= ?lastIndexTime
英文:
We plan to create a delete operation in one of our custom indexers. A document in this indexer consists of several categories and product. When we delete a category, we store it in a new table called SolrDeletedItem. For products, we only set the approval status to unapproved. We want to combine 2 different queries without any relation to get all deleted items stored both in SolrDeletedItem and Product tables. Below are the queries.
- select {deletedItemPK} from {SolrDeletedItem as sdi join ComposedType as ct on {sdi.deletedItemType} = {ct.pk}}
where {ct.code} = 'Category' and {sdi.modifiedtime} >= ?lastIndexTime - SELECT {p.PK} AS pk FROM {Product AS p join ArticleApprovalStatus as aas on {aas.pk} = {p.approvalStatus}}
WHERE {aas.code} = 'unapproved' and {p.modifiedtime} >= ?lastIndexTime
答案1
得分: 0
SELECT uniontable.PK FROM
(
{{选择 {deletedItemPK} 作为 PK 从 {SolrDeletedItem as sdi join ComposedType as ct on {sdi.deletedItemType} = {ct.pk}} where {ct.code} = 'Category'}}
UNION ALL
{{选择 {p.PK} 作为 PK 从 {Product AS p join ArticleApprovalStatus as aas on {aas.pk} = {p.approvalStatus}} WHERE {aas.code} = 'unapproved'}}
) uniontable
英文:
SELECT uniontable.PK FROM
(
{{
select {deletedItemPK} as PK from {SolrDeletedItem as sdi join ComposedType as ct on {sdi.deletedItemType} = {ct.pk}} where {ct.code} = 'Category'
}}
UNION ALL
{{
SELECT {p.PK} AS PK FROM {Product AS p join ArticleApprovalStatus as aas on {aas.pk} = {p.approvalStatus}} WHERE {aas.code} = 'unapproved'
}}
) uniontable
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论