google/lovefield

dealing with nested queries

zcaudate opened this issue · 1 comments

I'm wondering if it's possible to get nested sql queries

like for example, if I needed to get a graph for the two tables below in postgres, this would be the query:

UserAccount
-> UserProfile

WITH j_ret AS (
  SELECT
    "id",
    "nickname",
    "password_updated",
    (
      WITH j_ret AS (
        SELECT
          "id",
          "first_name",
          "last_name",
        FROM "UserProfile"
        WHERE (
          "account_id" = "UserAccount"."id"
        )
      )
      SELECT jsonb_agg(j_ret) FROM j_ret
    ) AS "profile"
  FROM "UserAccount"
)
SELECT jsonb_agg(j_ret) FROM j_ret

Is there a way to do this with Lovefield?

Lovefield does not support subqueries. Instead, you can use an explicit transaction and perform multiple queries against the same transaction, see https://github.com/google/lovefield/blob/master/docs/spec/05_transaction.md#53-explicit-transactions.