ElectricDragon
04/21/2022, 11:15 AMScott P
04/21/2022, 4:52 PMsql
CREATE OR REPLACE FUNCTION get_chapter_likes(
chapter int
)
RETURNS int
AS $BODY$
BEGIN
RETURN (
SELECT sum(get_chapter_likes(chapter))
FROM public.chapter_likes
WHERE chapter.seriesid = series_id
);
END $BODY$
LANGUAGE plpgsql;
I'm not sure what type chapter
parameter is - you'll need to change the definition in the params section (line 2) if it's not an intsilentworks
04/21/2022, 5:55 PMtourdownunder
04/23/2022, 1:54 AMsql
select c.seriesid, count(*)
From chapter_likes cl
inner join chapters c ON cl.chapter = c.id
group by c.seriesid;
I just edited the minimum repeated example below to add temp_
prefix to all tables.
sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE IF NOT EXISTS public.temp_chapters
(
title character varying(25) COLLATE pg_catalog."default" DEFAULT 'Untitled'::character varying,
id uuid NOT NULL DEFAULT uuid_generate_v4(),
body jsonb,
images text[] COLLATE pg_catalog."default",
seriesid uuid NOT NULL,
chapternum integer NOT NULL,
is_published boolean NOT NULL DEFAULT false,
createdat timestamp without time zone NOT NULL DEFAULT now(),
totalreads integer NOT NULL DEFAULT 0,
CONSTRAINT temp_chapters_pkey PRIMARY KEY (id)
-- CONSTRAINT chapters_seriesid_fkey FOREIGN KEY (seriesid)
-- REFERENCES public.series (id) MATCH SIMPLE
-- ON UPDATE NO ACTION
-- ON DELETE NO ACTION
);
insert into public.temp_chapters(
title,
id,
body,
images,
seriesid,
chapternum,
is_published
-- createdat,
-- totalreads
)
VALUES (
'abc',
uuid_generate_v4(),
json_build_object('i', 1),
array ['/tmp/abc', '/tmp/def']::TEXT[],
uuid_generate_v4(),
1,
false
);
CREATE TABLE IF NOT EXISTS public.temp_chapter_likes
(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"user" uuid NOT NULL,
chapter uuid NOT NULL,
CONSTRAINT "temp_chapterLikes_pkey" PRIMARY KEY (id),
--CONSTRAINT "chapterLikes_user_fkey" FOREIGN KEY ("user")
--REFERENCES auth.users (id) MATCH SIMPLE
-- ON UPDATE NO ACTION
-- ON DELETE NO ACTION,
CONSTRAINT temp_chapter_likes_chapter_fkey FOREIGN KEY (chapter)
REFERENCES public.temp_chapters (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
INSERT INTO public.temp_chapter_likes(
"user",
chapter
)
VALUES (
uuid_generate_v4(),
(select id from public.temp_chapters limit 1)
);
select uuid_generate_v4();
SELECT temp_chapter_likes.id, temp_chapters.id
From temp_chapter_likes
INNER JOIN temp_chapters ON temp_chapter_likes.chapter = temp_chapters.id;
--WHERE temp_chapters.seriesid = series_id
select c.seriesid, count(*)
From temp_chapter_likes cl
inner join temp_chapters c ON cl.chapter = c.id
group by c.seriesid;
ElectricDragon
04/24/2022, 12:35 PMchapter
parameter is a uuid
, but I do not get this code. Does the function call itself? And how do I pass in series_id
?tourdownunder
04/25/2022, 12:51 AMtemp_
to the tables and edited the sql script above so that you can run it as it is and not effect your current tables. You can help me by editing the insert statements be more realistic test data so we are on the same page.
You should get the same results I do. (with different UUID's) as they are random.sql
-- new proposal
create view series_counts as
select c.seriesid, count(*)
From temp_chapter_likes cl
inner join temp_chapters c ON cl.chapter = c.id
group by c.seriesid;
select * from series_counts;
create view chapter_counts as
select cl.chapter, count(*)
From temp_chapter_likes cl
group by cl.chapter;
select * from chapter_counts;
You can call a view like you would a table through superbase.ElectricDragon
04/26/2022, 1:35 PMcolumn "row" has pseudo-type record
create or replace view series_total_reads as (
select (chapters.seriesid,count(chapter_likes.id))
from chapter_likes
-- inner join ( select id, seriesid
-- from chapters
-- -- group by chapters.seriesid
-- ) as chapters
right join chapters
on chapter_likes.chapter = chapters.id
group by chapters.seriesid
)
row
tourdownunder
04/26/2022, 8:05 PMElectricDragon
04/29/2022, 5:21 PM