Search with the supplied value in the current and all successive parents when both child and parent are stored in the same table using JPA Hibernate

Question

I have two entities:

Influence details mapped as OneToOne with aggregators

/**
 * @author MalkeithSingh on 27-08-2019
 */
@Entity
@Table(name = "INFLUENCE_DETAILS")
@Builder
@NoArgsConstructor
@AllArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
@JsonNaming(PropertyNamingStrategy.LowerCaseWithUnderscoresStrategy.class)
public class InfluenceDetails {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;



    @Column(name = "influence_summary_id", length = 64)
    private Long influenceSummaryId;

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "aggregator_id", nullable = false, referencedColumnName = "agg_id",
                updatable = false, insertable = false)
    private QualityAggregator aggregators;



    @Column(name = "rule_id", length = 64)
    private String ruleId;

    @Column(name = "rule_desc", nullable = false)
    private String ruleDesc;

    @Column(name = "value")
    private String value;

Entity 2:
And QualityAggregators. A quality aggregator can have another aggregator as a parent which can again have a parent and so on. Hence a child parent relationship in the same table.

@Entity
@Table(name = "QUALITY_AGGREGATOR")
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
@JsonNaming(PropertyNamingStrategy.LowerCaseWithUnderscoresStrategy.class)
@Builder
@JsonInclude(JsonInclude.Include.NON_NULL)
public class QualityAggregator {

    @Id
    @Column(name = "agg_id")
    private String aggId;

    @Column(name = "project_id")
    private String projectId;

    @Column(name = "job_id")
    private String jobId;

    @Column(name = "job_instance_id")
    private String jobInstanceId;

    @Column(name = "created_at")
    private Date timestamp;

    @Column(name = "agg_key")
    private String aggKey;

    @Column(name = "agg_value")
    private String aggValue;

    @Column(name = "level")
    private Integer level;

    @Column(name = "parent_agg_id")
    private String parentAggId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_agg_id", referencedColumnName = "agg_id",insertable = false,updatable = false)
    private QualityAggregator parent;

    @OneToMany(mappedBy = "parent",fetch = FetchType.EAGER)
    @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
    private Set<QualityAggregator> children;

Note:- The last child in the aggregators table is mapped to the influence detail table.

Now what i want to do is to get all InfluenceDetails where both the agg_key and agg_value in the mapped child aggregator or in any of it parents matches the supplied value.

This is what I have so far. I am not aware of how many levels the aggregators might have so I take default 5 and have made the property configurable. But there has to be a better way.

public class InfluencerDetailsSpecification {

    @Value("{no.of.aggregators}")
    private Integer aggregators;

    @PostConstruct
    public void init(){
        setNoOfAggregators(aggregators);
    }

    private static Integer noOfAggregators = 5;

    /**
     * Creates a query for searching in aggregators and all its successive parents upto the no of levels specified(Defaults to 5)
     * @param request list of AggregatorsRequest to be searched
     * @return The prepared specification
     */
    public static Specification<InfluenceDetails> findByAggregators(List<AggregatorsRequest> request) {
        return (root, query, cb) -> {
            Join<InfluenceDetails, QualityAggregator> lastChild = root.join("aggregators");

            Function<AggregatorsRequest, Predicate> toPredicate = agg -> {
                List<Predicate> listOfOrPredicates = new ArrayList<>();
                Join<QualityAggregator, QualityAggregator> parent =  lastChild.join("parent",JoinType.LEFT);
                Predicate p0 = cb.and(cb.equal(lastChild.get("aggKey"), agg.getAggKey()), cb.equal(lastChild.get("aggValue"), agg.getAggValue()));
                listOfOrPredicates.add(p0);
                Integer aggsNos = Integer.valueOf(noOfAggregators);
                while(aggsNos-- > 2){
                    listOfOrPredicates.add(cb.and(cb.equal(parent.get("aggKey"), agg.getAggKey()), cb.equal(parent.get("aggValue"), agg.getAggValue())));
                    parent = parent.join("parent",JoinType.LEFT);
                }
                return cb.or(listOfOrPredicates.toArray(new Predicate[listOfOrPredicates.size()]));
            };
            return cb.and(cb.or(request.stream().map(toPredicate).toArray(Predicate[]::new)));
        };

    }

    public static Integer getNoOfAggregators() {
        return noOfAggregators;
    }

    public static void setNoOfAggregators(Integer noOfAggregators) {
        if(Objects.isNull(noOfAggregators))
            noOfAggregators = 5;

        InfluencerDetailsSpecification.noOfAggregators = noOfAggregators;
    }
}

Answer 1

Here is the recursive "one step" solution. I implemented it with FluentJPA:

public List<InfluenceDetails> getDetailsByAggregator(String aggKey,
                                                     String aggValue) {
    FluentQuery query = FluentJPA.SQL((InfluenceDetails details) -> {

        QualityAggregator relevantAgg = subQuery((QualityAggregator it,
                                                  QualityAggregator agg,
                                                  QualityAggregator child) -> {
            SELECT(agg);
            FROM(agg);
            WHERE(agg.getAggKey() == aggKey && agg.getAggValue() == aggValue);

            UNION_ALL();

            SELECT(child);
            FROM(child).JOIN(recurseOn(it)).ON(child.getParent() == it);
        });

        WITH(RECURSIVE(relevantAgg));

        // DISTINCT removes possible dups
        SELECT(DISTINCT(details));
        FROM(details).JOIN(relevantAgg).ON(details.getAggregators() == relevantAgg);
    });

    return query.createQuery(em, InfluenceDetails.class).getResultList();
}

this produces the following SQL:

WITH RECURSIVE q0  AS 
(SELECT t2.* 
FROM QUALITY_AGGREGATOR t2 
WHERE ((t2.agg_key = ?1) AND (t2.agg_value = ?2)) 
UNION ALL  
SELECT t3.* 
FROM QUALITY_AGGREGATOR t3  INNER JOIN q0 t1  ON (t3.parent_agg_id = t1.agg_id) )

SELECT DISTINCT t0.*  
FROM INFLUENCE_DETAILS t0  INNER JOIN q0  ON (t0.aggregator_id = q0.agg_id)

Explanation of the WITH RECURSIVE.