0

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
iamrooovic
  • 47
  • 6

1 Answers1

0

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

iamrooovic
  • 47
  • 6
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 18 '23 at 18:07