Subquery

Contents

• How to write sub-queries?
  • What if destination is a custom named type?
  • What if we are not interested in all sub-query projections?
  • What if sub-query projection is not a table column?
• Lateral queries

How to write sub-queries?

Sub-queries are composed first:

// select film_id, title and rating from film table that have 'R' rating.
rRatingFilms := 
	SELECT(
		Film.FilmID,
		Film.Title,
		Film.Rating,
	).FROM(
		Film,
	).WHERE(
		Film.Rating.EQ(enum.MpaaRating.R),
	).AsTable("rFilms")

AsTable("rFilms") - allows SELECT statements to be used as a source table from FROM clause.

To use sub-query columns in main SELECT statement expressions, we have to export column from sub-query, using From method.

rFilmId := Film.FilmID.From(rRatingFilms)  // <- used for join condition

Now we can write:

query := SELECT(
		rRatingFilms.AllColumns(),
		Actor.AllColumns,
	).FROM(
		rRatingFilms.
			INNER_JOIN(FilmActor, FilmActor.FilmID.EQ(rFilmID)).
			INNER_JOIN(Actor, FilmActor.ActorID.EQ(Actor.ActorID)),
	)

rRatingFilms.AllColumns(), - all sub-query columns required for projection can be exported with AllColumns() method. The effect is the same as each of the sub-query columns are exported one by one using the From method.

Debug SQL of above example:

SELECT "rFilms"."film.film_id" AS "film.film_id",                -- <- the same alias names from sub-query
     "rFilms"."film.title" AS "film.title",
     "rFilms"."film.rating" AS "film.rating",
     actor.actor_id AS "actor.actor_id",
     actor.first_name AS "actor.first_name",
     actor.last_name AS "actor.last_name",
     actor.last_update AS "actor.last_update"
FROM (
          SELECT film.film_id AS "film.film_id",                -- <- the same alias names will appear in surrounding(main) query
               film.title AS "film.title",
               film.rating AS "film.rating"
          FROM dvds.film
          WHERE film.rating = 'R'
     ) AS "rFilms"
     INNER JOIN dvds.film_actor ON (film_actor.film_id = "rFilms"."film.film_id")
     INNER JOIN dvds.actor ON (film_actor.actor_id = actor.actor_id);

Note that column aliases are the same in main query as they are in the sub-query. This is because default column aliases are just passed from the sub-query to surrounding query, or in this case main query. Default column aliases are NOT affected with a sub-query alias(rFilms).

Since the sub-query column aliases, are default table alias in the main query, we can use the generated model types as the scan destination, without any modifications:

var dest []struct {
	model.Film             // <- data from sub-query

	Actors []model.Actor
}

err := stmt.Query(db, &dest)

The same logic applies as well for common table expression queries used in WITH statement or in other common table expression queries.

What if destination is a custom named type?

Let say destination for above sub-query is MyFilm struct now:

type MyFilm struct {
	Name     string             // <- !! there is no matching alias projection 
        Duration time.Duration      // <- !! 

	Actors   []model.Actor
}

var dest []MyFilm

Scan in this case would not work for Name and Duration destination fields, because there is no projection aliased as "my_film.name" or "my_film.duration". To fix the scan we can add alias tag in our destination or we can update alias in the main query:

query := SELECT(
		rRatingFilms.AllColumns().AS("my_film.*),  // will change alias of all sub-query projections
		Actor.AllColumns,
	).

Debug SQL now contains correctly aliased projections for scan to work:

SELECT "rFilms"."film.film_id" AS "my_film.film_id",       -- projections renamed from film.film_id to my_film.film_id
     "rFilms"."film.title" AS "my_film.title",
     "rFilms"."film.rating" AS "my_film.rating",
     ...

What if we are not interested in all sub-query projections?

The list of sub-query projections can be easily reduced using the Except method:

query := SELECT(
		rRatingFilms.AllColumns().Except(Film.Title, Film.Rating), 
		Actor.AllColumns,
                ...

film.title and film.rating will not appear in the generated SQL anymore:

SELECT "rFilms"."film.film_id" AS "film.film_id",             
     actor.actor_id AS "actor.actor_id",
     actor.first_name AS "actor.first_name",
     ...

What if sub-query projection is not a table column?

For instance:

customersPayments := SELECT(
        Payment.CustomerID,
        SUMf(Payment.Amount).AS("amount_sum"),
    ).FROM(
        Payment,
    ).
    GROUP_BY(Payment.CustomerID).
    AsTable("customer_payment_sum")

customerId := Payment.CustomerID.From(customersPayments)

To export "amount_sum" from customersPayments sub-query we have to create column first with appropriate type and a name. Since SUMf returns float expression, we will create FloatColumn named "amount_sum" and then export it from sub-query.

amountSum := FloatColumn("amount_sum").From(customersPayments)

This is only required if amount_sum needs to appear in main query conditions (e.g. as part of JOIN or WHERE). If this is not the case, amount_sum can be exported in the usual way, using AllColumns() method.

Lateral queries

Lateral queries have a similar syntax as sub-queries and can be used in a FROM clause in the same way as tables or sub-queries.

languages := LATERAL(
	SELECT(
		Language.AllColumns,
	).FROM(
		Language,
	).WHERE(
		Language.Name.NOT_IN(String("spanish")).
			AND(Film.LanguageID.EQ(Language.LanguageID)),   // Film.Language is from main statement (bellow)
	),
).AS("languages")

stmt := SELECT(
	Film.FilmID,
	Film.Title,
	languages.AllColumns(),
    ).FROM(
	Film, 
        languages,
    ).WHERE(
	Film.FilmID.EQ(Int(1))
    )