Nested resultsets

Nested resultsets
Prev	Chapter 9. Adding joins to resultsets	Next

Using the same schema given in the beginning of this chapter, the following example executes the equivalent of “SELECT * from accounts” followed by a “SELECT * from ledger_entries where account_id=value” for each row in the accounts resultset, substituting that row's account_id value for value:

accounts accounts_rs=accounts::create(conn);

for (const accounts::base::row &row: *accounts_rs)
{
    ledger_entries account_ledger=row->join_ledger_entries();

    for (const example2::ledger_entries::base::row &row: *account_ledger)
    {
       // ...

Each class::base::row, each row in class's resultset, also implements join_class() methods, from the schema, like the resultset does, but in a slightly different way.

The row's join_class() returns a new resultset, class instead of a class::base::joins and initializes it with a constraint based on the values of the row's key fields.

This has the effect of creating a resultset that represents the rows in the foreign table that the row joins to. The resulting resultset is no different than any other resultset, and accepts additional constraints, by calling its search() method. For example:

accounts accounts_rs=accounts::create(conn);

for (const accounts::base::row &row: *accounts_rs)
{
    ledger_entries account_ledger=row->join_ledger_entries();

    account_ledger->search("amount", ">", 0);
    for (const example2::ledger_entries::base::row &row: *account_ledger)
    {
       // ...

This example results in nested resultsets that execute “SELECT * from ledger_entries where account_id=value AND amount > 0” for each row in the accounts resultset.

<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>
</table>

A join element has two optional attributes, only and maybe, which are mutually exclusive. These attributes take effect for nested resultset joins only, for join_classname() methods implemented by classname::base::row, and do not apply to a resultset's join_classname()s.

They have the effect of having the classname::base::row's join_joinname() automatically using only() or maybe(), and returning another class::base::row or class::base::rowptr, respectively.

int64_t ledger_id;

auto ledger_entries_rs=ledger_entries::create(conn);

ledger_entries_rs->search("ledger_entry_id", "=", ledger_id);

auto ledger_entry=ledger_entries_rs->only();

auto account=ledger_entry->join_accounts();

In the example schema, the accounts table's account_id is a foreign key in the ledger_entries table, so the ledger_entries's join to accounts using account_id always joins one row from accounts. Without the only attribute, join_accounts() returns, by default, a resultset which always ends up containing one row. only saves the trouble of always invoking only() on the resultset, and changes join_accounts() so that it returns an accounts::base::row directly.

Similarly, the maybe attribute changes join_joinname() so that it uses maybe() and returns a class::base::rowptr.

Note

The resulting class::base::row or class::base::rowptr gets cached. Invoking join_joinname() again returns the same row, and does not create another resultset and execute it.

Since only or maybe create the nested resultset and immediately iterate over it, it's not possible to add additional joins to the nested resultset and prefetch rows from them. Use only and maybe only when it this is never required.

One suggested naming convention is to always define two joins, from a table with a foreign key to the table with the primary key, like this:

<schema>
  <table name="ledger_entries">

    <!-- ... -->

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

    <join class="accounts" joinname="account" type="inner join" only="1">
      <column>account_id</column>
    </join>
  </table>
</schema>

join_account() returns a row and join_account_id() returns a resultset for the same logical join.