Drizzle Team•12mo ago

Map casing when using sql`` operator

If I query my table using the magic sql operator. How can I get back the column names like userId instad of how they are in the tabel user_id?

29 Replies

ericmartinezr•12mo ago

https://orm.drizzle.team/docs/sql#sqlmapwith probably this?

Magical sql operator 🪄 - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Roberto Duran•12mo ago

sql`select user_id AS "userId" from users``;

bambam22OP•12mo ago

@ericmartinezr So, I was looking at this, I'm not sure how to get it to work with all the columns, you know? @Roberto Duran Okay, if that's what I need to do that's cool. I was just wondering if I could use .as<T>() or .mapWith() instead But the examples in the documentation only show how to use them with single column names. So I'm confused

Roberto Duran•12mo ago

for your query I think the .as is a better option You could build your request like this

db.
select({
  userId: sql`${tableName.user_id}`.as("userId")
})
.from(tableName)

db.
select({
  userId: sql`${tableName.user_id}`.as("userId")
})
.from(tableName)

sorry is the syntax is not right... Typing from my phone

bambam22OP•12mo ago

Okay okay, no worries about the sytax. The high level approach is what I needed. Thanks!

Angelelz•12mo ago

Question. I you select like this:

select({
  userId: sql`${tableName.user_id}`
})
.from(tableName)

select({
  userId: sql`${tableName.user_id}`
})
.from(tableName)

Do you not get an object with userId? That might just be a bug. Can you show the query you're trying to create?

bambam22OP•12mo ago

@Angelelz Here's what I have so far

        const data = await db.query.GamesTable.findMany({
            with: {
                platforms: {
                    columns: {},
                    with: {
                        platform: true,
                    },
                },
            },
            where: (game, { ilike }) => ilike(game.name, `%${query.searchText}%`),
            offset: page * 20,
            limit: 20,
        })
        return { data, nextPage }

        const data = await db.query.GamesTable.findMany({
            with: {
                platforms: {
                    columns: {},
                    with: {
                        platform: true,
                    },
                },
            },
            where: (game, { ilike }) => ilike(game.name, `%${query.searchText}%`),
            offset: page * 20,
            limit: 20,
        })
        return { data, nextPage }

I need to put a where clause on Genres that is joined to the GamesTable by a join table Since I couldn't really get that to work I decided to just write a raw query

        const data = await db.execute(
            sql<Game[]>`
                 SELECT g.id,
                         g.source_id as sourceId,
                         g.created_at as createdAt,
                         g.updated_at as updatedAt,
                         g.slug,
                         g.name,
                         g.released_at as released,
                         g.background_image as backgroundImage,
                         g.rating,
                         g.rating_top as ratingTop,
                         g.ratings_count as ratingsCount,
                         g.metacritic,
                         g.playtime,
                         g.user_id as userId,
                         json_agg(p) as platforms
                 FROM games g
                 JOIN games_to_platforms gtp on gtp.game_id = g.id
                 JOIN platforms p on gtp.platform_id = p.id
                 JOIN games_to_genres gtg on gtg.game_id = g.id
                 JOIN genres genres on genres.id = gtg.genre_id
                 WHERE genres.slug = 'action'
                 GROUP BY g.id
                 LIMIT 20
            `
        )

        const data = await db.execute(
            sql<Game[]>`
                 SELECT g.id,
                         g.source_id as sourceId,
                         g.created_at as createdAt,
                         g.updated_at as updatedAt,
                         g.slug,
                         g.name,
                         g.released_at as released,
                         g.background_image as backgroundImage,
                         g.rating,
                         g.rating_top as ratingTop,
                         g.ratings_count as ratingsCount,
                         g.metacritic,
                         g.playtime,
                         g.user_id as userId,
                         json_agg(p) as platforms
                 FROM games g
                 JOIN games_to_platforms gtp on gtp.game_id = g.id
                 JOIN platforms p on gtp.platform_id = p.id
                 JOIN games_to_genres gtg on gtg.game_id = g.id
                 JOIN genres genres on genres.id = gtg.genre_id
                 WHERE genres.slug = 'action'
                 GROUP BY g.id
                 LIMIT 20
            `
        )

But it's not converting the casing like drizzle usualy does

Angelelz•12mo ago

Oh got you! Yeah, raw queries don't get mapped to the camel case

bambam22OP•12mo ago

Yeahhhhh

Angelelz•12mo ago

But translating that query to drizzle syntax should be pretty simple

bambam22OP•12mo ago

Oh really? I thought I couldn't do a search on the Genres via the inner join? That's where I got stuck

Angelelz•12mo ago

That query should be a one to one translation I don't see anything weird. What was the part you couldn't figure out?

bambam22OP•12mo ago

Well, I haven't been able to figure out two things at this point 1. How to query games that have a genre with a certain id 2. How to automatically map the casing when writing a raw query If I could do 1 I wouldn't need 2 But I can't figure out either really

Angelelz•12mo ago

db
  .select({
    ...getTableColumns(games),
    platforms: sql`json_agg(${platforms})`.mapWith(JSON.parse),
  })
  .from(games)
  .leftJoin(gamesToPlatforms, eq(gamesToPlatforms, games.id))
...
  .leftJoin(genres, eq(genres.id, gamesToGenres.genreId))
  .where(and(eq(genres.slug, 'action'), eq(genres.id, 'whatever you want')))
  .groupBy(games.id)
  .limit(20)

db
  .select({
    ...getTableColumns(games),
    platforms: sql`json_agg(${platforms})`.mapWith(JSON.parse),
  })
  .from(games)
  .leftJoin(gamesToPlatforms, eq(gamesToPlatforms, games.id))
...
  .leftJoin(genres, eq(genres.id, gamesToGenres.genreId))
  .where(and(eq(genres.slug, 'action'), eq(genres.id, 'whatever you want')))
  .groupBy(games.id)
  .limit(20)

I'm assuming several of the names, you'll have to check if that's correct

bambam22OP•12mo ago

Okay on it

bambam22OP•12mo ago

It's not loving the platforms syntax Do I need to await that or something?

bambam22OP•12mo ago

It also doesn't like the PlatformsTable

Angelelz•12mo ago

Where are you importing sql from?

bambam22OP•12mo ago

import { sql } from 'drizzle-orm'

Angelelz•12mo ago

Weird, I'll check later today and get back with you. If you ignore the type errors, do you get the correct data back?

bambam22OP•12mo ago

ohhhh That's a good questioh Let me try when i get off work

Angelelz•12mo ago

The types should all work, see this example in the typescript playground https://www.typescriptlang.org/play?#code/JYWwDg9gTgLgBAbwCZWALzQGwKYF84BmUEIcARCultgLTQgD0IAngM4COmATGQFCiRYiFh0wAVAIYAjHABo4wAHYx5MbAA8Y+IiXKUMOOlEYjONAMbRsfAdHgJR8gObYYkmdgDCETAFcQiqzy2OzyEopI2sSkFKgGtPR8vJaB8L6s2FCscAC8cKbi0jgAFGTpmaxk8gi8cHUKSABcCsqlwEhkAJSytfWKEiDYzWqapf2DXby4nbzJEKlwkKww2XkF7iVkSytViL117c1KMG0d3ftwMMAwOMMaJ2RXN9bn9XASvjAAFtAAkk0tB4fb7QAD67Um01mKWWcCQUlycLi1GKCHwEmy4WYMzmC3KUEREgA7hJrnCpAA6DI4cwnGpvCmMlxuIpeHz+QLFfGsV71Vgkb5KJzNUQAAwAVvzFKCJE4nMUACQIbasaaiikgCRgADq1y+AB5igA3CSYEUwVCKJydXIAPkQDWain8Ukyqmutzgy0tTjCnx+UH+TpdmVwAG0ALq24oAKQAygB5AByFLAEiy2BmUIZOhAXIyWRmDJwBBgMYgSmKKuC7CrEGWrApwID-3k3Ip7U6RfqFKJX0y2GK4SQxRC+YqHaQ8gAjN04GOVRSnnJyCxLh6Xl2gA

TS Playground - An online editor for exploring TypeScript and JavaS...

The Playground lets you write TypeScript or JavaScript online in a safe and sharable way.

Angelelz•12mo ago

You'll see it if you hover over user My guess is without noticing you imported sql from another library

bambam22OP•12mo ago

Where is that platforms come from? How does drizzle understand the relationship? Things are looking better, I'm just not sure about that platforms definition, here is what I have so far

        const rawData = await db
            .select({
                ...getTableColumns(GamesTable),
                platforms: sql`json_agg(${PlatformsTable})`.mapWith(JSON.parse),
            })
            .from(GamesTable)
            .limit(20)
            .offset(page * 20)

        const rawData = await db
            .select({
                ...getTableColumns(GamesTable),
                platforms: sql`json_agg(${PlatformsTable})`.mapWith(JSON.parse),
            })
            .from(GamesTable)
            .limit(20)
            .offset(page * 20)

But I'm getting the following error

column "platforms" does not exist at eval

Any ideas?

Angelelz•12mo ago

It's your own table, I just translated from your own query

bambam22OP•12mo ago

Well yeah but like, the GamesTable doesn't have a platforms column on it. The platforms are coming from a join table Well, in your example query it doesn't seem to care about your key at all You just had something hmmmm i wonder why my query would be blowing up on that

Angelelz•12mo ago

I was under the implession that platforms was a table, not a column I used a table in my example, I'm not even sure that a correct query lol I was just following your query

bambam22OP•12mo ago

So, the PlatformsTable is but I'm just not sure about the key platforms: in your select well i was thinking that the only reference to platforms was the key but that's dumb, the actual table is platforms So why would that table not exist But why is it looking at platforms as a column I guess is my question? like, it should just be creating that column on the query okay okay okay, I think we've got it!

        const rawData = await db
            .select({
                ...getTableColumns(GamesTable),
                platforms: sql<Platform[]>`json_agg(${PlatformsTable})`,
                genres: sql<Genre[]>`json_agg(${GenresTable})`,
            })
            .from(GamesTable)
            .leftJoin(GamesToPlatforms, eq(GamesTable.id, GamesToPlatforms.gameId))
            .innerJoin(
                PlatformsTable,
                eq(GamesToPlatforms.platformId, PlatformsTable.id)
            )
            .leftJoin(
                GamesToGenresTable,
                eq(GamesTable.id, GamesToGenresTable.gameId)
            )
            .innerJoin(GenresTable, eq(GamesToGenresTable.genreId, GenresTable.id))
            .where(ilike(GamesTable.name, `%${query.searchText}%`))
            .groupBy(GamesTable.id)
            .orderBy(asc(GamesTable.createdAt))
            .limit(20)
            .offset(page * 20)

        const rawData = await db
            .select({
                ...getTableColumns(GamesTable),
                platforms: sql<Platform[]>`json_agg(${PlatformsTable})`,
                genres: sql<Genre[]>`json_agg(${GenresTable})`,
            })
            .from(GamesTable)
            .leftJoin(GamesToPlatforms, eq(GamesTable.id, GamesToPlatforms.gameId))
            .innerJoin(
                PlatformsTable,
                eq(GamesToPlatforms.platformId, PlatformsTable.id)
            )
            .leftJoin(
                GamesToGenresTable,
                eq(GamesTable.id, GamesToGenresTable.gameId)
            )
            .innerJoin(GenresTable, eq(GamesToGenresTable.genreId, GenresTable.id))
            .where(ilike(GamesTable.name, `%${query.searchText}%`))
            .groupBy(GamesTable.id)
            .orderBy(asc(GamesTable.createdAt))
            .limit(20)
            .offset(page * 20)

Well that was a trip, thanks so much for your help!

Angelelz•12mo ago

Good deal

Gaming

Programming

Map casing when using sql`` operator