https://supabase.com/ logo
Join Discord
Powered by
Hello Everyone - Just to follow up on my earlier m...
# sql
j
Hello Everyone - Just to follow up on my earlier message, does anyone have any insight?
b
Hi @User can you post the SQL you have once again so I can look at it? There's so much action on this server now I'm getting a little lost.
j
Hello!
b
I'm looking at the JSONB documentation... this is a tough one!
j
I don't have any sql, I'm trying to convert some Mongo to sql.
Did you grab a chance to look at my description?
b
ah, so it might be a better idea to structure the data a little differently.
Yes I understand what you're trying to do.
Can you store the tags in a related table? If so, then it's pretty easy as a subquery.
j
I had thought about that. But the tags are user generated. They could be anything 😦
b
That's fine. No problem there.
So what's your main table name?
j
posts
b
ok, so you have posts, each post has an id.
and tags you have an id, plus a post_id, plus a tag field.
tags.tag_id, tags.post_id, tag.tag_name
something like that.
now what are you trying to get? posts with the most matching tags?
j
yes, that's right! So if someone searches articles with tags "food", "Texas", "Top-Five"
Then they would get a list of articles with at least one match, and ordered by the number of matches on those tags.
But see I think there is an issue with having user generated tags, because a tag could be in that table multiple times. For example: "Texas"
b
that shouldn't matter... but let me think this SQL through, hold on
j
Okay, it's a fun one.
b
as long as you only allow one Texas tag per post, which is easy to do
you could make that a unique key so it wouldn't allow Texas to be inserted as a tag twice for a certain post
Copy code
select post_id, count(*) as theCount from tags where tag_name in ('Texas','food','Top-Five') group by post_id order by theCount desc;
That should do it, I think.
j
whoa
b
been a while since I wrote some difficult SQL 🙂
j
haha
Thank you very much! I'm excited to give this a go in supabase.
b
Yeah let me know if that works. It looks right to me, and it's actually pretty simple if you structure it like that.
PreviousNext
Powered by