compatible-crimson

Trying to select the latest of visit of a customer

I have these schemas:

const customerSchema = v.object({
    id: idWithDefaultSchema,
    first_name: v.pipe(v.string(), v.minLength(3)),
    last_name: v.nullish(v.pipe(v.string(), v.minLength(3))),
    phone_number: v.nullish(phoneNumberSchema),
    organization_id: v.string(),
});

const visitSchema = v.object({
    id: idWithDefaultSchema,
    date: v.date(),
    customer_id: idSchema,
    status: visitStatusTypeSchema,
});

const visitServiceSchema = v.object({
    id: idWithDefaultSchema,
    service_organization_id: idSchema,
    custom_service: v.nullish(v.string()),
    payment: v.nullish(v.number()),
    duration: v.nullish(v.string()),
    customer_visit_id: idSchema,
});

const customerSchema = v.object({
    id: idWithDefaultSchema,
    first_name: v.pipe(v.string(), v.minLength(3)),
    last_name: v.nullish(v.pipe(v.string(), v.minLength(3))),
    phone_number: v.nullish(phoneNumberSchema),
    organization_id: v.string(),
});

const visitSchema = v.object({
    id: idWithDefaultSchema,
    date: v.date(),
    customer_id: idSchema,
    status: visitStatusTypeSchema,
});

const visitServiceSchema = v.object({
    id: idWithDefaultSchema,
    service_organization_id: idSchema,
    custom_service: v.nullish(v.string()),
    payment: v.nullish(v.number()),
    duration: v.nullish(v.string()),
    customer_visit_id: idSchema,
});

I'm trying to get the last visit that the customer made but, I can't figure out how to write the query with tanstack db. My last attempt was joining the two collections and then grouping by customer id and then trying to select biggest (last) visit date but I found that max doesn't support dates 🙁 Is there another way to do this?

12 Replies

compatible-crimsonOP•6d ago

Looking at max and min I think adding support for dates should be easy... at least that what it looks like.

rival-black•6d ago

Yep, we need to add support for dates to max/min. You are very welcome to have a go, as you said it should be quite easy. I'll review any PR as soon as you post it. The proposed query would then work.

compatible-crimsonOP•6d ago

I'm looking into but, I noticed that max always returns a number. Is that OK if we pass in a date and, we get back, a number? Or should I override something somewhere to make it return a date?

mute-gold•6d ago

generally you order by date and limit 1

compatible-crimsonOP•6d ago

Well, I want all the customers and I want the latest date for all of them. I think that approach would work for a single customer, but not when I'm getting multiple.

rival-black•6d ago

Yep, we can change max to return the type that is passed in, for for dates return the max date.

compatible-crimsonOP•5d ago

I wonder if I have to do some work to make it work with gte and the like? Right now, I'm pretty sure it doesn't work with my changes since max and min now return the date that was passed in.

rival-black•5d ago

If you're happy to pick that up too that would be awesome! It should be relatively simple too.

compatible-crimsonOP•5d ago

Yep, giving it a shot.

compatible-crimsonOP•5d ago

I've got a PR up: https://github.com/TanStack/db/pull/428

GitHub

feat: add support for dates to max and min functions. by MAST1999 ...

I tried not to change too many things. If something doesn&#39;t look right, or we need some tests that are missing, I&#39;m happy to try and make those changes. BTW, I tried adding date sup...

rival-black•5d ago

Awesome! I'll take a good look at it tomorrow. Thank you.

compatible-crimsonOP•4d ago

Just pinging as a reminder

Gaming

Programming

Trying to select the latest of visit of a customer

Did you find this page helpful?