Subquery

Contents

• How to write sub-queries?
• Lateral queries
• What if sub-query projection is not a column?

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 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,
		FilmActor.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",
     film_actor.actor_id AS "film_actor.actor_id",
     film_actor.film_id AS "film_actor.film_id",
     film_actor.last_update AS "film_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 model type?

Let say destination for above sub-query example is:

type MyFilm struct {
	Name     string             // <- !! Wrong alias
        Duration time.Duration      // <- !! Wrong alias

	Actors []model.Actor
}

var dest []MyFilm

Scan in this case would not work for Name and Duration destination field, 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.*),  // change alias of all projections from the sub-query
		Actor.AllColumns,
		FilmActor.AllColumns,
	).

Debug SQL now has correctly aliased projections 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",

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))
)

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

For instance:

customersPayments := Payment.
    SELECT(
        Payment.CustomerID,
        SUMf(Payment.Amount).AS("amount_sum"),
    ).
    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. Because SUMf produces float expression we will create FloatColumn with name of the column alias "amount_sum"

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