Prefetching rows from joined tables

Prefetching rows from joined tables
Prev	Chapter 9. Adding joins to resultsets	Next

Schema (partial):

  ...
  <table name="ledger_entries">
    <column name="ledger_entry_id" datatype="int64_t" primarykey='1' />
    <column name="account_id" datatype="int64_t"/>
    <column name="ledger_date" datatype="x::ymd"/>
    <column name="amount" datatype="double"/>

    <join class="accounts" type="inner join" only="1">
      <column>account_id</column>
    </join>

    <join class="payments" type="inner_join" maybe="1" name="source_ledger_id">
      <column foreigncolumn='source_ledger_id'>ledger_entry_id</column>
    </join>

    <join class="payments" type="inner_join" maybe="1" name="dest_ledger_id">
      <column foreigncolumn='dest_ledger_id'>ledger_entry_id</column>
    </join>

  </table>

  <table name="payments">
    <column name="payment_id" datatype="int64_t" primarykey='1' />
    <column name="source_ledger_id" datatype="int64_t" />
    <column name="dest_ledger_id" datatype="int64_t" />

    <join class="ledger_entries" type="inner join" name="source_ledger_id" only="1">
      <column foreigncolumn='ledger_entry_id'>source_ledger_id</column>
    </join>

    <join class="ledger_entries" type="inner join" name="dest_ledger_id" only="1">
      <column foreigncolumn='ledger_entry_id'>dest_ledger_id</column>
    </join>
  </table>

  ...

Code:

int64_t key;

// ...
payments payments_rs=payments::create(conn);

payments_rs->search("payment_id", "=", key);

This partial example creates a resultset on the payments table with a constraint that retrieves a single row, for the given payment_id. The payments table has two joins to the ledger_entries table, which get added to the resultset and presumably, there will be other search() constraints (not shown) that reference the joined tables.

A resultset for the payments table always returns a row that has only the columns from the payments table. The resulting SELECT SQL query lists only its table's columns, and a nested resultset is required to retrieve the corresponding row or rows from the joined table. Prefetching eliminates the need to make an additional query:

int64_t key;

// ...
payments payments_rs=payments::create(conn);

payments_rs->search("payment_id", "=", key);

ledger_entries::base::joins
     source_ledger_id_join=payments_rs->join_source_ledger_id(),
     dest_ledger_id_join=payments_rs->join_dest_ledger_id();

ledger_entries::base::prefetchedrow
    prefetched_src_ledger=source_ledger_id_join->prefetch(),
    prefetched_dst_ledger=dest_ledger_id_join->prefetch();

for (const payments::base::row &payment: *payments)
{
    ledger_entries::base::row source_ledger=prefetched_src_ledger->row,
                              dest_ledger=prefetched_dst_ledger->row;
// ...

Iterating over a class result iterates over class::base::rows. Each join to a joinclass's table, a joinclass::base::joins implements a prefetch() method that returns a joinclass::base::prefetchedrow. Invoking prefetch() has the following effect:

The joinclass's table's columns get added to the columns in the SELECT query.
Iterating to each row from class's table, sets each joinclass::base::prefetchedrow's row to joinclass::base::row from the additional columns.
prefetch() must be invoked before getting the beginning iterator value from begin(), or before the range iteration starts. If the resultset gets iterated over again, prefetch() must be invoked again, in order to return the joinclass::base::prefetchedrow for the second iteration.

Note

If a row in the resultset's table gets joined to multiple rows in another table, the row occurs once in the SQL results for each joined row, naturally. The row gets iterated over once, for each occurence of the row in the query's resultset. Each iteration prefetches the corresponding joined row, into the joinclass::base::prefetchedrow.

ledger_entries::base::prefetchedrow
    prefetched_src_ledger=source_ledger_id_join->prefetch(),
    prefetched_dst_ledger=dest_ledger_id_join->prefetch();

payments::base::row payment=payments->only();
ledger_entries::base::row source_ledger=prefetched_src_ledger->row,
                          dest_ledger=prefetched_dst_ledger->row;
// ...

Prefetched rows are also available after invoking only() (or maybe(), if it returns a non-null() row).

Prev	Up	Next
Nested resultsets	Table Of Contents	Chapter 10. Adding `GROUP BY`, `HAVING`, and `ORDER BY` to resultsets