Complex Query Types

[theelderbeever]

I don't have all of these fully fleshed out in terms of implementation and whether they are really possible but, it would be really cool to seem some of the following for constructing complex queries down the road. Some of the examples are pretty simplistic so I can come up with more relevant uses cases if need be.

So this is all just a food for thought post...

Common Table Expression (CTE)

The Common Table Expression (CTE). Returns a querystring with internals wrapped in a WITH clause. The ability to define columns and their types would be useful as well so the selected values could be type cast

class CTE:
    base = "WITH {tablename} AS ({query})"
   
    def __init__(self, tablename: str, columns: t.Dict[str, Column], wrapped_query: QueryString):
        ...
# Implements All the common Table capabilities. 
cte = CTE("temp", {"name": VarChar}, "SELECT name from employees")
....

Query would look like

WITH temp AS (
  SELECT "name" FROM employees
)

VALUES

A VALUES list. These can be useful when crafting bulk UPDATE clauses, passing values to CTE's, or doing some really wild stuff like a Cartesian Product (pretty slow in SQL fyi). Its important to note though that everything gets cast directly to text when coming from a VALUES list so whatever wraps it would likely need to implement typecasting

class VALUES:
    base = "VALUES {values} 

Standard selection with VALUES. Being able to define typecasting the the python select delegate would be super valuable.

SELECT num::numeric, word::text FROM (
	VALUES
		(1, 'hello')
) t (num, word)

Cartesian Product just for fun

select
	num,
	letter
from
	(select * from (values (1),(2),(3)) as t (num)) nums,
	(select * from (values ('one'),('two'),('three')) as t (letter)) letters; 

Subquery

Basically just a query wrapped in parentheses to be shoved after a FROM

class SUBQUERY:
    base = "({query})"

Bulk Update

This can be done in Postgres and SQLite but they are pretty different. This can make a lot of use out of the preceding classes (CTE, SUBQUERY, and VALUES)

A final SQL statement might look something like this in Postgres

WITH temp AS (
	SELECT name::text, salary::INTEGER FROM (
		VALUES
			('Bob', 100000),
			('Mary', 150000),
	) t (name, salary)
)

UPDATE employees e
SET
	salary = t.salary
FROM temp t
WHERE e.name = t.name

This could also be rewritten as a subquery.

UPDATE employees e
SET
	salary = t.salary
FROM (
	SELECT name::text, salary::INTEGER FROM (
		VALUES
			('Bob', 100000),
			('Mary', 150000),
	) t (name, salary)
)
WHERE e.name = t.name

Both example would make use of the VALUES class though.

[dantownsend]

@theelderbeever There are lots of interesting ideas here.

I think a precursor to this is exposing the internal QueryString to the end users:

#406

That will help with composing complex queries.

inner_select = QueryString(
    """
    SELECT name::text, salary::INTEGER FROM (
        VALUES ({}, {})
    ) t (name, salary)
    """,
    'Bob',
    10000
)

query = QueryString(
    """
    UPDATE employees e
    SET
	salary = t.salary
    FROM ({}) WHERE e.name = t.name
    """,
    inner_select
)

As you say, we can create some custom helper classes which generate QueryStrings.

With the type casting, we could even add something like a cast=True argument to QueryString, so any values inside are explicitly cast.