https://supabase.com/ logo
#sql
Title
# sql
m

mwit

01/15/2022, 10:43 PM
Copy code
sql
    SELECT 
    g.user_id,
    (select curRank+1 into curRank) rank1
    FROM 
    (
        SELECT 
        p.user_id
        FROM posts p
        WHERE p.parent_uuid = input_uuid
        OR p.uuid = input_uuid

        GROUP BY p.user_id
    ) g
Copy code
SELECT ... INTO is not allowed here
curRank is the issue here, any ideas 🤔 ?
t

tourdownunder

01/15/2022, 10:49 PM
Is this in a plpgsql fucntion where
curRank
declared?
m

mwit

01/15/2022, 10:49 PM
im migrating from a MySQL query that worked
Copy code
sql
    LEFT JOIN 
    (
        SELECT 
        g.user_id,
        (@curRank := @curRank + 1) rank1
        FROM 
        (
            SELECT 
            p.user_id
            FROM posts p
            WHERE p.parent_id = ${parent_id}
            OR p.uuid = ${parent_id}
    
            GROUP BY p.user_id
        ) g,
        (SELECT @curRank := 0) r
    ) gg ON p.user_id = gg.user_id
yes,
declare curRank int := 0;
Copy code
sql
DROP FUNCTION IF EXISTS get_post_children;
create function get_post_children(input_uuid text) 
returns table (
  rank1 bigint,
  text text,
  channel text,
  uuid text,
  parent_uuid text,
  created_at timestamptz,
  image_url text,
  comment_count bigint,
  vote_count bigint, 
  my_vote smallint
) as $$
declare curRank int := 0;

begin
return query select 

gg.rank1,
p.text,
p.channel,
p.uuid,
p.parent_uuid,
p.created_at,
p.image_url,
p2.comment_count,
pv2.vote_count,
pv4.my_vote

from posts p

left join (
  select p1.parent_uuid, count(p1.*) comment_count from posts p1 group by p1.parent_uuid
) p2 on p2.parent_uuid = p.uuid
left join (
  select pv1.post_uuid, count(pv1.*) vote_count from post_votes pv1 where pv1.vote = 1 group by pv1.post_uuid
) pv2 on pv2.post_uuid = p.uuid
left join (
  select pv3.post_uuid, pv3.vote my_vote from post_votes pv3 where pv3.user_id = auth.uid()
) pv4 on pv4.post_uuid = p.uuid

LEFT JOIN 
(
    SELECT 
    g.user_id,
    curRank rank1,
    (select curRank+1 into curRank)
    FROM 
    (
        SELECT 
        p.user_id
        FROM posts p
        WHERE p.parent_uuid = input_uuid
        OR p.uuid = input_uuid

        GROUP BY p.user_id
    ) g
) gg ON p.user_id = gg.user_id

WHERE
p.parent_uuid in (input_uuid)

order by p.id asc
;
end;
$$ language plpgsql;
select get_post_children('vVAZ1zYite2')
complete function
t

tourdownunder

01/15/2022, 10:53 PM
I
'm not familair with mysql
is it just adding autoincrementing a int for every row?
m

mwit

01/15/2022, 10:54 PM
its basically a DISTINCT rank, yes
ive fiddled with using rank() over (partition by ... order by ...) but then I'd have to distinct it anyways and ran into trouble which this solution ive built on mysql solved
t

tourdownunder

01/15/2022, 10:58 PM
I'd like to understand why you felt you had to use distinct anyway? Was it to remove duplicates?
m

mwit

01/15/2022, 10:58 PM
yes
t

tourdownunder

01/15/2022, 11:01 PM
For issues like if you can produce a minimal reproducible example it would be useful.
For example what I did in the other thread that has a CREATE table, insert mock data to understnad the problem
m

mwit

01/15/2022, 11:02 PM
ive just started using postgres / supabase, so would take me a while to create a repro db 😄
but i agree, figured someone might chip in with some SQL knowledge
i still havent gotten around understanding SQL syntax completely 😂
thank you for trying though 🙂
t

tourdownunder

01/15/2022, 11:11 PM
Breaking a problem down into smaller parts will help likely help both understand the problem and also sharing the problem in in forums such as this in a digestible chunk.
m

mwit

01/15/2022, 11:20 PM
it actually is pretty barebones already, hence why my initial post was short compared to the full query i am successful like this
Copy code
sql
SELECT 
  g.user_id,
  rank() over(partition by g.user_id order by g.id) rank1
  FROM 
  (
      SELECT 
      p.id,
      p.user_id
      FROM posts p
      WHERE p.parent_uuid = 'vVAZ1zYite2'
      OR p.uuid = 'vVAZ1zYite2'
  ) g
but now i'd need to DISTINCT(rank1) it, which is sadly more SQL text compared to what i had with MySQL (innodb)
t

tourdownunder

01/15/2022, 11:30 PM
Ohh. the duplicates can occur with rank when they are equal. So you can use
row_number()
instead.
m

mwit

01/15/2022, 11:34 PM
tried, does not work either have to run an additional query to solve that issue
t

tourdownunder

01/15/2022, 11:37 PM
Do you have the correct partition in your `query? I'm starting to think
g.user_id
isn't what you need in there.
m

mwit

01/15/2022, 11:37 PM
pic shows rank, but i ran the query with
row_number
t

tourdownunder

01/15/2022, 11:38 PM
I understand as you label it as
rank1
m

mwit

01/15/2022, 11:39 PM
what else though, i want to split by ranks
have unique ranks, and the connected user_id
t

tourdownunder

01/15/2022, 11:39 PM
though what do you want to rank by?
m

mwit

01/15/2022, 11:39 PM
by p.id asc
its late, i realize that im not querying properly 😂
ill take off for today, nothing is making sense anymore 🥲
t

tourdownunder

01/15/2022, 11:43 PM
try tomorrow to put the window function in the inner query. And in the outer query you use the result it in a where statement to only get the rank you want per user
m

mwit

01/15/2022, 11:45 PM
still a duplicate
but i'll take off, i'll solve it tomorrow
thank you though for spending time with me ❤️
just for the sake of sharing my database here
t

tourdownunder

01/15/2022, 11:51 PM
Try tomorrow. I think I understand now
Copy code
sql
SELECT 
  g.user_id
  FROM 
  (
      SELECT 
      p.id,
      p.user_id,
      rank() over(partition by p.user_id order by p.id desc) rank1
      FROM posts p
      WHERE p.parent_uuid = 'vVAZ1zYite2'
      OR p.uuid = 'vVAZ1zYite2'
  ) g
 WHERE g.rank1 = 1  -- Only show the most recent post per user
m

mwit

01/15/2022, 11:52 PM
but in that case i would only get 1 row
i need all of them, so i can LEFT JOIN ON p.user_id them in the function ive posted
im basically hiding user_ids and instead showing an incremental number (participan1, participant2)
t

tourdownunder

01/15/2022, 11:53 PM
sorry. Something for tomorrow. It might sink in for me too
m

mwit

01/15/2022, 11:53 PM
the post can be made by participant1 and also commented on by participant1, thats why there is duplicate (of rank #1) here
haha, thanks for trying, ill figure it out
i did it before, now its just on postgres instead 😂 but its 1am and ive worked for 10h+ since i was not doing much heavy thinking i thought i'll tackle this one too
apparently not 💤 have a good night man ❤️ thanks