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 <l...@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?
