https://supabase.com/ logo
Join Discord
Powered by
```sql SELECT g.user_id, (select curR...
# sql
m
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
Is this in a plpgsql fucntion where 
curRank
declared?
m
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
I
'm not familair with mysql
is it just adding autoincrementing a int for every row?
m
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
I'd like to understand why you felt you had to use distinct anyway? Was it to remove duplicates?
m
yes
t
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
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
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
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
Ohh. the duplicates can occur with rank when they are equal. So you can use 
row_number()
instead.
m
tried, does not work either have to run an additional query to solve that issue
t
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
pic shows rank, but i ran the query with 
row_number
t
I understand as you label it as 
rank1
m
what else though, i want to split by ranks
have unique ranks, and the connected user_id
t
though what do you want to rank by?
m
by p.id asc
its late, i realize that im not querying properly 😂
ill take off for today, nothing is making sense anymore 🥲
t
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
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
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
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
sorry. Something for tomorrow. It might sink in for me too
m
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
PreviousNext
Powered by