Geek asks and answers

Ebean: how to filter the right table in LEFT OUTER JOIN

I want to create a query with Ebean as follows:

SELECT 
    t0.book_id, t0.name, t1.accno
FROM
    books t0
        LEFT OUTER JOIN
    external_refs t1 ON t0.book_id = t1.book_id AND t1.type = "doi"
WHERE
    t0.book_id in (1, 2, 3, 4)

The essence of the Book contains a one-to-many relationship.

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name="book_id", referencedColumnName = "book_id")
public List<ExternalRef> externalRefs;

This is how I create the query:

List<Integer> bookIds;
Query<Book> query = Ebean.createQuery(Book.class).where(Expr.in("book_id", bookIds));

How to add a part AND t1.type = "doi"to this request?

+3
source share
1 answer

I think your request cannot be improved to meet your requirements. But I built another that returns the desired results. Here is the code:

Query<ExternalRef> queryER = Ebean.createQuery(ExternalRef.class).where(Expr.and (Expr.eq("type", "doi"), Expr.in("book.book_id", bookIds) ));

So, instead of books, I request for ExternalRefs.

0
source

More articles:


All Articles