Subselect with `LEFT JOIN LATERAL` and `LIMIT 1`

[lukehutch]

Is your feature request related to a problem? Please describe.

I finally figured out how to describe the feature that I need that is missing from Serverpod query semantics. I keep hitting this requirement again and again, in many situations. I only figured out how to enunciate what I need after throwing away my query code and rewriting it all in terms of raw SQL queries. So here is the scenario:

• I have a standard single-table query I want to execute, returning a list of result rows. (Serverpod's query syntax works great for this right now.)
• For every row in the result list, I need to left-join with another table, pulling in at most one result row from the other table.
  • The purpose of this is to look up adjunct information that is not in the main (left-hand) table, but is important to have in the query results -- and I don't want to do Postgres API roundtrips multiple times to run these sub-queries.
  • Additionally, I want the main query (of the left-hand table) to be filtered upon some of the values brought in from the right-hand table, and I don't want to have to manually implement filtering once all these separate queries come back.
• I don't want to use a 1:1 entity relationship, because:
  • sometimes the relationship can be 1:0;
  • the right table in the join may not naturally have any sort of parent/child relationship with the left table;
  • and most importantly, I don't want to join on just one column, as is implemented in the current entity relationship system in Serverpod for the 1:1 and 1:n relationships, because I need to be able to dynamically specify the join using a WHERE clause, which may include matching conditions for more than one column.
• Sometimes I need to be able to join with the same table more than once, following the above pattern, in a single query. This means that the columns need to be aliased. (I cover this issue in #1563.)

In other words, I need the ability to do a LEFT JOIN against a sub-select of the right-hand table, also imposing LIMIT 1. The LIMIT 1 is important, because I don't want to duplicate rows on the left side of the join in the case where the right side of the join inadvertently returns 2 or more rows.

Furthermore, I need to be able to refer to fields in the result row on the left side of the join, when querying columns in the right-hand table, and vice versa. Postgres has a relatively new way to do this very efficiently: a LATERAL join.

So the pattern I need will be something like the following in SQL:

select *, rt.*
  from left_table lt
    left join lateral (
      select * from right_table
      where [... `where` conditions joining `rt` to `lt`, which can make use of columns of `lt` row ...]
      limit 1
    ) rt on true
    where [... `where` conditions selecting `lt` row, which may depend upon `rt` columns also ...]

So maybe the yaml syntax could flag this kind of situation using something like lookup:

class: LeftTable
table: left_table
fields:
  rightTable: RightTable?, lookup
  ...

and then for each lookup type, the query syntax would add a ...Where function:

LeftTable.db.find(session,
  where: (lt, rt) => /* `where` conditions selecting from left columns `lt`; may also match right columns `rt` */,
  rightTableWhere: (lt, rt) =>
    /* `where` conditions selecting from right columns `rt`; may match left columns `lt` */,
);

The result would be a class LeftTable with its usual fields, and an additional field rightTable that has type RightTable?, so that zero or one of these matching RightTable rows can be obtained through each row of LeftTable.

If instead of rightTable: RightTable?, lookup, the yaml line said rightTable: RightTable, lookup, then if the sub-select returns zero rows, an exception should be thrown (since the returned sub-select record would be null).

For tables containing one or more lookup fields, the main where: expression lambda needs to take one parameter for the left-hand table (lt in this example), and one parameter for each joined right-hand table (rt in this example, which has only one join). So if there are 3 lookup joins of this type, where: will take 4 params. Each of the sub-select where-expression lambdas (rightTableWhere: in the above example) needs to take exactly two parameters: one for the left table (lt), giving access to the outer query's columns, and one for the sub-select table itself (rt). This is probably going to require some refactoring, because I assume all the TableName.db objects have the same API currently.

The Expression API needs to have .equalsColumn and .notEqualsColumn methods added, so that columns can be compared to each other in a join. Currently you can only compare a column with a value.

Supporting left lateral joins is very important for scalability, because there is no faster way to join a right-hand table to every row of the result set from a left-hand table. It is dramatically faster than launching a new query against the right-hand table for every result row of the left-hand table (which is what I have to do right now with the current API).

@SandPod hopefully that gives more clarity to the discussions we have had so far!

[lukehutch]

Here is some code I hacked together to build these sorts of queries (not intended for incorporation into Serverpod, but just to demonstrate what I need):

class ColumnsAreNotDistinctExpression<L, R> extends Expression {
  final Column<L> leftColumn;
  final Column<R> rightColumn;

  ColumnsAreNotDistinctExpression(
    this.leftColumn,
    this.rightColumn,
  ) : super(leftColumn);

  @override
  List<Column> get columns => [leftColumn, rightColumn];

  @override
  String toString() {
    return '"${leftColumn.table.queryPrefix}"."${leftColumn.columnName}" '
        ' IS NOT DISTINCT FROM '
        '"${rightColumn.table.queryPrefix}"."${rightColumn.columnName}"';
  }
}

class RightTableSubQuery {
  final Table rightTable;
  final String alias;
  final Expression Function(Table leftTable, Table rightTable) where;

  RightTableSubQuery({
    required this.rightTable,
    required this.alias,
    required this.where,
  });
}

class LateralJoinQuery {
  final Table leftTable;
  final List<RightTableSubQuery> rightTableSubQueries;
  final Expression Function(Table leftTable, List<Table> rightTables) where;
  int? limit;
  int? offset;

  LateralJoinQuery({
    required this.leftTable,
    required this.rightTableSubQueries,
    required this.where,
    this.limit,
    this.offset,
  }) : assert(rightTableSubQueries.isNotEmpty);

  String query() => '''     
      SELECT *, ${[
        for (var rightTableSubQuery in rightTableSubQueries)
          for (var rightTableColumn in rightTableSubQuery.rightTable.columns)
            '"${rightTableSubQuery.alias}"."${rightTableColumn.columnName}" '
                '"${rightTableSubQuery.alias}.${rightTableColumn.columnName}"'
      ].join(', ')}
      FROM "${leftTable.tableName}"
      ${[
        for (var rightTableSubQuery in rightTableSubQueries)
          '''
            LEFT JOIN LATERAL (
              SELECT *
              FROM "${rightTableSubQuery.rightTable.tableName}"
                "${rightTableSubQuery.alias}"
              WHERE ${rightTableSubQuery.where( //
              leftTable, rightTableSubQuery.rightTable)}
              LIMIT 1
            ) AS ${rightTableSubQuery.alias} ON TRUE
        '''
      ].join('\n')}
      WHERE ${where(leftTable, //
          rightTableSubQueries.map((r) => r.rightTable).toList())}
      ${limit == null ? '' : 'LIMIT $limit'}
      ${offset == null ? '' : 'OFFSET $offset'}
    ''';

  LeftTableType deserializeLeftTable<LeftTableType>(
      Session session, Map<String, Map<String, dynamic>> resultRowMap) {
    return session.serverpod.serializationManager
        .deserialize<LeftTableType>(resultRowMap[leftTable.tableName]!);
  }

  RightTableType? deserializeRightTable<RightTableType>(Session session,
      Map<String, Map<String, dynamic>> resultRowMap, int rightTableIdx) {
    final rightTableSubQuery = rightTableSubQueries[rightTableIdx];
    final aliasedMap = resultRowMap[rightTableSubQuery.rightTable.tableName]!;
    // Strip the alias prefix from the map keys (needed in case the same
    // table is joined multiple times as a right table)
    final unaliasedMap = <String, dynamic>{};
    bool hasNonNullValue = false;
    for (final column in rightTableSubQuery.rightTable.columns) {
      String key = column.columnName;
      dynamic value =
          aliasedMap['${rightTableSubQuery.alias}.${column.columnName}'];
      if (value != null) {
        hasNonNullValue = true;
      }
      unaliasedMap[key] = value;
    }
    return hasNonNullValue
        ? session.serverpod.serializationManager
            .deserialize<RightTableType>(unaliasedMap)
        : null;
  }
}

Here is how it is called:

// I had to monkey-patch these tables because the `queryPrefix` can't be overridden on a case-by-case basis

// Give UserActionTable an alias for outgoing actions
class OutgoingUserActionTable extends UserActionTable {
  @override
  String get queryPrefix => 'action_outgoing';
}

// Give UserActionTable an alias for incoming actions
class IncomingUserActionTable extends UserActionTable {
  @override
  String get queryPrefix => 'action_incoming';
}

// The general calling pattern:

    final lateralJoinQuery = LateralJoinQuery(
      leftTable: Profile.t,
      rightTableSubQueries: [
        RightTableSubQuery(
          rightTable: Account.t,
          alias: 'account',
          where: (leftTable, rightTable) => ColumnsAreNotDistinctExpression(
            (leftTable as ProfileTable).userId,
            (rightTable as AccountTable).userId,
          ),
        ),
        RightTableSubQuery(
          rightTable: IncomingUserActionTable(),
          alias: 'action_incoming',
          where: (leftTable, rightTable) =>
              ColumnsAreNotDistinctExpression(
                (rightTable as UserActionTable).fromUserId,
                (leftTable as ProfileTable).userId,
              ) &
              rightTable.toUserId.equals(viewerUserId),
        ),
        RightTableSubQuery(
          rightTable: OutgoingUserActionTable(),
          alias: 'action_outgoing',
          where: (leftTable, rightTable) =>
              (rightTable as UserActionTable).fromUserId.equals(viewerUserId) &
              ColumnsAreNotDistinctExpression(
                rightTable.toUserId,
                (leftTable as ProfileTable).userId,
              ),
        ),
      ],
      where: (leftTable, rightTables) =>
          (leftTable as ProfileTable).userId.equals(profileUserId) &
          ((rightTables[1] as UserActionTable)
                  .likeActionType
                  .notEquals(LikeActionType.block) &
              (rightTables[2] as UserActionTable)
                  .likeActionType
                  .notEquals(LikeActionType.block)),
    );
    final queryStr = lateralJoinQuery.query();

This builds the following SQL query:

SELECT 
  *, 
  "account"."id" "account.id", 
  "account"."userId" "account.userId", 
  "account"."name" "account.name", 
  "account"."gender" "account.gender", 
  "account"."accountCreatedDate" "account.accountCreatedDate", 
  "account"."accountStatus" "account.accountStatus", 
  "action_incoming"."id" "action_incoming.id", 
  "action_incoming"."fromUserId" "action_incoming.fromUserId", 
  "action_incoming"."toUserId" "action_incoming.toUserId", 
  "action_incoming"."likeActionType" "action_incoming.likeActionType", 
  "action_incoming"."likeActionDate" "action_incoming.likeActionDate", 
  "action_incoming"."starActionType" "action_incoming.starActionType", 
  "action_incoming"."starActionDate" "action_incoming.starActionDate", 
  "action_outgoing"."id" "action_outgoing.id", 
  "action_outgoing"."fromUserId" "action_outgoing.fromUserId", 
  "action_outgoing"."toUserId" "action_outgoing.toUserId", 
  "action_outgoing"."likeActionType" "action_outgoing.likeActionType", 
  "action_outgoing"."likeActionDate" "action_outgoing.likeActionDate", 
  "action_outgoing"."starActionType" "action_outgoing.starActionType", 
  "action_outgoing"."starActionDate" "action_outgoing.starActionDate" 
FROM 
  "profile" 
  LEFT JOIN LATERAL (
    SELECT 
      * 
    FROM 
      "account" "account" 
    WHERE 
      "profile"."userId" IS NOT DISTINCT 
    FROM 
      "account"."userId" 
    LIMIT 
      1
  ) AS account ON TRUE 
  LEFT JOIN LATERAL (
    SELECT 
      * 
    FROM 
      "user_action" "action_incoming" 
    WHERE 
      (
        "action_incoming"."fromUserId" IS NOT DISTINCT 
        FROM 
          "profile"."userId" 
          AND "action_incoming"."toUserId" = 51
      ) 
    LIMIT 
      1
  ) AS action_incoming ON TRUE 
  LEFT JOIN LATERAL (
    SELECT 
      * 
    FROM 
      "user_action" "action_outgoing" 
    WHERE 
      (
        "action_outgoing"."fromUserId" = 51 
        AND "action_outgoing"."toUserId" IS NOT DISTINCT 
        FROM 
          "profile"."userId"
      ) 
    LIMIT 
      1
  ) AS action_outgoing ON TRUE 
WHERE 
  (
    "profile"."userId" = 51 
    AND (
      "action_incoming"."likeActionType" IS DISTINCT 
      FROM 
        'block' 
        AND "action_outgoing"."likeActionType" IS DISTINCT 
      FROM 
        'block'
    )
  )

The query is executed and the joined rows are recovered as follows:

    final queryResults = await session.dbNext
        .unsafeQueryMappedResults(session, queryStr);
    final profileResultRow = lateralJoinQuery.deserializeLeftTable<Profile>(
        session, queryResults[0]);
    final accountResultRow = lateralJoinQuery.deserializeRightTable<Account>(
        session, queryResults[0], 0);
    final incomingActionResultRow = lateralJoinQuery
        .deserializeRightTable<UserAction>(session, queryResults[0], 1);
    final outgoingActionResultRow = lateralJoinQuery
        .deserializeRightTable<UserAction>(session, queryResults[0], 2);

The query definition system and the result parsing system is a bit of a mess, but this is the best that can probably be done right now.

[SandPod]

Hey! Thank for the longer explanation with clear descriptions of what you are after. Feels like a got a much better picture now.

If I understood your description correctly I identified 2 features that the relational query system currently does not support and 1 additional query feature from your SQL examples.

1. Foreign key relations on multiple columns.
2. Column equality in where expression (probably utilizing the LATERAL keyword since a subquery for every row would then be required).
3. IS DISTINCT column keyword in query.

Some of the requirements that you specified are already supported in the relational query system given that the entity relations are specified:

• Pulling at most a single row from a 1:n relation - This is achieved by simply applying limit 1 to the includeList object.
• Having no related entity in a 1:1 relation - This can be achieve by making the related id optional.
• Filtering main query based on relation row attributes - This is achieved by following the object relation in the query and applying filtering as required.

Note that the LATERAL join executes a subquery for each row. The system we have built right now, with the current limitation of not supporting Column equality, allows us to always run our subqueries once and then use that result for all rows in the main query. I don't like to talk performance without having concrete numbers (because reality is often surprising) but I have a strong hunch that Column equality feature is going to come with a strong performance implication, especially when working with 2 large tables.

[lukehutch]

I don't like to talk performance without having concrete numbers (because reality is often surprising) but I have a strong hunch that Column equality feature is going to come with a strong performance implication, especially when working with 2 large tables.

I don't see the reason why -- when you have two indexed columns, and you join between them, Postgres does a hash-match between the query indices. It is very fast.

lateral has some smart index utilization logic that can dramatically speed up the sub-queries:

https://stackoverflow.com/a/49460987/3950982

The system we have built right now, with the current limitation of not supporting Column equality, allows us to always run our subqueries once and then use that result for all rows in the main query.

This is presumably because your sub-query rows don't depend upon the outer query rows? (The need for a dependency between these is the whole reason the lateral query type exists...)

You caught most of the important points, I think, although also:

• I need to be able to join the same right-hand table more than once to the left-hand table (in this case, for incoming and outgoing user actions). The current entity system doesn't allow this.
  • This is reflected in the fact that the Table class has only a fixed queryPrefix (which is why I had to monkey-patch the Table subclass with two different subclasses, so that I could alias the table name prefix of the columns in the two different joins).
• I don't want to run sub-queries in separate queries, like you do currently for entity relationships. That dramatically increases the cost of a query, because:
  • multiple database round-trips are required
  • you lose the performance benefits you get with a lateral query
  • join logic can't all be made part of the same query, because the right-hand result row can cause the left-hand result row to be filtered out -- like I mentioned (and you pointed out), I need to be able to add right-hand table columns to left-hand table where clauses, AND left-hand table columns to right-hand table where clauses. But I don't want the right-to-left filtering to have to be manually applied, almost retrofitted, after the separate sub-query is executed.
• Therefore, because I don't want to run sub-queries in separate queries, automatically adding limit 1 to the sub-query in a lateral join (to prevent the chance of row duplication due to the Cartesian product) is not the same thing as adding .limit(1) to the sub-query in the current system.
• Also with the current system, a sub-query with .limit(1) is still returned as a List<T>. I want T?.

My biggest concerns with the current system are with performance, and therefore scalability. This can all be done in a single query, highly optimized by Postgres' query planner -- so why wouldn't I want to do it that way?

[lukehutch]

I updated my hacky solution to solve several issues. The main thing to notice is that the right table subquery now specifies whereRight, which is the right-hand table (subquery) WHERE clause, and whereLeft, which is a term added to the left-hand table's WHERE clause. This means both of these take exactly two parameters: the left table reference, and the right table reference. Both the inner (right-hand) and outer (left-hand) WHERE clauses can depend upon columns of either the left-hand table or one of the right-hand tables.

// ignore_for_file: public_member_api_docs, sort_constructors_first
import 'package:serverpod/serverpod.dart';

class ColumnsEqualExpression<L extends Column, R extends Column>
    extends Expression {
  final L leftColumn;
  final R rightColumn;

  ColumnsEqualExpression(
    this.leftColumn,
    this.rightColumn,
  ) : super(leftColumn);

  @override
  List<Column> get columns => [leftColumn, rightColumn];

  @override
  String toString() {
    return '"${leftColumn.table.queryPrefix}"."${leftColumn.columnName}" '
        ' = '
        '"${rightColumn.table.queryPrefix}"."${rightColumn.columnName}"';
  }
}

class RightTableSubQuery<L extends Table, R extends Table> {
  final R rightTable;

  /// WHERE-clause to possibly filter out the row from the right table.
  final Expression Function(L leftTable, R rightTable) whereRight;

  /// WHERE-clause to possibly filter out row from the left table,
  /// based on the row from the right table.
  final Expression Function(L leftTable, R rightTable)? whereLeft;

  /// Needed to solve type covariance problem
  Expression _whereRightAdapter(L leftTable, Table rightTable) =>
      whereRight(leftTable, rightTable as R);

  /// Needed to solve type covariance problem
  Expression? _whereLeftAdapter(L leftTable, Table rightTable) =>
      whereLeft?.call(leftTable, rightTable as R);

  RightTableSubQuery({
    required this.rightTable,
    required this.whereRight,
    this.whereLeft,
  });
}

class LateralJoinQuery<L extends Table> {
  final L leftTable;
  final List<RightTableSubQuery<L, Table>> rightTableSubQueries;
  final Expression Function(L leftTable) where;
  int? limit;
  int? offset;

  LateralJoinQuery({
    required this.leftTable,
    required this.rightTableSubQueries,
    required this.where,
    this.limit,
    this.offset,
  }) : assert(rightTableSubQueries.isNotEmpty);

  String query() => '''     
      SELECT *, ${[
        for (var q in rightTableSubQueries)
          for (var c in q.rightTable.columns)
            '"${q.rightTable.queryPrefix}".'
                '"${c.columnName}" '
                '"${q.rightTable.queryPrefix}.'
                '${c.columnName}"'
      ].join(', ')}
      FROM "${leftTable.tableName}"
      ${[
        for (var r in rightTableSubQueries)
          '''
            LEFT JOIN LATERAL (
              SELECT *
              FROM "${r.rightTable.tableName}"
                "${r.rightTable.queryPrefix}"
              WHERE ${r._whereRightAdapter(leftTable, r.rightTable)}
              LIMIT 1
            ) AS ${r.rightTable.queryPrefix} ON TRUE
        '''
      ].join('\n')}
      WHERE ${where(leftTable)}
        ${rightTableSubQueries.map((r) => //
              r._whereLeftAdapter(leftTable, r.rightTable)) //
          .nonNulls //
          .map((w) => 'and $w').join(' ')}
      ${limit == null ? '' : 'LIMIT $limit'}
      ${offset == null ? '' : 'OFFSET $offset'}
    ''';

  LeftTableType? deserializeLeftTable<LeftTableType>(
      Session session, Map<String, Map<String, dynamic>> resultRowMap) {
    final row = resultRowMap[leftTable.tableName];
    return row == null
        ? null
        : session.serverpod.serializationManager
            .deserialize<LeftTableType>(row);
  }

  RightTableType? deserializeRightTable<RightTableType>(Session session,
      Map<String, Map<String, dynamic>> resultRowMap, int rightTableIdx) {
    final rightTableSubQuery = rightTableSubQueries[rightTableIdx];
    final aliasedMap = resultRowMap[rightTableSubQuery.rightTable.tableName];
    if (aliasedMap == null) {
      return null;
    }
    // Strip the alias prefix from the map keys (needed in case the same
    // table is joined multiple times as a right table)
    final unaliasedMap = <String, dynamic>{};
    bool hasNonNullValue = false;
    for (final column in rightTableSubQuery.rightTable.columns) {
      String key = column.columnName;
      dynamic value = aliasedMap[
          '${rightTableSubQuery.rightTable.queryPrefix}.${column.columnName}'];
      if (value != null) {
        hasNonNullValue = true;
      }
      unaliasedMap[key] = value;
    }
    return hasNonNullValue
        ? session.serverpod.serializationManager
            .deserialize<RightTableType>(unaliasedMap)
        : null;
  }
}

Example of calling this:

// Give tables that are joined more than once a query prefix

class ProfileAccountTable extends AccountTable {
  @override
  String get queryPrefix => 'account_profile';
}

class ViewerAccountTable extends AccountTable {
  @override
  String get queryPrefix => 'account_viewer';
}

class OutgoingUserActionTable extends UserActionTable {
  @override
  String get queryPrefix => 'action_outgoing';
}

class IncomingUserActionTable extends UserActionTable {
  @override
  String get queryPrefix => 'action_incoming';
}

// ...

  final lateralJoinQuery = LateralJoinQuery<ProfileTable>(
    leftTable: Profile.t,
    rightTableSubQueries: [
      RightTableSubQuery<ProfileTable, ProfileAccountTable>(
        rightTable: ProfileAccountTable(),
        whereRight: (profileTable, profileAccountTable) =>
            // Find account matching profile on userId
            ColumnsEqualExpression(
                profileTable.userId, profileAccountTable.userId),
        whereLeft: (profileTable, profileAccountTable) =>
            // Don't return anything if profile account is not active
            profileAccountTable.accountStatus.equals(AccountStatus.active),
      ),
      RightTableSubQuery<ProfileTable, ViewerAccountTable>(
        rightTable: ViewerAccountTable(),
        whereRight: (profileTable, viewerAccountTable) =>
            // Find account with viewer's userId
            viewerAccountTable.userId.equals(viewerUserId),
        whereLeft: (profileTable, viewerAccountTable) =>
            // Don't return anything if viewer  account is not active
            viewerAccountTable.accountStatus.equals(AccountStatus.active),
      ),
      RightTableSubQuery<ProfileTable, IncomingUserActionTable>(
        rightTable: IncomingUserActionTable(),
        whereRight: (profileTable, incomingUserActionTable) =>
            // Find UserAction from profile userId to viewer userId
            ColumnsEqualExpression(
                incomingUserActionTable.fromUserId, profileTable.userId) &
            incomingUserActionTable.toUserId.equals(viewerUserId),
        whereLeft: (profileTable, incomingUserActionTable) =>
            // Don't return anything if there's an incoming block action
            incomingUserActionTable.likeActionType
                .notEquals(LikeActionType.block),
      ),
      RightTableSubQuery<ProfileTable, OutgoingUserActionTable>(
        rightTable: OutgoingUserActionTable(),
        whereRight: (profileTable, outgoingUserActionTable) =>
            // Find UserAction from viewer userId to profile userId
            outgoingUserActionTable.fromUserId.equals(viewerUserId) &
            ColumnsEqualExpression(
                outgoingUserActionTable.toUserId, profileTable.userId),
        whereLeft: (profileTable, outgoingUserActionTable) =>
            // Don't return anything if there's an outgoing block action
            outgoingUserActionTable.likeActionType
                .notEquals(LikeActionType.block),
      ),
    ],
    where: (leftTable) => leftTable.userId.equals(profileUserId),
  );
  final profileResultsList = await session.dbNext
      .unsafeQueryMappedResults(session, lateralJoinQuery.query());

The generated query:

SELECT 
  *, 
  "account_profile"."id" "account_profile.id", 
  "account_profile"."userId" "account_profile.userId", 
  "account_profile"."name" "account_profile.name", 
  "account_profile"."gender" "account_profile.gender", 
  "account_profile"."religion" "account_profile.religion", 
  "account_profile"."accountCreatedDate" "account_profile.accountCreatedDate", 
  "account_profile"."accountStatus" "account_profile.accountStatus", 
  "account_profile"."accountStatusChangedDate" "account_profile.accountStatusChangedDate", 
  "account_profile"."datingIsEnabled" "account_profile.datingIsEnabled", 
  "account_profile"."isSubscriber" "account_profile.isSubscriber", 
  "account_profile"."subscriptionExpires" "account_profile.subscriptionExpires", 
  "account_profile"."vipStatus" "account_profile.vipStatus", 
  "account_profile"."vipStatusModified" "account_profile.vipStatusModified", 
  "account_profile"."dob" "account_profile.dob", 
  "account_profile"."thumbnailUrlSquare" "account_profile.thumbnailUrlSquare", 
  "account_profile"."thumbnailUrlPortrait" "account_profile.thumbnailUrlPortrait", 
  "account_viewer"."id" "account_viewer.id", 
  "account_viewer"."userId" "account_viewer.userId", 
  "account_viewer"."name" "account_viewer.name", 
  "account_viewer"."gender" "account_viewer.gender", 
  "account_viewer"."religion" "account_viewer.religion", 
  "account_viewer"."accountCreatedDate" "account_viewer.accountCreatedDate", 
  "account_viewer"."accountStatus" "account_viewer.accountStatus", 
  "account_viewer"."accountStatusChangedDate" "account_viewer.accountStatusChangedDate", 
  "account_viewer"."datingIsEnabled" "account_viewer.datingIsEnabled", 
  "account_viewer"."isSubscriber" "account_viewer.isSubscriber", 
  "account_viewer"."subscriptionExpires" "account_viewer.subscriptionExpires", 
  "account_viewer"."vipStatus" "account_viewer.vipStatus", 
  "account_viewer"."vipStatusModified" "account_viewer.vipStatusModified", 
  "account_viewer"."dob" "account_viewer.dob", 
  "account_viewer"."thumbnailUrlSquare" "account_viewer.thumbnailUrlSquare", 
  "account_viewer"."thumbnailUrlPortrait" "account_viewer.thumbnailUrlPortrait", 
  "action_incoming"."id" "action_incoming.id", 
  "action_incoming"."fromUserId" "action_incoming.fromUserId", 
  "action_incoming"."toUserId" "action_incoming.toUserId", 
  "action_incoming"."likeActionType" "action_incoming.likeActionType", 
  "action_incoming"."likeActionDate" "action_incoming.likeActionDate", 
  "action_incoming"."starActionType" "action_incoming.starActionType", 
  "action_incoming"."starActionDate" "action_incoming.starActionDate", 
  "action_outgoing"."id" "action_outgoing.id", 
  "action_outgoing"."fromUserId" "action_outgoing.fromUserId", 
  "action_outgoing"."toUserId" "action_outgoing.toUserId", 
  "action_outgoing"."likeActionType" "action_outgoing.likeActionType", 
  "action_outgoing"."likeActionDate" "action_outgoing.likeActionDate", 
  "action_outgoing"."starActionType" "action_outgoing.starActionType", 
  "action_outgoing"."starActionDate" "action_outgoing.starActionDate" 
FROM 
  "profile" 
  LEFT JOIN LATERAL (
    SELECT 
      * 
    FROM 
      "account" "account_profile" 
    WHERE 
      "profile"."userId" = "account_profile"."userId" 
    LIMIT 
      1
  ) AS account_profile ON TRUE 
  LEFT JOIN LATERAL (
    SELECT 
      * 
    FROM 
      "account" "account_viewer" 
    WHERE 
      "account_viewer"."userId" = 51 
    LIMIT 
      1
  ) AS account_viewer ON TRUE 
  LEFT JOIN LATERAL (
    SELECT 
      * 
    FROM 
      "user_action" "action_incoming" 
    WHERE 
      (
        "action_incoming"."fromUserId" = "profile"."userId" 
        AND "action_incoming"."toUserId" = 51
      ) 
    LIMIT 
      1
  ) AS action_incoming ON TRUE 
  LEFT JOIN LATERAL (
    SELECT 
      * 
    FROM 
      "user_action" "action_outgoing" 
    WHERE 
      (
        "action_outgoing"."fromUserId" = 51 
        AND "action_outgoing"."toUserId" = "profile"."userId"
      ) 
    LIMIT 
      1
  ) AS action_outgoing ON TRUE 
WHERE 
  "profile"."userId" = 13 
  and "account_profile"."accountStatus" = 'active' 
  and "account_viewer"."accountStatus" = 'active' 
  and "action_incoming"."likeActionType" IS DISTINCT 
FROM 
  'block' 
  and "action_outgoing"."likeActionType" IS DISTINCT 
FROM 
  'block'

Extracting the results:

  if (profileResultsList.isEmpty) {
  } else if (profileResultsList.length > 1) {
    throw 'Got too many profile results';
  }
  final profileResultFirst = profileResultsList.first;
  final profile = lateralJoinQuery.deserializeLeftTable<Profile>(
      session, profileResultFirst);
  final profileAccount = lateralJoinQuery.deserializeRightTable<Account>(
      session, profileResultFirst, 0);
  final viewerAccount = lateralJoinQuery.deserializeRightTable<Account>(
      session, profileResultFirst, 1);
  final incomingAction = lateralJoinQuery.deserializeRightTable<UserAction>(
      session, profileResultFirst, 2);
  final outgoingAction = lateralJoinQuery.deserializeRightTable<UserAction>(
      session, profileResultFirst, 3);

[SandPod]

This is presumably because your sub-query rows don't depend upon the outer query rows?

Exactly, the limitation allows us to do that type of optimization. If it is worth the tradeoff is another question.

I need to be able to join the same right-hand table more than once to the left-hand table (in this case, for incoming and outgoing user actions). The current entity system doesn't allow this.

We actually do support that. You can filter as many times as you wish on the same relation or towards the same table in multiple relations and we will handle it. But if you would like to include the many relation result data multiple times (say for example a company has a list of "employees" and a list of "pastEmployees" and both are "users" and you would like to retrieve both for each company) these need to be modeled as different relations.

class: Company
table: company
fields:
  name: String
  employees: List<User>?, relation
  pastEmployees: List<User>?, relation

I don't want to run sub-queries in separate queries, like you do currently for entity relationships.

I think there is a misunderstanding here, we don't run sub-queries as separate query. They are included as a single query to the database. That means that you can filter your result as much as your want and that will only be a single query, regardless of if your are working with 1:1 or 1:n relations.

We do however run a separate query if you would like to include relational 1:n data in the result. This seems to be a shared default practice with most ORMs; Django, Ruby on Rails.

Also with the current system, a sub-query with .limit(1) is still returned as a List. I want T?.

The system simply treats the data as it is modelled. Since there is a 1:n relation, that object representation then becomes a List. If it was a 1:1 relation then it would treat it as a single object. The source of truth for how deserialization should be made is the protocol files.

There will always be cases when specialized SQL queries will yield better query performance than what can be achieved by a generalized ORM implementation. Together, we have however identified 3 specific features we don't support in our current typed system.

[lukehutch]

I think there is a misunderstanding here, we don't run sub-queries as separate query. They are included as a single query to the database. That means that you can filter your result as much as your want and that will only be a single query, regardless of if your are working with 1:1 or 1:n relations.

How does this work for 1:n relations? You will end up with a Cartesian product, so every row in the outer table will be duplicated for every row in the inner table. Do you just de-dupe? If so, this seems very inefficient!

There will always be cases when specialized SQL queries will yield better query performance than what can be achieved by a generalized ORM implementation. Together, we have however identified 3 specific features we don't support in our current typed system.

Yes but basic joins are hardly "specialized SQL queries"... I need direct access to joins :-/

Although for my usecase, I don't need every form of join, I just need outer lateral joins, 1:[0/1].

[SandPod]

How does this work for 1:n relations? You will end up with a Cartesian product, so every row in the outer table will be duplicated for every row in the inner table. Do you just de-dupe? If so, this seems very inefficient!

Since we don't support the column equals operation we can actually perform this as a single CTE subquery for all rows (that is part of the same main query). And for every filtering operation that is isolated we can do it as a separate CTE subquery.

I would suggest playing around with the existing relational query system and inspecting the queries using Insights for a better understanding on how we build them. Introduction for how to inspect it was posted here: https://github.com/serverpod/serverpod/issues/1145#issuecomment-1744502264

Yes but basic joins are hardly "specialized SQL queries"... I need direct access to joins :-/

Yes, we don't provide support for the join keyword to build them on the fly (YET). That is a bit of a different direction than the current ORM is built upon so in those cases we still refer to the raw SQL queries. But when the entities are modelled so that the generator can derive that there is a relation we do support all form of join operations in our typed ORM.

Here is an endpoint that runs through a couple of scenarios for a 1:1 relation: https://github.com/serverpod/serverpod/blob/main/tests/serverpod_test_server/lib/src/endpoints/entity_relation.dart The entities for the endpoint are described here: https://github.com/serverpod/serverpod/tree/main/tests/serverpod_test_server/lib/src/protocol/entities_with_relations/one_to_one

Until our documentation is complete, there is a full test suite that runs through the different type of join operations we support that can be inspected here with the model definitions here.