useUpsertItem does not seem to work when I select with a join on a foreign table

[gregamann]

Describe the bug
I've got 2 tables : Messages and Profiles (linked by sender_id column in Messages).

useUpsertItem is working when I select only columns from Messages table
(ex: supabase.from('messages').select('id, sender_id, content, created_at'))

But when I try to add profiles columns (ex: supabase.from('messages').select('id, sender_id, profiles(id, first_name, last_name), content, created_at')) cache is not updated...

Here is the upsert code. Did I miss something ?

const upsert = useUpsertItem({
    schema: 'public',
    table: 'messages',
    primaryKeys: ['id'],
  })

Expected behavior
Cache should be updated even if I use a select with a join

[stnmonroe]

I'm having the same issue. Did you end up figuring out how to make this work @gregamann?

[psteinroe]

can you provide a repro and more snippets, especially how you pass the data to useUpsertItem? The dx around custom cache updates is not so great, and I assume that this something about how the data is passed.

[rgathmann]

I believe I have a similar issue and I can provide a some example code that might help illustrate. I don't have time to put together fully custom repo but hopefully this code helps.

My query is located here:

export const getJourneysWithThreads = (userId: string) =>
  createClient()
    .from('member_journeys')
    .select(
      `
      id,
      name,
      user_id,
      created_at,
      updated_at,
      threads(
        id,
        name,
        created_at,
        updated_at
      ),
      journey_user(journey_id, user_id)
    `,
      { count: 'exact' }
    )
    .or(`member_id.eq.${userId}, user_id.eq.${userId}`)
    .throwOnError();

Here is my upsertItem code:

  const upsertItem = useUpsertItem({
    primaryKeys: ['id'],
    schema: 'public',
    table: 'member_journeys',

    // this will only work if I uncomment this line
    // revalidateTables: [{ schema: 'public', table: 'member_journeys' }],
  });

And it gets called in this function:

  const onSubmit: SubmitHandler<CreateJourneySchemaType> = async (formData) => {
    try {
      const result = await createClient().rpc('create_journey', {
        name: formData.name,
        threads: formData.threads.map((thread) => thread.name),
        emails: formData.members.map((member) => member.email),
      });

      if (!result.data) {
        throw new Error('Failed to create journey');
      }

      const journey = await getJourneyWithThreadById(result.data);
      await upsertItem({ result: journey.data });
      console.log('Journey created:', journey.data);

      router.replace(`/`);
    } catch (error) {
      console.error(error);
    }
  };

The data that is entered as the result here upsertItem({ result: journey.data }) is the exact same shape as the query result as well. I am not sure what the issue would be but any guidance would be awesome!

[rgathmann]

Also if it helps, this is the query details from the react query dev tools

[
  "postgrest",
  "null",
  "public",
  "member_journeys",
  "or=%28member_id.eq.87188daa-7eb1-4913-ba8d-7689600f7797%2C+user_id.eq.87188daa-7eb1-4913-ba8d-7689600f7797%29&select=id%2Cname%2Cuser_id%2Ccreated_at%2Cupdated_at%2Cthreads%28id%2Cname%2Ccreated_at%2Cupdated_at%29%2Cjourney_user%28journey_id%2Cuser_id%29",
  "null",
  "count=exact",
  "head=false",
  ""
]

My theory is that this line I the reason I am not able to do the upset:

"or=%28member_id.eq.87188daa-7eb1-4913-ba8d-7689600f7797%2C+user_id.eq.87188daa-7eb1-4913-ba8d-7689600f7797%29&select=id%2Cname%2Cuser_id%2Ccreated_at%2Cupdated_at%2Cthreads%28id%2Cname%2Ccreated_at%2Cupdated_at%29%2Cjourney_user%28journey_id%2Cuser_id%29",

which is created by this part of the query

.or(`member_id.eq.${userId}, user_id.eq.${userId}`)

[psteinroe]

thanks for the details @rgathmann.

for the upsertItem to work, cache helpers needs to to get a few things:

1. a value for the primary key. you need to pass the id as part of the object, e.g.

await upsertItem({ id: "myId", result: journey.data });

2. all filters must be available "offline". you should add member_id and user_id to the query select fields so that these fields can be evaluated without doing the query.

what also looks a bit suspicious:

await upsertItem({ result: journey.data });

why are you setting result? I can't see that field in the original query.

I know that this is not a great dx, and I am open for suggestions to improve everything around custom cache updates.

[rgathmann]

Interesting! Let me take a look tonight and see what I can find. The issue may be that I am providing the user_id but not the member_id so that is causing the filter to fail. Let me test a few things and come back with an answer.