You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
86 lines
2.1 KiB
JavaScript
86 lines
2.1 KiB
JavaScript
"use strict";
|
|
/* eslint-disable no-undef */
|
|
|
|
/*
|
|
SELECT
|
|
reviews.movie_id,
|
|
movie.title AS title,
|
|
COUNT(reviews.*) AS positive_review_count
|
|
FROM reviews
|
|
JOIN
|
|
movies AS movie ON reviews.movie_id = movie.id
|
|
WHERE
|
|
reviews.rating > 3
|
|
AND movie.title LIKE '%Movie%'
|
|
GROUP BY (reviews.movie_id, movie.id);
|
|
*/
|
|
query = select("reviews", [
|
|
define({ movie: belongsTo("movie_id") }),
|
|
where({
|
|
rating: moreThan(3),
|
|
movie: { title: includes("Movie") }
|
|
}),
|
|
collapseBy("movie_id", [
|
|
compute({ positive_review_count: count() }),
|
|
renameColumn("movie.title", "title")
|
|
])
|
|
]);
|
|
|
|
|
|
/*
|
|
SELECT
|
|
movies.*,
|
|
COUNT(reviews_1.*) AS positive_review_count
|
|
FROM movies
|
|
LEFT JOIN
|
|
reviews AS reviews_1 ON
|
|
movies.id = reviews_1.movie_id
|
|
AND reviews_1.rating > 3
|
|
GROUP BY movies.id;
|
|
|
|
NOTES:
|
|
- Must be a LEFT JOIN, to ensure that the rows from the parent table are always present
|
|
- Foreign predicates are in the ON, not the WHERE; that way they filter the foreign inputs, not the JOIN output?
|
|
MARKER: Continue fleshing out the mental model for all these different features, and how they translate to SQL. Also figure out where *non*-relation virtual tables fit into the picture, with aggregrates and such - especially whether they also require the primary-key GROUP BY.
|
|
*/
|
|
query = select("movies", [
|
|
compute({ // better name for withDerived?
|
|
positive_review_count: count(has("reviews.movie_id", [
|
|
where({ rating: moreThan(3) })
|
|
]))
|
|
})
|
|
]);
|
|
|
|
query = select("movies", [
|
|
define({ reviews_1: has("reviews.movie_id", [
|
|
where({ rating: moreThan(3) })
|
|
])}),
|
|
compute({ // better name for withDerived?
|
|
positive_review_count: count("reviews_1")
|
|
})
|
|
]);
|
|
|
|
|
|
/*
|
|
SELECT
|
|
movies.*,
|
|
COUNT(reviews_1.*) AS positive_review_count
|
|
FROM movies
|
|
JOIN
|
|
reviews AS reviews_1 ON movies.id = reviews_1.movie_id
|
|
WHERE reviews_1.rating > 3
|
|
GROUP BY movies.id;
|
|
*/
|
|
query = select("movies", [
|
|
define({
|
|
releases: has("releases.movie_id"),
|
|
positive_reviews: has("reviews.movie_id", [
|
|
where({ rating: moreThan(3) })
|
|
])
|
|
}),
|
|
compute({ // better name for withDerived?
|
|
positive_review_count: count("positive_reviews"),
|
|
initial_release_year: lowestOf("releases.year")
|
|
})
|
|
]);
|