lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexandre Rafalovitch <arafa...@gmail.com>
Subject Re: Solr incorrectly fetching elements that do not match conditions in where as all-null rows
Date Mon, 15 Aug 2016 13:37:13 GMT
Solr (well DIH) just passes that query to the DB, so if you are
getting extra rows (not extra fields), than I would focus on the
database side of the situation.

Specifically, I would confirm from the database logs what the sent
query actually looks like.

Very specifically, in your very first entity, I see the condition
"h.enabled=true" where "h" does not match the table names in the FROM
statement. Perhaps, that's the problem?

Regards,
   Alex.
----
Newsletter and resources for Solr beginners and intermediates:
http://www.solr-start.com/


On 15 August 2016 at 23:27, Luis SepĂșlveda <luis@abroadwith.com> wrote:
> 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
View raw message