Search code examples
restwebsolrsolrcloudsolr4

Faceting in solr

Good day friends I am facing a problem as I require to match the following query in solr

SELECT entidad, municipio, count(*) as total, 
 sum(case when ip like '10.%' then 1 else 0 end) as internos, 
 sum(case when ip not like '10.%' then 1 else 0 end) as externos 
 FROM bitacora.actividad a 
 where sistema = 'RGNPMarcas' 
 AND fecha >= '2021/07/16' and fecha <= '2021/07/31' 
 AND parametros like 'PDF'
 and parametros like 'EGV'
 and entidad = '01'
 GROUP BY entidad, municipio 
 ORDER BY entidad, municipio

I have been researching on Faceting in solr, I have come to make some counts like the following, but the truth is that I have no idea how to mitigate this situation. enter image description here

My schema is the following:

<field name="tabla" type="string" indexed="true" stored="false" multiValued="false"/>
  <field name="entidad" type="string" indexed="true" stored="true" default="" multiValued="false"/>
  <field name="municipio" type="string" indexed="true" stored="true" default="" multiValued="false"/>
  <field name="ip" type="text_general" indexed="true" stored="true" default="" multiValued="false"/>
  <field name="fecha" type="pdate" indexed="true" stored="true" multiValued="false" default=""/>
  <field name="parametros" type="text_general" indexed="true" stored="true" default="" multiValued="false"/>
  <field name="total" type="pint" uninvertible="true" indexed="true" stored="true"/>
  <field name="internos" type="pint" uninvertible="true" indexed="true" stored="true"/>
  <field name="externos" type="pint" uninvertible="true" indexed="true" stored="true"/>

As you can see, I already have the base and the indexed fields, what I need is your experience on these issues of solr or that someone can guide me, please!

Solution

  • if you could have one field with the name "ip_type" and the content "Internos" or "Externos" depending on weather or not the ip starts with "10.", then your query will have as Filter Query all the conditions you wrote after "where" (for instance "sistema = 'RGNPMarcas'") and the facet pivot would have 3 fields: "entidad,municipio,ip_type".

    Now, in order to obtain the "ip_type" column, you can either change the way you index data (alter the input), or you can use a pattern replace char filter factory (https://solr.apache.org/guide/7_1/charfilterfactories.html#solr-patternreplacecharfilterfactory)