[FIXED] How to join data in unidirectional ManyToMany and ManyToOne relation with Hibernate Search

Issue

I'm new to Hibernate Search, I'm using Hibernate Search 6.1.4.Final along with Spring Boot 2.6.7, Spring Data JPA 2.6.7 and PostgreSQL 14. I have the following schema:

CREATE TABLE manufacturer
(
    id         UUID,
    PRIMARY KEY (id)
);

CREATE TABLE flavor
(
    id         UUID,
    PRIMARY KEY (id)
);

CREATE TABLE tobacco
(
    id              UUID,
    name            VARCHAR   NOT NULL,
    manufacturer_id UUID      NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE tobacco
    ADD CONSTRAINT fk_manufacturer FOREIGN KEY (manufacturer_id) REFERENCES manufacturer (id);

CREATE TABLE tobacco_flavor
(
    tobacco_id UUID REFERENCES tobacco (id) ON UPDATE CASCADE ON DELETE CASCADE,
    flavor_id  UUID REFERENCES flavor (id) ON UPDATE CASCADE,
    CONSTRAINT tobacco_flavor_pk PRIMARY KEY (tobacco_id, flavor_id)
);

I have an unidirectional ManyToOne relationship between Tobacco and Manufacturer, and another unidirectional ManyToMany relationship between Tobacco and Flavor. The indexed entity is:

@Data
@Entity
@Indexed
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
@EqualsAndHashCode(of = "id")
@EntityListeners(AuditingEntityListener.class)
public class Tobacco {
    @Id
    @GeneratedValue
    private UUID id;
    @NotBlank
    @FullTextField(analyzer = "name")
    @FullTextField(name = "name_prefix", analyzer = "name_prefix", searchAnalyzer = "name")
    private String name;
    @ManyToOne(fetch = FetchType.EAGER) // this is EAGER because hibernate cannot serialize the proxy wrapper when using LAZY
    @JoinColumn(name = "manufacturer_id", nullable = false)
    private Manufacturer manufacturer;
    @ManyToMany(cascade = {
        CascadeType.MERGE
    })
    @JoinTable(name = "tobacco_flavor",
        joinColumns = @JoinColumn(name = "tobacco_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "flavor_id", referencedColumnName = "id")
    )
    private Set<Flavor> flavors;
}

I'm using the following lucene configurer:

@Component("luceneTobaccoAnalysisConfigurer")
public class LuceneTobaccoAnalysisConfigurer implements LuceneAnalysisConfigurer {
    @Override
    public void configure(LuceneAnalysisConfigurationContext context) {
        context.analyzer("name").custom()
            .tokenizer("standard")
            .tokenFilter("lowercase")
            .tokenFilter("asciiFolding");

        context.analyzer("name_prefix").custom()
            .tokenizer("standard")
            .tokenFilter("lowercase")
            .tokenFilter("asciiFolding")
            .tokenFilter("edgeNGram")
            .param("minGramSize", "2")
            .param("maxGramSize", "7");
    }
}

And I'm performing the following query:

public List<Tobacco> find(String query) {
    return Search.session(entityManager)
        .search(Tobacco.class)
        .where(f -> f.match()
            .fields("barcode", "name").boost(2.0f)
            .fields("name_prefix")
            .matching(query)
            .fuzzy()
        )
        .fetchHits(10);
}

When I execute the query Hibernate performs 5 SELECT queries instead of 1 using joins. I would like to query always by tobacco name and retrieve all of the entities associated to it (flavors and manufacturer), is there any way of instructing Hibernate Search to perform the query with a join efficiently?

Solution

To control what gets loaded by Hibernate Search, you can leverage JPA entity graphs in Hibernate Search by calling .loading( o -> o.graph( someGraph, GraphSemantic.FETCH ) ) when building your search query.

Something like this?

public List<Tobacco> find(String query) {
    EntityGraph<Tobacco> graph = entityManager.createEntityGraph( Tobacco.class ); 
    graph.addAttributeNodes( "manufacturer" );
    graph.addAttributeNodes( "flavors" );
    return Search.session(entityManager)
        .search(Tobacco.class)
        .where(f -> f.match()
            .fields("barcode", "name").boost(2.0f)
            .fields("name_prefix")
            .matching(query)
            .fuzzy()
        )
        .loading( o -> o.graph( graph, GraphSemantic.FETCH ) ) 
        .fetchHits(10);
}

However, depending on your model and entity graph, this might lead to more than one query being executed regardless (due to implementation constraints in Hibernate ORM). In that case, you can explore these alternative solutions that will affect any loading, not just loading in Hibernate Search:

• Use @Fetch(FetchMode.JOIN) on you flavors association.
• Leverage Hibernate ORM’s batch fetching to only ever trigger two SQL queries (one for the tobacco instances, one for their flavors): see the batch_fetch_size configuration property and the @BatchSize annotation.

Answered By - yrodiere
Answer Checked By - Pedro (JavaFixing Volunteer)