flexible-search

Combining 2 different queries in flexible search


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.


Solution

  • 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