The export that read the whole table

import java.util.*;

/**
 * Solution: the export that read the whole table.
 *
 * Four defects, and only two of them are N+1 queries. The other two are the
 * opposite mistake — one statement that reads far more than it needs. Counting
 * statements alone would have found half the problem.
 */
public class Solution {

    /* ── the store (correct; do not change) ─────────────────────────────── */

    record Item(long id, long orderId, String sku, int quantity) {}
    record Order(long id, long customerId, String reference, String status) {}

    static final class Store {
        final List<Order> orders = new ArrayList<>();
        final Map<Long, List<Item>> items = new LinkedHashMap<>();

        int statements, rowsRead, columnsRead;

        Store seed(int orderCount, int itemsPerOrder) {
            for (long o = 1; o <= orderCount; o++) {
                orders.add(new Order(o, o % 50, "REF-" + o, o % 3 == 0 ? "OPEN" : "CLOSED"));
                var list = new ArrayList<Item>();
                for (int i = 0; i < itemsPerOrder; i++)
                    list.add(new Item(o * 100 + i, o, "sku-" + i, i + 1));
                items.put(o, list);
            }
            return this;
        }

        void reset() { statements = rowsRead = columnsRead = 0; }

        /** A page of orders. Four columns each, because an entity carries the row. */
        List<Order> pageOfOrders(int size) {
            statements++;
            var page = orders.subList(0, Math.min(size, orders.size()));
            rowsRead += page.size();
            columnsRead += page.size() * 4;
            return new ArrayList<>(page);
        }

        /**
         * A fetch join: one statement, but one ROW per item, and the limit
         * cannot be applied in SQL without truncating collections.
         */
        List<Order> pageOfOrdersJoinFetch(int size) {
            statements++;
            for (List<Item> list : items.values()) rowsRead += list.size();
            columnsRead += rowsRead * 8;
            return new ArrayList<>(orders.subList(0, Math.min(size, orders.size())));
        }

        /** One order's items. This is the statement a lazy getter hides. */
        List<Item> itemsOf(long orderId) {
            statements++;
            var list = items.getOrDefault(orderId, List.of());
            rowsRead += list.size();
            columnsRead += list.size() * 4;
            return list;
        }

        /** Items for many orders at once — the query a fix should produce. */
        Map<Long, List<Item>> itemsOf(List<Long> orderIds) {
            statements++;
            var out = new LinkedHashMap<Long, List<Item>>();
            for (Long id : orderIds) {
                var list = items.getOrDefault(id, List.of());
                out.put(id, list);
                rowsRead += list.size();
                columnsRead += list.size() * 4;
            }
            return out;
        }

        /** Exactly the columns named — what a projection or aggregate reads. */
        List<long[]> summaryRows(int size) {
            statements++;
            var out = new ArrayList<long[]>();
            for (Order o : orders.subList(0, Math.min(size, orders.size()))) {
                out.add(new long[] { o.id(), items.getOrDefault(o.id(), List.of()).size() });
                rowsRead++;
                columnsRead += 2;
            }
            return out;
        }

        /** A count, answered by the database. */
        long countOrders() { statements++; rowsRead++; columnsRead++; return orders.size(); }
    }

    /* ── the service under review ───────────────────────────────────────── */

    record OrderView(long id, String reference, int itemCount) {}

    static final class OrderService {
        final Store store;
        OrderService(Store store) { this.store = store; }

        /*
         * Defects 1 and 2, in one method. It used a fetch join AND still read
         * the collection per row, so it paid twice: the join could not be
         * limited in SQL — a limit would truncate collections, so the whole
         * table came back and was paged in memory — and the loop then issued a
         * statement per order anyway.
         *
         * The shape that works with pagination is two queries: page the
         * parents with a query the database can limit, then load the children
         * for exactly that page of ids.
         */
        List<OrderView> listPage(int size) {
            var page = store.pageOfOrders(size);
            var ids = page.stream().map(Order::id).toList();
            var itemsByOrder = store.itemsOf(ids);
            var views = new ArrayList<OrderView>();
            for (Order o : page)
                views.add(new OrderView(o.id(), o.reference(),
                    itemsByOrder.getOrDefault(o.id(), List.of()).size()));
            return views;
        }

        /*
         * Defect 3. A textbook N+1: one query for the page, then one per row
         * for the item count. Nothing in the loop looks like a query, which is
         * why it survived review.
         *
         * The count is the only thing this tile needs from the children, so
         * the database should compute it — an aggregate reads two columns per
         * row instead of an entity's worth.
         */
        List<OrderView> dashboard(int size) {
            var page = store.pageOfOrders(size);
            var counts = new LinkedHashMap<Long, Long>();
            for (long[] row : store.summaryRows(size)) counts.put(row[0], row[1]);
            var views = new ArrayList<OrderView>();
            for (Order o : page)
                views.add(new OrderView(o.id(), o.reference(),
                    counts.getOrDefault(o.id(), 0L).intValue()));
            return views;
        }

        /*
         * Defect 4. Not an N+1 at all — one statement, and it returned every
         * order in the table so that Java could call size() on the list. The
         * database can answer this without materialising a single row.
         *
         * This is the mirror image of an N+1 and a query-count assertion would
         * never have caught it, which is why rows read is worth measuring too.
         */
        long orderCount() {
            return store.countOrders();
        }
    }

    /* ── checks ─────────────────────────────────────────────────────────── */

    static final int ORDERS = 5_000;
    static final int ITEMS_PER_ORDER = 4;
    static final int PAGE = 20;

    public static void main(String[] args) {
        List<String> failures = new ArrayList<>();
        var store = new Store().seed(ORDERS, ITEMS_PER_ORDER);
        var service = new OrderService(store);

        // 1. A page of 20 must take a small, constant number of statements.
        store.reset();
        var page = service.listPage(PAGE);
        int listStatements = store.statements, listRows = store.rowsRead;
        if (page.size() != PAGE)
            failures.add("1. listPage returned " + page.size() + " views, expected " + PAGE);
        else if (listStatements > 2)
            failures.add("1. listPage took " + listStatements + " statements for " + PAGE
                       + " orders — a lazy collection is being read inside the loop");

        // 2. And it must not read the whole table to build that page.
        if (listRows > PAGE * (ITEMS_PER_ORDER + 1))
            failures.add("2. listPage read " + listRows + " rows to return " + PAGE
                       + " — a fetch join cannot be limited in SQL, so everything was read");

        // 3. The dashboard must not issue a statement per row.
        store.reset();
        service.dashboard(PAGE);
        if (store.statements > 2)
            failures.add("3. dashboard took " + store.statements + " statements for " + PAGE
                       + " rows — one query per row for the item count");

        // 4. A count must not materialise orders at all.
        store.reset();
        long count = service.orderCount();
        if (count != ORDERS)
            failures.add("4. orderCount returned " + count + ", expected " + ORDERS);
        else if (store.rowsRead > 1)
            failures.add("4. counting orders read " + store.rowsRead
                       + " rows — the database can answer this without returning them");

        /* ── report ─────────────────────────────────────────────────────── */
        if (failures.isEmpty()) {
            System.out.println("PASS");
        } else {
            failures.forEach(f -> System.out.println("  " + f));
            System.out.println("FAIL");
        }
    }
}