S
Supabase•2y ago
Funo

Limit select for each entry in "in"

Hey there I am trying to fetch the last message for severeal groups in one select. 
  const { data: lastMessages } = await supabase
    .from('messages')
    .select('message')
    .in('group', groups?.map((group) => group.id) || [])
    .order('created_at', { ascending: false })
  const { data: lastMessages } = await supabase
    .from('messages')
    .select('message')
    .in('group', groups?.map((group) => group.id) || [])
    .order('created_at', { ascending: false })
which works fine but gives me back all of the messages of the group. I would like to only get one message for each group in the "in" condition. So if I got four groups with messages in it it should return four rows. "limit" or "single" only work for the whole amount of rows so I am kind of stuck here.
6 Replies
Olyno
Olyno•2y ago
Hi :vmathi: This is due to the fact that the .limit() function in Supabase applies to the entire result set, not individual entries in the IN clause. You could group the result by group id, either by doing it with loadsh or something similar.
Funo
FunoOP•2y ago
Thanks for reaching out. Sure I could solve this in the client but this would mean I would always fetch hundreds of messages although I only need one. I also thought about splitting it into a multiple selects and request them in parallel with Promise.all Just thought there might be a solution I am missing. This should also be possible with a RPC right?
Olyno
Olyno•2y ago
Definitely, this is what i would recommend you if you don't want to make any client side computation. Something like this should work; 
CREATE OR REPLACE FUNCTION get_last_messages(_groupIds integer[])
RETURNS TABLE (groupId int, message text) AS $$
BEGIN
 RETURN QUERY
 WITH numbered_messages AS (
   SELECT groupId, message, row_number() OVER (PARTITION BY groupId ORDER BY created_at DESC) AS rn
   FROM messages WHERE groupId = ANY(_groupIds)
 )
 SELECT groupId, message FROM numbered_messages WHERE rn = 1;
END; $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION get_last_messages(_groupIds integer[])
RETURNS TABLE (groupId int, message text) AS $$
BEGIN
 RETURN QUERY
 WITH numbered_messages AS (
   SELECT groupId, message, row_number() OVER (PARTITION BY groupId ORDER BY created_at DESC) AS rn
   FROM messages WHERE groupId = ANY(_groupIds)
 )
 SELECT groupId, message FROM numbered_messages WHERE rn = 1;
END; $$ LANGUAGE plpgsql;
And then you can call it like that: 
const { data, error } = await supabase
 .rpc('get_last_messages', { _groupIds: groups.map((group) => group.id) });
if (error) throw error;
const { data, error } = await supabase
 .rpc('get_last_messages', { _groupIds: groups.map((group) => group.id) });
if (error) throw error;
Funo
FunoOP•2y ago
Awesome I am gonna try this out. Thank you so much!
Olyno
Olyno•2y ago
You're welcome :p
Funo
FunoOP•2y ago
This solution worked fine but @David pointed out that limit also takes a foreignTable option to achieve this. It would look something like this 
await supabase
    .from('groups')
    .select(
      'id, messages (id, content, created_by (name))'
    )
    .in('id', groupsWithAccess)
    .order('created_at', { ascending: false, foreignTable: 'messages' })
    .limit(1, { foreignTable: 'messages' });
await supabase
    .from('groups')
    .select(
      'id, messages (id, content, created_by (name))'
    )
    .in('id', groupsWithAccess)
    .order('created_at', { ascending: false, foreignTable: 'messages' })
    .limit(1, { foreignTable: 'messages' });
In case anyone gets into a similar problem 🙂

Did you find this page helpful?