6 ms·
Can someone from EdgeDB explain why the SQL isn't as simple as what I have below? What am I missing? Why is that cross join lateral necessary: SELECT t
by nknealk 5y ago
Can someone from EdgeDB explain why the SQL isn't as simple as what I have below? What am I missing? Why is that cross join lateral necessary:
SELECT
title,
ARRAY_SLICE(ARRAY_AGG(movie_actors.name WITHIN GROUP (order by movie_actors.credits_order asc)),0,5)
avg(movie_reviews.score)
FROM movie
JOIN movie_actors on (movie.id = movie_actors.movie_id)
JOIN person on (movie_Actors.person_id = person.id)
JOIN movie_reviews on (movie.id = movie_reviews.id)
WHERE person.name like '%Zendaya%'
group by title
- RedCrowbar 5y agoBecause that only gives you actor names, not records, and also because arrays aren't a universal SQL feature.
- eatonphil 5y agoEdgeDB is already postgres specific though.
- RedCrowbar 5y agoEdgeDB currently is. Graph-relational and EdgeQL are not.
- SahAssar 5y agoUntil another db is graph-relational and can be queried via edgeql those are just as postgres-specific as arrays though, right?
- nicoburns 5y agoYou can JSON_AGG to get whole records.
- deleted 5y ago[deleted]
- RedCrowbar 5y agoUnfortunately, JSON aggregation destroys type information, so you can't reason about that your SQL query actually returns anymore.
- RedCrowbar 5y agoAlso, this query is wrong because we want movies where Zendaya played, but _also_ other actors in order, possibly without Zendaya, so you really need to do the actors join twice.
- deleted 5y ago[deleted]