Hello,
Solr is trying to process non-existing child/nested entities. By
non-existing I mean that they exist in DB but should not be at Solr side
because they don't match the conditions in the query I use to fetch them.
I have the below solr data configuration. The relationship between tables
is complicated, but the point is that I need to fetch child/nested entities
and perform some calculations at query time. My problem is that some
products have onSite services that are not enabled. I would expect Solr
from ignoring those elements because of the conditions in the query. If I
turn debug on when importing, I can see that all fields are null. However,
Solr still tries to process them, which results in invalid SQL queries
because it replaces null fields with nothing.
<entity name="product" query="SELECT p.id AS productId,
CONCAT(unitPrice,',',currency) AS unitPriceCurr FROM products p LEFT JOIN
companies c ON c.id=p.companyId WHERE h.enabled=true">
<entity name="serviceTypes"
query="SELECT s.serviceType, sl.language FROM services s
LEFT JOIN serviceLanguages sl ON s.id=sl.serviceId WHERE
companyId=${product.companyId} AND s.enabled=true">
<field column="serviceType" name="services" />
<field column="language" name="languages" />
</entity>
<entity name="onSite"
query="SELECT s.id, s.enabled, ${product.unitPrice} +
(hourlyPrice * MIN(hours)) AS onSitePriceRaw, CONCAT(${product.unitPrice} +
(hourlyPrice * MIN(hours)), ',', '${product.currency}') AS onSitePrice FROM
services s LEFT JOIN serviceHourlyPrices shp ON s.id=shp.serviceId WHERE
companyId=${product.companyId} AND s.enabled=true AND s.serviceType='OS'">
<field column="onSitePrice" name="onSitePrice" />
<entity name="fullReducedOnSitePrice"
query="SELECT CONCAT(${onSite.onSitePriceRaw} * (1 -
percentage), ',', '${product.currency}') AS fullReducedOnSitePrice FROM
discounts WHERE companyId=${product.companyId} AND category='FULL'">
<field name="fullReducedOnSitePrice"
column="fullReducedOnSitePrice"/>
</entity>
<entity name="partialReducedOnSitePrice"
query="SELECT CONCAT(${onSite.onSitePriceRaw} * (1 -
percentage), ',', '${product.currency}') AS partialReducedOnSitePrice FROM
discounts WHERE companyId=${product.companyId} AND category='PARTIAL'">
<field name="partialReducedOnSitePrice"
column="partialReducedOnSitePrice"/>
</entity>
</entity>
</entity>
The problem seems to be related to the condition s.enabled=true in the
query, because are rows with enabled=false that are causing problems (Solr
interprets them as rows with all fields null). I get an invalid SQL query
SELECT CONCAT( * (1 - percentage), ',', 'USD') AS fullReducedOnSitePrice
FROM discounts WHERE companyId=65.
How can I force Solr to ignore, as it should, those elements?
