CODEWITHSUNDEEP
databasepostgresqlfluttersupabase
lostDeveloper
lostDeveloper

Reputation: 63

Writing Database Function in PostgreSql - Supabase

I need help with querying data from a PostgreSQL database. Currently on the client side this is the query that is being sent to the server:

await Supabase.instance.client
        .from('books')
        .select('''*,BookBy:authors(*), likes(likedBy), totalLikes:likes(count)''')
        .match({'likes.likedBy': loggedInUserUid,'category': categoryId})
        .order('id');

This query currently returns data in the following structure:

[{
   'id': 1,
   'title':'...',
   'Description': '...',
   'Published Date':'...',
   'categoryId':'3',
   'BookBy':{
              'id': 1,
              'Author':'...',
              'Bio':'...',
              'Picture':'...'
            },
   'likes':{'likedBy':'...'},
   'totalLikes':{'count':30},       
}]

By using match statement, it fetches all the books that match the provided categoryId. Also, my main table here is books and (authors, likes) tables are foreign, with "totalLikes" used as an alias to query count for the same foreign "likes" table.

What I am now trying to figure out is how to query books by popularity.

So when the data is retrieved it is in the order of the number of likes per book.

Current Fetching Order:

Ex: Book id 1, 50 likes
    Book id 2, 60 likes
    Book id 3, 20 likes
    Book id 4, 30 likes
    Book id 5, 25 likes

Intended Fetching Order:

EX: Book id 2, 60 likes
    Book id 1, 50 likes
    Book id 4, 30 likes
    Book id 5, 25 likes
    Book id 3, 20 likes

I don't know if this is something that should be queried on the client side or if should I write a database function to take care of this. If it's the latter option then I would appreciate any guidance as to how to do that as I'm fairly new to the PostgreSQL database.

Upvotes: 0

Views: 1062

Answers (1)

dshukertjr
dshukertjr

Reputation: 18670

I think you would need a database function in this case, and would call the function with .rpc() from your client.

You might have to make some adjustment to fit your particular usecase, but hopefully this gives you a general direction of what kind of function you need.

create or replace function liked_books(category_id text)
returns setof record
language sql
as
$$
  select books.id, books.title, count(likes.id) as likes_count
    from books
    left join likes on books.id = likes.book_id
    where books.category = liked_books.category_id
    group by books.id
    order by likes_count desc;
$$;

Upvotes: 1

Related Questions