1
\$\begingroup\$

This query is part of a mobile application backend I am working on. It is hosted on Supabase (open source Firebase alternative): It's purpose is to retrieve the profile 'preview' - the most important data related to the user's profile.

I need to be able to present the following using this function:

  • basic profile information (displayName, avatar, etc...)
  • is this profile followed by the auth user
  • is this profile following the auth user
  • is this the profile of a current user
  • how many followers does this profile have
  • how many people are following this profile
  • last 18 recipes of this profile (needs to be joined with categories table)
  • the last 18 posts (recipes) posted by this profile

Important tables for this query are:

  • profiles(id, userId, displayName, avatarUrl...)
  • following(id, followerId, followeeId...)
  • recipes(id, userId, profileId, title, imageUrl, description...)
  • categories(id, title, slug, imageUrl...)

The code I wrote:

create or replace function get_user_profile(user_id uuid, auth_id uuid)
returns json as $body$
begin
  return (
    select json_build_object(
      'id', p.id,
      'userId', p."userId",
      'displayName', p."displayName",
      'avatarUrl', p."avatarUrl",
      'followedByCurrentUser', exists(
        select * from following 
        where "followerId" = user_id 
          and "followeeId" = auth_id
          limit 1
      ),
      'isFollowingCurrentUser', exists(
        select * from following 
        where "followeeId" = user_id 
          and "followerId" = auth_id
          limit 1
      ),
      'isCurrentUser', user_id = auth_id,
      'followeesCount', count(distinct f1.id),
      'followersCount', count(distinct f2.id),
      'recipes', (
        select array_agg(r)
        from (
          select rp.*, row_to_json(ct) category from recipes rp 
          left join categories ct
          on ct.id = rp."categoryId"
          where "userId" = 'fec610ab-c601-4682-9340-a71710cf33f3' 
          order by "updatedAt" 
          desc limit 10
        ) r
      )
    )
    from profiles p
    left join following f1 on f1."followerId" = p."userId" and f1."followeeId" != p."userId"
    left join following f2 on f2."followeeId" = p."userId" and f2."followerId" != p."userId"
    where p."userId" = user_id
    group by p.id
  );
end;
$body$ language PLPGSQL;

In terms of displaying the data, this is working fine as it does provide the information I need. But this query would be called every time a user visits another user's profile (or 100 users visiting 100 random profiles in the same time) and the following table may contain lots of data reading this data may become problematic in many ways.

My question is: is there a way to reduce the requests for the following table? As you can see, there are 2 exists queries and 2 joins on the following table. Is it possible to lower the amount of joins of this table and still present the data as intended?

To give more context about how I'm using this function:

Since I am using backend as a service called Supabase, I get the api scaffold for free, which means I can do something like this (this is just an example, I am not using javascript to call this but that is irrelevant):

let { data, error } = await supabase
  .rpc('get_user_profile', {
    auth_id, 
    user_id
  })

if (error) console.error(error)
else console.log(data)

or using bash:

curl -X POST 'myurl/rest/v1/rpc/get_user_profile' \
-d '{ "auth_id": "value", "user_id": "value" }' \
-H "Content-Type: application/json" \
-H "apikey: SUPABASE_KEY" \
-H "Authorization: Bearer SUPABASE_KEY"

The app is communicating directly with the Supabase api, there is no middleware application parsing the results of this return.

Thank you.

\$\endgroup\$
8
  • \$\begingroup\$ I don't understand your explanation as to why this returns JSON. "Preview" or otherwise, usual table-like results are more conventional. How specifically does the caller consume the return value? \$\endgroup\$
    – Reinderien
    May 31 at 18:47
  • \$\begingroup\$ @Reinderien this is consumed by a mobile application. This json will be converted into dart class. When I say preview, i think of it more as a summary of a user profile. I am querying only 18 recipes, but in reality the user can have much more. \$\endgroup\$
    – mischkez
    May 31 at 19:05
  • 4
    \$\begingroup\$ I asked you to read the SQL info before posting. "Before posting on CR, please read the SQL tag info. It is important you do this before posting." Did you do this? \$\endgroup\$
    – Mast
    May 31 at 19:24
  • 2
    \$\begingroup\$ The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. \$\endgroup\$
    – Mast
    May 31 at 19:25
  • 2
    \$\begingroup\$ Surely the mobile application doesn't itself spin up a database connection? In other words, is there an application server? \$\endgroup\$
    – Reinderien
    Jun 1 at 1:07

0

Browse other questions tagged or ask your own question.