Drizzle Team•16mo ago

Is it possible to do lateral sub-query join without relational queries?

I'm trying to rewrite the same query that the relation query builder does behind the scenes but not sure if it's possible If i log the query this is what i get

select "articles"."id", "articles"."title", "articles_keywords"."data" as "keywords" from "articles" 
left join lateral (select coalesce(json_agg(json_build_array("articles_keywords"."article_id", "articles_keywords"."keyword_value")), '[]'::json) 
as "data" from "article_keyword_association_table" "articles_keywords" 
where "articles_keywords"."article_id" = "articles"."id") "articles_keywords" on true 
where "articles"."id" in ('ec9ffb05-dfa0-455f-a71c-46ece3dc28a8', 'e56a9ae6-2373-4ad7-8d59-a76c7809d8ec')

select "articles"."id", "articles"."title", "articles_keywords"."data" as "keywords" from "articles" 
left join lateral (select coalesce(json_agg(json_build_array("articles_keywords"."article_id", "articles_keywords"."keyword_value")), '[]'::json) 
as "data" from "article_keyword_association_table" "articles_keywords" 
where "articles_keywords"."article_id" = "articles"."id") "articles_keywords" on true 
where "articles"."id" in ('ec9ffb05-dfa0-455f-a71c-46ece3dc28a8', 'e56a9ae6-2373-4ad7-8d59-a76c7809d8ec')

I would like to do the same logic with the query builder becauase the relational queries there are certain things i cant do

2 Replies

Angel•12mo ago

Angelelz•12mo ago

Totally doable. Can you share what you have so far?

Gaming

Programming

Is it possible to do lateral sub-query join without relational queries?