Gi
Gi•3w ago

Are there any plans to allow for better

Are there any plans to allow for better support on compound indexes that are ranged? Or is this simply not possible?
1 Reply
Gi
GiOP•3w ago
With this schema for example, 
day_ahead_prices: defineTable({
    period_start: v.number(),
    period_end: v.number(),
    unit_price: v.float64(),
}).index("by_period_start_period_end", ["period_start", "period_end"]),
day_ahead_prices: defineTable({
    period_start: v.number(),
    period_end: v.number(),
    unit_price: v.float64(),
}).index("by_period_start_period_end", ["period_start", "period_end"]),
This works: 
const existing = await ctx.db.query("day_ahead_prices")
    .withIndex("by_period_start_period_end", q => q
        .eq("period_start", period_start)
        .eq("period_end", period_end)
    ).unique();
const existing = await ctx.db.query("day_ahead_prices")
    .withIndex("by_period_start_period_end", q => q
        .eq("period_start", period_start)
        .eq("period_end", period_end)
    ).unique();
But this does not: 
return await ctx.db.query("day_ahead_prices")
    .withIndex("by_period_start_period_end", q => q
        .gte("period_start", period_start)
        .lte("period_end", period_end) // ❌ error
    )
    .collect()
return await ctx.db.query("day_ahead_prices")
    .withIndex("by_period_start_period_end", q => q
        .gte("period_start", period_start)
        .lte("period_end", period_end) // ❌ error
    )
    .collect()
Instead you would need to guess which of the cases is more likely to give fewer results (the gte start or lte end) and write it like this 
return await ctx.db.query("day_ahead_prices")
    .withIndex("by_period_start_period_end", q =>
        q.gte("period_start", period_start)
    )
    .filter(q =>
        q.lte(q.field("period_end"), period_end)
    )
    .collect()
return await ctx.db.query("day_ahead_prices")
    .withIndex("by_period_start_period_end", q =>
        q.gte("period_start", period_start)
    )
    .filter(q =>
        q.lte(q.field("period_end"), period_end)
    )
    .collect()
But this loses the benefit of half the index. Especially for cases where you want to query something thats older

Did you find this page helpful?