MAX() as a filtering clause for a LEFT JOIN, Are there any alternatives?

Question

It seems to me that there is another option for this query, but I cannot pinpoint exactly what is that option:

@Query("" +
        "SELECT " +
        "... not really important... " +
        "FROM (" +
        "SELECT ect.* " +
        "FROM element_constant_table ect " +
        "WHERE book_parent_id = :bookId AND availability = 1" +
        ") ect1 " +
        "LEFT JOIN " +
        "element_version_table evt " +
        "ON (" +
            "SELECT evt.element_parent_id " +
            "WHERE " +
            "evt.time = (" +
                "SELECT MAX(evt2.time) " +
                "FROM element_version_table evt2 " +
                "WHERE evt2.element_parent_id = evt.element_parent_id" +
            ") " +
        ") = ect1.element_id" +
        "")
public abstract LiveData<List<Element>> getLatestElementsAt(int bookId);

For readability:

        SELECT 
        ... not really important... 
        FROM (
        SELECT ect.* 
        FROM element_constant_table ect 
        WHERE book_parent_id = :bookId AND availability = 1
        ) ect1 
        LEFT JOIN 
        element_version_table evt 
        ON (
            SELECT evt.element_parent_id 
            WHERE 
            evt.time = (
                SELECT MAX(evt2.time) 
                FROM element_version_table evt2 
                WHERE evt2.element_parent_id = evt.element_parent_id
            ) 
        ) = ect1.element_id

The MAX() function is forcing a new instantiation of the whole element_version_table for each row found on element_constant_table.

I was aware that something like this may happen so I always try to filter the table before it reaches the LEFT JOIN, in this case with the clause "WHERE book_parent_id = :bookId AND availability = 1"

There is no way to filter the LEFT TABLE and make it coincide with its corresponding key on the RIGHT TABLE before the ON clause, and so the WHERE needs to be placed there.

The problem is that if instead of using the MAX(), I use a simple ORDER BY evt.time DESC LIMIT 1 inside the ON clause the compiler tells me that there is no evt.time field (I assume that it does not exist YET). So my conclusions to this are:

a) An ORDER BY requires for the table to be instantiated to its full length (It does not happens during the process of row location).

b) The ON clause happens BEFORE the LEFT TABLE reaches instantiation (what I assume is) to it's full length.

Are my conclusions about the order of clause execution correct?

How much of a burden may this query be on an extensive Database? and What would a better option be?

Answer 1

Please use below sql query to retrieve live data of book in your code:

     SELECT 
    ... not really important... 
    FROM element_constant_table ect 
    LEFT JOIN 
    (SELECT evt.*, ROW_NUMBER() OVER (PARTITION BY evt.element_parent_id ORDER BY evt.time DESC) as seqnum 
    from element_version_table evt) evt_final on ect.element_id = evt_final.element_parent_id and evt_final.seqnum=1
    where ect.book_parent_id = :bookId AND ect.availability = 1

I am finding here latest book record from "element_version_table" if exists.

Answer 2

SQLite window function ROW_NUMBER() can be a solution to your problem.

https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/

You didn't provide full details of your tables. But, depending on size of your element_version table and query performance, you can try:

SELECT 
    ... not really important...  FROM
    (SELECT ect.*
    FROM element_constant_table ect
    WHERE book_parent_id=:bookId AND availability=1
    ) ect1

    LEFT JOIN

    (SELECT t.*
     FROM
        (SELECT
            evt.*,
            ROW_NUMBER() OVER (PARTITION BY element_parent_id ORDER BY evt.time DESC) rn
         FROM element_version_table evt) t
     WHERE t.rn = 1
    ) evt1

    ON ect1.element_id = evt1.element_parent_id

or

SELECT t.*
FROM
    (SELECT 
        ... not really important...,
        ROW_NUMBER() OVER (PARTITION BY ect1.element_id, evt1.element_parent_id ORDER BY evt1.time DESC) rn

    FROM
        (SELECT ect.*
        FROM element_constant_table ect
        WHERE book_parent_id=:bookId AND availability=1
        ) ect1

        LEFT JOIN element_version_table evt1
        ON ect1.element_id = evt1.element_parent_id
    ) t
WHERE t.rn = 1

Answer 3

This solution would be for SQLite versions < 3.25.0

Thanks to user @armamut That gave the original idea and this other question from 7 years ago MySQL order by before group by.

@Query("" +
        "SELECT " +
        ... not really important ...
        "FROM (" +
            "SELECT ect.* " +
            "FROM element_constant_table ect " +
            "WHERE book_parent_id = :bookId AND availability = 1" +
        ") ect1 " +
        "LEFT JOIN " +
        "(" +
            "SELECT " +
                "evt.*," +
                "MAX(evt.time) " +
            "FROM element_version_table evt " +
            "GROUP BY evt.element_parent_id" +
        ") evt1 " +
        "ON evt1.element_parent_id = ect1.element_id" +
        "")
public abstract LiveData<List<Element>> getLatestElementsAt(int bookId);

For readability:

        SELECT 
        ... not really important ...
        FROM (
            SELECT ect.* 
            FROM element_constant_table ect 
            WHERE book_parent_id = :bookId AND availability = 1
        ) ect1 
        LEFT JOIN 
        (
            SELECT 
                evt.*,
                MAX(evt.time) 
            FROM element_version_table evt 
            GROUP BY evt.element_parent_id
        ) evt1 
        ON evt1.element_parent_id = ect1.element_id