JPA query of a Set of terms

Hi,

Is it possible to write a JPA query to select objects that contain a collection of elements?

For example, suppose I have a table that maps a search terms to pages:

@Entity
public class SearchIndex {
  @Index
  SearchTerm term;

  @Index
  Page page;
}

Now suppose I have an array of SearchTerm objects. ie.

Set<SearchTerm> terms;
    // the set is filled with SearchTerm objects that are already persisted.
em.createQuery(
    "SELECT DISTINCT si.page FROM SearchIndex si WHERE si.term ????",Page.class);

I want the query to give me all Pages that match all of the search terms provided. Is this possible?

Thanks,

Carl

Retrieving pages that contain any term should be simpler:

Set<SearchTerm> terms;
    // the set is filled with SearchTerm objects that are already persisted.
TypedQuery<Page> query = em.createQuery(
    "SELECT DISTINCT si.page FROM SearchIndex si WHERE si.term MEMBER OF :terms",
    Page.class);
query.setParameter("terms", terms);

I am afraid that retrieving pages that contain all the terms requires building a dynamic query based on the number of search terms. For example, for two search terms:

TypedQuery<Page> query = em.createQuery(
    "SELECT DISTINCT p FROM Page p, SearchIndex s1, SearchIndex s2 " +
    "WHERE s1.page = p AND s1.term = :t1 AND s2.page = p AND s2.term = :t2",
    Page.class);
query.setParameter("t1", t1).setParameter("t2", t2);

By the way, since ObjectDB supports indexed collections you may also consider a simpler schema, with no intermediate SearchIndex class:

@Entity
public class Page {
  @Index
  List<SearchTerm> terms;
}

In that case the query is simpler (and possibly faster):

TypedQuery<Page> query = em.createQuery(
    "SELECT DISTINCT p FROM Page p " +
    "WHERE :t1 MEMBER OF p.terms AND :t2 MEMBER OF p.terms",
    Page.class);
query.setParameter("t1", t1).setParameter("t2", t2);

The following query should also be valid in ObjectDB:

TypedQuery<Page> query = em.createQuery(
    "SELECT DISTINCT p FROM Page p WHERE p.terms.containsAll(:terms)",
    Page.class);
query.setParameter("terms", terms);

but it is not standard JPQL and it would be less efficient since indexes cannot be used.

Thanks so much for the help.

The Indexed collection looks extremely powerful.

@Entity
public class Page {
  @Index
  List<SearchTerm> terms;
}

In the above case, is there a separate index for each Page, or a single combined index for all pages?

Carl

You are welcome.

In the above definition there is one index, implemented using one BTree, for the entire database. The number of entries in this BTree may be much larger than the number of Page instances in the database, because each Page may have multiple entries in the index, one per term.

See also the document and words sample queries in the manual.