Sub Query

There are many SQL tutorials out there. They make a good job reviewing SQL from the very basic to advanced functionality. We have chosen one of such tutorials and wrote most interesting SQL samples from there in FluentJPA (skipping repeating stuff). So we suggest to open the original tutorial side-by-side and compare. Each topic below is a link to the relevant tutorial page; search the label above the sample. Have fun comparing!

JPA mappings (Model) is declared here.

PostgreSQL Subquery

Average rental rate

FluentJPA.SQL((Film film) -> {

    SELECT(AVG(film.getRentalRate()));
    FROM(film);
});

with use of the Library, the same can be written in one line:

FluentJPA.SQL((Film film) -> {

    // pick is a builtin function that SELECTs expression from given table
    // and returns the result as a scalar
    pick(film, AVG(film.getRentalRate()));
});

Films whose rental rate is higher than the average rental rate

FluentJPA.SQL((Film film) -> {

    SELECT(film);
    FROM(film);
    WHERE(film.getRentalRate() > 2.98);
});

Let's combine the two queries together

FluentJPA.SQL((Film film) -> {

    // To create a sub query we call subQuery()
    // The result is casted to what we need
    float avgRentalRate = subQuery(() -> {
        SELECT(AVG(film.getRentalRate()));
        FROM(film);
    });

    SELECT(film);
    FROM(film);
    WHERE(film.getRentalRate() > avgRentalRate);
});

Same with pick()

FluentJPA.SQL((Film film) -> {

    SELECT(film);
    FROM(film);
    WHERE(film.getRentalRate() > pick(film, AVG(film.getRentalRate())));
});

In scalar context pick() makes the code clear and concise

Sub query with IN operator

Films that have the returned date between 2005-05-29 and 2005-05-30

FluentJPA.SQL((Inventory inv,
               Rental rental) -> {

    SELECT(inv);
    FROM(inv).JOIN(rental).ON(rental.getInventory() == inv);
    WHERE(BETWEEN(rental.getReturnDate(), DATE.of("2005-05-29"), 
                                          DATE.of("2005-05-30")));
});

In practice the dates would be parameterized. Also let's refactor and extract the query to a function

public List<X> myMethod(Date from, Date to) {

    FluentJPA.SQL(() -> {

        Inventory returned = inventoryReturnedBetweenDates(from, to);
    });
    ...
}

// why is Inventory the return type? Because Inventory columns are SELECTed!
private static Inventory inventoryReturnedBetweenDates(Date from,
                                                       Date to) {
    return subQuery((Inventory inv,
                     Rental rental) -> {
        // since inv is an entity, FluentJPA translates SELECT(inv) to SELECT inv.*
        SELECT(inv);
        FROM(inv).JOIN(rental).ON(rental.getInventory() == inv);
        WHERE(BETWEEN(rental.getReturnDate(), from, to));
    });
}

Lets use our brand new function in the WHERE clause

To use Inventory in the IN context, we need to project it to film_id:

FluentJPA.SQL(() -> {

    Inventory returned = inventoryReturnedBetweenDates(from, to);

    // in Java, projection of a single field is abstracted as a Collection
    Collection<Integer> filmIds = subQuery(() -> {
        SELECT(returned.getFilm().getId());
        FROM(returned);
    });
});

Projection is a so common operation, FluentJPA provides it builtin by collect() Library method. So we can write:

FluentJPA.SQL((Film film) -> {

    Inventory returned = inventoryReturnedBetweenDates(from, to);

    SELECT(film);
    FROM(film);
    WHERE(IN(film.getId(), collect(returned, returned.getFilm().getId())));
    //                     ^^^^^^^- returns a ready to use projection as a Java Collection
});

... but IN is semantically Collection.contains in Java. To make the usage "fluent", FluentJPA maps Collection.contains to IN. Finally we have:

FluentJPA.SQL((Film film) -> {

    Inventory returned = inventoryReturnedBetweenDates(from, to);

    // another shortcut from Library. Same as SELECT(film); FROM(film);
    selectAll(film);

    // collect().contains() with full compile time type safety
    WHERE(collect(returned, returned.getFilm().getId()).contains(film.getId()));
});

private static Inventory inventoryReturnedBetweenDates(Date from,
                                                       Date to) {
    return subQuery((Inventory inv,
                     Rental rental) -> {
        SELECT(inv);
        FROM(inv).JOIN(rental).ON(rental.getInventory() == inv);
        WHERE(BETWEEN(rental.getReturnDate(), from, to));
    });
}

Sub query with EXISTS operator

Of course the simplest way to implement the requirement is without EXISTS

FluentJPA.SQL((Customer customer,
               Payment payment) -> {

    SELECT(customer.getFirstName(), customer.getLastName());
    FROM(customer);
    WHERE(collect(payment, payment.getCustomer().getId()).contains(customer.getId()));
});

Let's do it the EXISTS way as an exercise

FluentJPA.SQL((Customer customer) -> {

    SELECT(customer.getFirstName(), customer.getLastName());
    FROM(customer);
    WHERE(EXISTS(customerPayments(customer)));
});

private static Payment customerPayments(Customer customer) {
    return subQuery((Payment payment) -> {

        selectAll(payment);
        WHERE(payment.getCustomer() == customer);
    });
}