https://supabase.com/ logo
Powered by
#sql
Title
# sql
e

ElectricDragon

04/21/2022, 11:15 AM
Hi, I had begun an issue on GitHub but was advised to ask for help here as well. Here is my issue link https://github.com/supabase/supabase/discussions/6456. I am having issues with inner join
s

Scott P

04/21/2022, 4:52 PM
I would try this instead (run via the SQL console instead of creating a separate) [untested - should work]:
Copy code
sql
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 int
s

silentworks

04/21/2022, 5:55 PM
Do note you cannot name the function param the same as a column or table name else that will cause your code to fail.
t

tourdownunder

04/23/2022, 1:54 AM
Hey I remove some columns and created a minimum repeatable example My idea would be to go in another direction and use group by.
Copy code
sql
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.
Copy code
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;
e

ElectricDragon

04/24/2022, 12:35 PM
Hi @tourdownunder this is what I get when I try your code...can you help me out with this?
chapter
parameter is a 
uuid
, but I do not get this code. Does the function call itself? And how do I pass in 
series_id
?
t

tourdownunder

04/25/2022, 12:51 AM
The reason I pasted the minimum reproducible example is its something I can run and we are on the same page. I just added a 
temp_
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.
yeah I took out the parameter as I was focused on the whole series and not chapter. Its not hard to make another group by query that will quickly get the totals for the chapters. Though the functions had you had with parameters have good reuse principles though will always be slower then 1 single query. As the group by is a slightly different direction to what you were proposing with your 2 functions it might be worth just making 2 views and exposing them to the UI. Something like this:
Copy code
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.
e

ElectricDragon

04/26/2022, 1:35 PM
Hi @tourdownunder I tried to create a view with this but the error I get is 
column "row" has pseudo-type record
Copy code
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
 )
this is the data...i dont know why the column is 
row
t

tourdownunder

04/26/2022, 8:05 PM
That’s because you put the entire contents of the select statement enclosed with ()
e

ElectricDragon

04/29/2022, 5:21 PM
Ohhh
Is there a reason why that happens?
@tourdownunder thanks a lot! Your idea for using a view worked. Can I have your GitHub?
Powered by