lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Luis SepĂșlveda <l...@abroadwith.com>
Subject Solr incorrectly fetching elements that do not match conditions in where as all-null rows
Date Mon, 15 Aug 2016 13:27:21 GMT
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?

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message