solr delta import handling deletion of documents

Question

I have two tables joined by column "product_code".

1. product
2. product_retailer_map

My SQL query : SELECT products.*, COUNT(product_retailer_map.product_code) FROM products LEFT JOIN product_retailer_map ON product_retailer_map.product_code = products.product_code GROUP BY products.product_code

Whenever I add new retailer and execute delta import, solr updates document correctly. Whenever I add new product and execute delta import, solr updates document correctly.

BUT whenever I delete a retailer and execute delta import, solr do not update document. Also, whenever I delete a product and execute delta import, solr do not update document.

I have not yet configured deletedPkQuery since I am confused how to handle 2 separate deletes.

Edit

< entity name="products" 

query="SELECT p.*, COUNT(map.product_code) AS cnt, SUM(CASE WHEN map.retailer_availability > 0 THEN 1 ELSE 0 END) AS available_cnt FROM products AS p LEFT JOIN product_retailer_map AS map ON map.product_code = p.product_code GROUP BY p.product_code ORDER BY available_cnt DESC" 

deltaImportQuery="SELECT p.*, COUNT(map.product_code) AS cnt, SUM(CASE WHEN map.retailer_availability > 0 THEN 1 ELSE 0 END) AS available_cnt  FROM products AS p LEFT JOIN product_retailer_map AS map ON map.product_code = p.product_code WHERE p.product_id = '${dataimporter.delta.product_id}' GROUP BY p.product_code ORDER BY available_cnt DESC" 

deltaQuery="SELECT `product_id` FROM `products` WHERE ( product_id IN (SELECT product_id FROM products WHERE product_code IN (SELECT DISTINCT product_code FROM product_retailer_map WHERE updated_at > '${dataimporter.last_index_time}')) ) OR (updated_at > '${dataimporter.last_index_time}')"
>

            <field column="product_id" name="product_id"/>
            <field column="product_code" name="product_code"/>
            <field column="product_name" name="product_name"/>
            <field column="cnt" name="cnt"/>
            <field column="available_cnt" name="available_cnt"/>

        < / entity >

Please suggest if I am doing wrong here.

Also, I think if I use 2 entities then I can write 2 delete queries and may be issue will get addressed correctly.

What you think ??

Answer 1

When you delete a product, your delta query will not catch the delete since the product no longer exists in the table. You need to use a deletedPkQuery for this. You'll need to have a log table or similar in your database to select from, since you can no longer select the product's primary key from the products table. Something like this:

SELECT product_id
FROM LogProductDeletion
WHERE deleted_stamp > '${dataimporter.last_index_time}'

What needs to happen when you delete a retailer? Does that cause the product to be deleted from Solr? If so, you can use a UNION in your deletedPkQuery to get the primary keys of products whose retailers have been deleted. Again, you'll need to have access to a log table for retailer deletion, since you can no longer select the retailers from their table.