jamwt•11mo ago

One to many query questions

Can you create a support thread about this one to many relationship and performance? Happy to dive into it there.

8 Replies

@vector okay so instead of having a player store multiple item _id. I should just store the player_id in the items and query them and filter by the id on an index? The reason I save the Id on the player is so i can have quick access to the id without doing much work to find it. Or so i thought

lee•11mo ago

You mentioned indexes and caching, which are separate things. If you shared the code with the access pattern, it may help with suggestions. If you store the owner_id field with an index on it, it seems like the player.items field becomes redundant, although you are free to store it if you want

erquhart•11mo ago

You mentioned "when are ids cached?" - note that the only things that get cached are the results of function calls (as far as I'm aware). So as @vector alluded to, make a query function to get the items for a given player and caching will generally kick in when you call that query for the same player multiple times. In general, I've learned to avoid optimizing things when writing new convex functions. Write queries that do what you need and only bother optimizing when there's an observed performance or usage issue. Same for denormalizing your data (eg., keeping item id's on the player AND a player id on the item). There's definitely a place for denormalization with Convex, but it's extra work to keep things synced, so you'll want to be sure it's worth it before you go there.

CodingWithJamal•11mo ago

oh i see, thats good advice thanks. Yeah I think how im going it is good for right now, everything is still amazingly fast under 50ms most times okay here is my tables:

anime: defineTable({
        title: v.string(),
        display_name: v.string(),
        episodes: v.array(v.id('episodes')),
        thumbnail: v.string(),
        release_date: v.optional(v.string()),
    })
        .index('by_title', ['title'])
        .searchIndex('search_name', {
            searchField: 'display_name',
        }),
    episode: defineTable({
        parent_id: v.id('anime'),
        likes: v.number(),
        dislikes: v.number(),
    }),

anime: defineTable({
        title: v.string(),
        display_name: v.string(),
        episodes: v.array(v.id('episodes')),
        thumbnail: v.string(),
        release_date: v.optional(v.string()),
    })
        .index('by_title', ['title'])
        .searchIndex('search_name', {
            searchField: 'display_name',
        }),
    episode: defineTable({
        parent_id: v.id('anime'),
        likes: v.number(),
        dislikes: v.number(),
    }),

I use functions to get all the anime or search it and i always want to be able to load there episodes if needed, which is why i added the episode _id to the anime. then i just use await ctx.db.get(args.id); and get the episode when needed in the client but i dont think just querying and adding an index on the episode table will help me much because i still need to know the episode ids on the UI or else its a lot harder to use my data such as displaying shows and clicking on there episode urls

erquhart•11mo ago

I would drop the episodes array. Instead, query the episodes for each anime in your convex function and return what you need. You can index the episode table on parent_id to make it fast. Query function body would look something like:

const animes = ctx.db
  .query('anime')
  .withSearchIndex('search_name', q => {
    return q.search('display_name', args.query)
  })
  .take(10)
const animesWithEpisodes = await Promise.all(animes, async anime => {
  const episodes = await ctx.db
    .query('episode')
    .withIndex('by_parentId', q => q.eq('parentId', anime.parentId))
    .collect()
  return { ...anime, episodes }
})
return animesWithEpisodes

const animes = ctx.db
  .query('anime')
  .withSearchIndex('search_name', q => {
    return q.search('display_name', args.query)
  })
  .take(10)
const animesWithEpisodes = await Promise.all(animes, async anime => {
  const episodes = await ctx.db
    .query('episode')
    .withIndex('by_parentId', q => q.eq('parentId', anime.parentId))
    .collect()
  return { ...anime, episodes }
})
return animesWithEpisodes

CodingWithJamal•11mo ago

yeah i understand that. Maybe I will just use both ways, because I still see value in having the ids in the anime as sometimes I dont need to fetch all the data for the episodes, and I can just use a db.get(id). But if I dont have the id from the parent then I cant only query by the by_parentId index as there would be multile documents that match

erquhart•11mo ago

if I dont have the id from the parent then I cant only query by the by_parentId index as there would be multile documents that match

But your array of episodes also has multiple ids - the same list of ids that you would get by querying by parentId. What's the advantage?

CodingWithJamal•11mo ago

okay i see, let me try this

One to many query questions

Did you find this page helpful?