Common Table Expression in Blazebit

[RaulPampliegaMayoral]

Hi, we were using Solr to store some information and we were using the faceted information returned in the queries. We have moved that persistence to database and now we need to get something similar.
I was thinking about a CTE query like the following:

with data as (
 select * from table where filter_1='filter' and filter_2='filter'
)
select 'type_1' as facet,
       column1 as value,
       count(*) as count
from data
group by column1
union
select 'type_2' as facet,
       column2 as value,
       count(*) as count
from data
group by column2
........

But I can't find any example to make it with Blazebit. We have already made queries with recursive CTEs successfully.

If this is not possible, I had thought of making several queries with their respective repeated filters, but I prefer to avoid it since I consider that this way is more optimal.

Thanks in advance

[beikov]

Here you can find the documentation for working with CTEs: https://persistence.blazebit.com/documentation/1.6/core/manual/en_US/#ctes

Not sure what you are trying to achieve exactly, but maybe it's easier to create a CTE for the facets and join + aggregate the data against that. Something like select fd.type, case when fd.type = 'type_1' then column1 else column2 end, count(*) from facet_data fd join data d on 1=1 group by fd.type, case when fd.type = 'type_1' then column1 else column2 end.

[RaulPampliegaMayoral]

Hi @beikov , thanks for the answer!
The problem was that Blazebit did not generate correctly the query, at least the one I expected with 'with data...', it generated one with several joins.
I'm going to give a try to the solution you have proposed, to see if I get it.

Thanks and best regards!

[RaulPampliegaMayoral]

Hello again @beikov !
A query with the following format also gives me the data I need and looking at the execution plan I think it is performance as well.

select facet, value, count
from (
 select 'facet1' as facet, column1 as value, count(*) as count from table
 where filter_1='filter' and filter_2='filter'
 group by column1
 union
 select 'facet2' as facet, column2 as value, count(*) as count from table
 where filter_1='filter' and filter_2='filter'
 group by column2
 union
 select 'facet3' as facet, user_name  as value, count(*) as count from minerva.audit_log auditlog0_ 
 where filter_1='filter' and filter_2='filter' 
 group by column3
 union
 select 'facet4' as facet, action  as value, count(*) as count from minerva.audit_log auditlog0_ 
 where filter_1='filter' and filter_2='filter' 
 group by column4
) as data
order by facet

This is the query plan (with 100k elements)

versus the query plan with the 'with data query

[beikov]

Query plans look roughly the same to me, so yeah, should be fine!

[RaulPampliegaMayoral]

Hi again @beikov! and sorry to bother you!

Reading the documentation about CTE and trying the example with a query like this

    CriteriaBuilder<AuditLogCte> cb = cbf.create(entityManager, AuditLogCte.class)
            .with(AuditLogCte.class)
            .from(AuditLog.class, "al")
            .bind("id").select("al.id")
            .bind("objectType").select("al.objectType")
            .bind("objectSubType").select("al.objectSubType")
            .bind("objectName").select("al.objectName")
            .end();

is generating the following query (what getQueryString method shows):

SELECT auditLogCte FROM AuditLogCte(SELECT al.id, al.objectType, al.objectSubType, al.objectName FROM AuditLog al) 
auditLogCte(id, objectType, objectSubType, objectName)

And this is the query that is sent to the database (postgresql)

select auditlogct0_.id as id1_4_, auditlogct0_.object_name as object_n2_4_, auditlogct0_.object_sub_type as object_s3_4_, auditlogct0_.object_type as object_t4_4_ from (select auditlog0_.id as col_0_0_, auditlog0_.object_type as col_1_0_, auditlog0_.object_sub_type as col_2_0_, auditlog0_.object_name as col_3_0_ from anjana.audit_log auditlog0_) auditlogct0_(id,object_type,object_sub_type,object_name)

but in the documentation shows that it should be with the "with AuditLogCte" structure

I think I'm happy with the other proposed solution, but I'm curious to get the cte query working.

[beikov]

Hey there. The query you are seeing is equivalent to the CTE version, since a plain CTE is more or less the same as the inlined subquery. It would only make a difference if the CTE had the materialized keyword.

This inlining of CTEs can be controlled with a configuration setting.

[RaulPampliegaMayoral]

Hi again @beikov! I was finally able to get the test I wanted to run with the query I was hoping for, thanks to the specified configuration.
A query like the following in Blazebit:

    CriteriaBuilder<Tuple> cb = cbf.create(entityManager, Tuple.class)
            .from(FacetFieldValueDTO.class)
            .selectSubquery()
              .from(AuditLog.class)
              .select("'objectType'")
              .select("objectType")
              .select("count(*)")
              .groupBy("objectType")
            .union()
              .from(AuditLog.class)
              .select("'objectSubType'")
              .select("objectSubType")
              .select("count(*)")
              .groupBy("objectSubType")
            .union()
              .from(AuditLog.class)
              .select("'objectSubType'")
              .select("objectSubType")
              .select("count(*)")
              .groupBy("objectSubType")
            .endSet().end();

generates the following query:

with AuditLogCte(id, object_type, object_sub_type, object_name) AS( 
	select auditlog0_.id as col_0_0_, auditlog0_.object_type as col_1_0_, auditlog0_.object_sub_type as col_2_0_, auditlog0_.object_name as col_3_0_ from minerva.audit_log auditlog0_ 
) 
select 'subtype' as col_0_0_, 
        auditlogct0_.object_sub_type as col_1_0_, 
        count(*) as col_2_0_ 
        from AuditLogCte auditlogct0_ 
        group by auditlogct0_.object_sub_type 
UNION ALL 
select 'type' as col_0_0_, 
       auditlogct0_.object_type as col_1_0_, 
       count(*) as col_2_0_ 
       from AuditLogCte auditlogct0_ 
       group by auditlogct0_.object_type

which was expected. Thank you very much for your time and help