Access values in "where" query

Hey, so I wan't to do some calculations, specifically geo radius. How can I access the data while doing the query?

const res = await db.query.apartments.findMany({
  where: (apartment, { lte }) => {
    const long = apartment.long
    const lat = apartment.lat
    return lte(lat, "15.053018297208397")
  }
})

const res = await db.query.apartments.findMany({
  where: (apartment, { lte }) => {
    const long = apartment.long
    const lat = apartment.lat
    return lte(lat, "15.053018297208397")
  }
})

Or how would I achieve this?

40 Replies

propOP•14mo ago

the long and lat are just some object without the actual value

Angelelz•14mo ago

The apartment object is your table and lat is a column. It's just to help you create the query. They don't hold any values at runtime

propOP•14mo ago

How would I achieve doing math in my queries?

Angelelz•14mo ago

To do what you want, you need to retrieve the data from the database if I understood your problem correctly Or, do the math in SQL What math are you trying to do?

propOP•14mo ago

I mean this would make me have to get all data I just want to find the points that are in a radius of another point

Angelelz•14mo ago

Do you know how to construct that query in SQL only?

propOP•14mo ago

I believe I do but this makes me question it

Angelelz•14mo ago

Let me see, then you can construct that query using typesafe drizzle syntax

propOP•14mo ago

Ight give me a few @Angelelz Allright, so sorry for the horrible sql but this works

SELECT *, long, lat FROM apartments
WHERE 111.320 * COS(long - 56.17058131394731) * ABS(long - 56.17058131394731) + 110.574 * ABS(lat - 10.18838401351414) < 10

SELECT *, long, lat FROM apartments
WHERE 111.320 * COS(long - 56.17058131394731) * ABS(long - 56.17058131394731) + 110.574 * ABS(lat - 10.18838401351414) < 10

This finds all the apartments in the radius of 10km (might be a little wrong math)

Angelelz•14mo ago

Lol you're almost there. Now, I think that some DBMSs have functions for stuff like this. Have you checked that?

propOP•14mo ago

I think supa base has some kind of way to do geo but I was kinda curious if drizzle could do this

Angelelz•14mo ago

const calculation = (lat: AnyColumn, long: AnyColumn) => sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414)`
const res = await db.query.apartments.findMany({
  where: (apartment, { lte }) => {
    const long = apartment.long
    const lat = apartment.lat
    return lte(lat, calculation(lat, long))
  }
})

const calculation = (lat: AnyColumn, long: AnyColumn) => sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414)`
const res = await db.query.apartments.findMany({
  where: (apartment, { lte }) => {
    const long = apartment.long
    const lat = apartment.lat
    return lte(lat, calculation(lat, long))
  }
})

propOP•14mo ago

@Angelelz The return checks if lat is lte to the distance, though I'd like to check if the distance is lte the given radius The problem I have is that the lte function requires a column would be perfect if I could just pass the radius

Angelelz•14mo ago

const calculation = (lat: AnyColumn, long: AnyColumn) => sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414) < 10`
const res = await db.query.apartments.findMany({
  where: (apartment, { lte }) => {
    const long = apartment.long
    const lat = apartment.lat
    return calculation(lat, long)
  }
})

const calculation = (lat: AnyColumn, long: AnyColumn) => sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414) < 10`
const res = await db.query.apartments.findMany({
  where: (apartment, { lte }) => {
    const long = apartment.long
    const lat = apartment.lat
    return calculation(lat, long)
  }
})

Like that then

propOP•14mo ago

where: (apartment, { lte, sql }) => {
  const long = apartment.long
  const lat = apartment.lat
  return sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414) < 2`
}

where: (apartment, { lte, sql }) => {
  const long = apartment.long
  const lat = apartment.lat
  return sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414) < 2`
}

This seems to work as well Not sure how safe that is for injection @Angelelz oh, just realized it was the exact same so yeah, like that thanks a lot dude ! @AngelelzFor the final question, if I would like to also include the calculation in my result, normally I'd just select that as well in sql but that won't get me the type like output

{ 
  name: "somewhere",
  lat: "129412", 
  long: "129124", 
  distance: "1.5"
}

{ 
  name: "somewhere",
  lat: "129412", 
  long: "129124", 
  distance: "1.5"
}

in sql

SELECT *, lat, long, calculation(lat, long)  distance FROM apartments
WHERE distance < 10

SELECT *, lat, long, calculation(lat, long)  distance FROM apartments
WHERE distance < 10

propOP•14mo ago

Tried the extra thing, but no clue how to reuse it

Angelelz•14mo ago

You have to change the calculation function to what it was before. But I'm thinking you'll need to use db.select for this:

const calculation = (lat: AnyColumn, long: AnyColumn) => sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414)`
const res = await db.select({
  ...getTableColumns(apartaments)
  calculation: calculation(apartaments.lat, apartaments.long).mapWith(Number).as("calculation"),
})
  .from(apartments)
  .where(sql`calculation < 10`)

const calculation = (lat: AnyColumn, long: AnyColumn) => sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414)`
const res = await db.select({
  ...getTableColumns(apartaments)
  calculation: calculation(apartaments.lat, apartaments.long).mapWith(Number).as("calculation"),
})
  .from(apartments)
  .where(sql`calculation < 10`)

propOP•14mo ago

ahh I see, calculation column in the result will remain unknown though

Angelelz•14mo ago

It won't, that's why I used mapWith It'll be a number

propOP•14mo ago

ah I missed that @Angelelz did your last sql statement actually work? mine says "calculation" doesnt exist

Angelelz•14mo ago

No, I'm not trying it

propOP•14mo ago

fair

Angelelz•14mo ago

Can you show the sql that was generated?

propOP•14mo ago

Where can I see that?

Angelelz•14mo ago

If you pass logger: true to the drizzle function when instantiating it It will log all your queries to the console

propOP•14mo ago

@Angelelz hmm I passed the logger: true but it doesn't do much

Angelelz•14mo ago

Can I see how you're doing it?

propOP•14mo ago

export const db = drizzle(queryClient, { schema, logger: true });

export const db = drizzle(queryClient, { schema, logger: true });

sure thing

Angelelz•14mo ago

You're running the query with logger true and it's not being logged?

propOP•14mo ago

correct I do the query with this

const res = await db.select({.....

const res = await db.select({.....

Angelelz•14mo ago

Can you copy the error message from the console?

propOP•14mo ago

select "id", "name", "lat", "long", 111.320 * COS("long" - 56.17058131394731) * ABS("long" - 56.17058131394731) + 110.574 * ABS("lat" - 10.18838401351414) as "distance" from "apartments" where distance < 10

select "id", "name", "lat", "long", 111.320 * COS("long" - 56.17058131394731) * ABS("long" - 56.17058131394731) + 110.574 * ABS("lat" - 10.18838401351414) as "distance" from "apartments" where distance < 10

I got the query now Heres the error:

PostgresError: column "distance" does not exist

PostgresError: column "distance" does not exist

Keep in mind I actually called the column distance and so on The query that was built looks right

Angelelz•14mo ago

Try this:

const calculation = (lat: AnyColumn, long: AnyColumn) => sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414) as distance`
const res = await db.select({
  ...getTableColumns(apartaments)
  calculation: calculation(apartaments.lat, apartaments.long).mapWith(Number),
})
  .from(apartments)
  .where(sql`distance < 10`)

const calculation = (lat: AnyColumn, long: AnyColumn) => sql`111.320 * COS(${long} - 56.17058131394731) * ABS(${long} - 56.17058131394731) + 110.574 * ABS(${lat} - 10.18838401351414) as distance`
const res = await db.select({
  ...getTableColumns(apartaments)
  calculation: calculation(apartaments.lat, apartaments.long).mapWith(Number),
})
  .from(apartments)
  .where(sql`distance < 10`)

propOP•14mo ago

select "id", "name", "lat", "long", 111.320 * COS("long" - 56.17058131394731) * ABS("long" - 56.17058131394731) + 110.574 * ABS("lat" - 10.18838401351414) from "apartments" where distance < 10

select "id", "name", "lat", "long", 111.320 * COS("long" - 56.17058131394731) * ABS("long" - 56.17058131394731) + 110.574 * ABS("lat" - 10.18838401351414) from "apartments" where distance < 10

gives this query, same issue though

Angelelz•14mo ago

Look at the calculation function You missed the as distance If not try sql`"distance" < 10

propOP•14mo ago

same thing it just adds the as calculation at the end which is the same as the very first try Guess I'll have to look into $with if interested i ended up with this

const sq = db.$with('sq').as(db.select({
  ...getTableColumns(apartments),
  distance: sql`111.320 * COS(${apartments.long} - 56.17058131394731) * ABS(${apartments.long} - 56.17058131394731) + 110.574 * ABS(${apartments.lat} - 10.18838401351414)`.mapWith(Number).as("distance")
}).from(apartments))
const res = await db.with(sq).select().from(sq).where(lte(sq.distance, 10))

const sq = db.$with('sq').as(db.select({
  ...getTableColumns(apartments),
  distance: sql`111.320 * COS(${apartments.long} - 56.17058131394731) * ABS(${apartments.long} - 56.17058131394731) + 110.574 * ABS(${apartments.lat} - 10.18838401351414)`.mapWith(Number).as("distance")
}).from(apartments))
const res = await db.with(sq).select().from(sq).where(lte(sq.distance, 10))

i mean not perfect but I'll take it

Angelelz•14mo ago

That works! That's even better IMO

GatorCSE•14mo ago

does (sqlite) drizzle have a built in way to atomic increment a field during an update? I'm doing this:

db.update(table)
          .set({ numField: sql`${table.numField} + 1` })
          .where(gt(table.numField, 0))

db.update(table)
          .set({ numField: sql`${table.numField} + 1` })
          .where(gt(table.numField, 0))

but wondering if there was a built in function

Angelelz•14mo ago

This is the way

GatorCSE•14mo ago

can we use prepared statements in batches? I'm trying with:

db.batch([
  myPrepared.returning({}),
  db.select().from(myTable),
]);

db.batch([
  myPrepared.returning({}),
  db.select().from(myTable),
]);

but getting `Property 'returning' does not exist on type 'PreparedQuery<...' errors

Gaming

Programming

Access values in "where" query