DT
Drizzle TeamVRN

SQLite json_each

Hi. I wandted to use the json_each function in sqlite, but I dont think the query is building correctly, could someone please guide me in the right direction? My table: 
export const channels = sqliteTable('channels', {
    id: text('id').primaryKey().notNull(),
    channelId: text('channelId').notNull(),
    ownerId: text('ownerId').notNull(),
    participants: text('participants', {mode: 'json'}).$type<string[]>().$default(() => []),
    channelName: text('channelName').notNull(),
    lastMessage: text('lastMessage').notNull(),
    lastMessageTimestamp: integer('lastMessageTimestamp', { mode: 'timestamp_ms' }).notNull(),
});
export const channels = sqliteTable('channels', {
    id: text('id').primaryKey().notNull(),
    channelId: text('channelId').notNull(),
    ownerId: text('ownerId').notNull(),
    participants: text('participants', {mode: 'json'}).$type<string[]>().$default(() => []),
    channelName: text('channelName').notNull(),
    lastMessage: text('lastMessage').notNull(),
    lastMessageTimestamp: integer('lastMessageTimestamp', { mode: 'timestamp_ms' }).notNull(),
});
My query: 
const r = await db
  .select()
  .from(sql`channels, json_each(channels.participants)`)
  .where(or(
    eq(channels.ownerId, userId),
    sql`json_each.value LIKE '${userId}'`
  ));
return r as Channel[] // <-- loosing any type defs
const r = await db
  .select()
  .from(sql`channels, json_each(channels.participants)`)
  .where(or(
    eq(channels.ownerId, userId),
    sql`json_each.value LIKE '${userId}'`
  ));
return r as Channel[] // <-- loosing any type defs
The sql that ends up running is: 
Query: select  from channels, json_each(channels.participants) where ("channels"."ownerId" = ? or json_each.value LIKE '?') -- params: ["user1", "user1"]
2023-11-06T18:01:27.996962Z ERROR sqlite3Parser: near FROM, "Token(None)": syntax error 
Query: select  from channels, json_each(channels.participants) where ("channels"."ownerId" = ? or json_each.value LIKE '?') -- params: ["user1", "user1"]
2023-11-06T18:01:27.996962Z ERROR sqlite3Parser: near FROM, "Token(None)": syntax error
There doesnt seem to be any columns selected (I tried defining the cols manually as well but didnt work) Any help would be much appricated!!
Angelelz
Angelelz•194d ago
You can only use .select() if your from clause is a table, a subquery or a view. It's the only way drizzle can infer your selection What was the problem when you selected the columns?
VRN
VRN•193d ago
if I select a col: 
.select({ id: channels.id })
.select({ id: channels.id })
I get the error Your "id" field references a column "channels"."id", but the table "channels" is not part of the query! Did you forget to join it
Angelelz
Angelelz•193d ago
I don't think you'll be able to construct your query this way unfortunately. The from method doesn't support more than one parameter Oh wait, try this: 
.select({ id: sql<string>`${channels.id}` }
.select({ id: sql<string>`${channels.id}` }
VRN
VRN•193d ago
If i do this: 
.select({id: sql<string>`${channels.id}`,})
.select({id: sql<string>`${channels.id}`,})
I get the following error: 
Query: select "id", "channelId" from channels, json_each(channels.participants) where ("channels"."ownerId" = ? or json_each.value LIKE '?') -- params: ["user1", "user1"]
 6 |     rawCode;
 7 |     constructor(message, code, rawCode, cause) {
 8 |         if (code !== undefined) {
 9 |             message = `${code}: ${message}`;
10 |         }
11 |         super(message, { cause });
            ^
LibsqlError: SQLITE_ERROR: ambiguous column name: id
 code: "SQLITE_ERROR"
Query: select "id", "channelId" from channels, json_each(channels.participants) where ("channels"."ownerId" = ? or json_each.value LIKE '?') -- params: ["user1", "user1"]
 6 |     rawCode;
 7 |     constructor(message, code, rawCode, cause) {
 8 |         if (code !== undefined) {
 9 |             message = `${code}: ${message}`;
10 |         }
11 |         super(message, { cause });
            ^
LibsqlError: SQLITE_ERROR: ambiguous column name: id
 code: "SQLITE_ERROR"
but with 
.select({channelId: sql<string>`${channels.channelId}`,})
.select({channelId: sql<string>`${channels.channelId}`,})
no error it goes thru, but no results either
Angelelz
Angelelz•193d ago
Maybe like this? 
.select({channelId: sql.raw<string>("channels"."channelId"))
.select({channelId: sql.raw<string>("channels"."channelId"))
I can't test this right now, so I'm just throwing out ideas
VRN
VRN•190d ago
Ok I got it to work like this: 
db
  .select({ id: sql<string>`channels.id`,
            channelId:sql<string>`${channels.channelId}`,
      })
    .from(sql`channels, json_each(channels.participants) ch`)
    .where(or(
        eq(channels.ownerId, userId),
        sql`ch.value LIKE ${userId}`
    ));
db
  .select({ id: sql<string>`channels.id`,
            channelId:sql<string>`${channels.channelId}`,
      })
    .from(sql`channels, json_each(channels.participants) ch`)
    .where(or(
        eq(channels.ownerId, userId),
        sql`ch.value LIKE ${userId}`
    ));
Still cannot use id: channels.id or id: sql<string>'${channels.id}' wierdly. Only throwing ambiguous id error only for id field. Thanks for all your help @Angelelz ! Hi Team, a new issue regarding this type of query. It seems the query params are not being passed in properly. With the following query: 
await db.selectDistinct({jval: sql<string>`ch.value`})
      .from(sql<string>`${channels}, json_each(${channels.participants}) ch`)
.where(sql`ch.value like '%${userId}%`)
await db.selectDistinct({jval: sql<string>`ch.value`})
      .from(sql<string>`${channels}, json_each(${channels.participants}) ch`)
.where(sql`ch.value like '%${userId}%`)
The sql generated is: 
Query: select "id", "createdAt", "updatedAt", "name", "domain", "databaseName", "databaseAuthToken" from "organizations" where "organizations"."domain" = ? -- params: ["hookzapp.com"]
sql {
  sql: "select distinct ch.value from \"channels\", json_each(\"channels\".\"participants\") ch where ch.value like '%?%'",
  params: [ "some-user-id-1" ]
}
Query: select "id", "createdAt", "updatedAt", "name", "domain", "databaseName", "databaseAuthToken" from "organizations" where "organizations"."domain" = ? -- params: ["hookzapp.com"]
sql {
  sql: "select distinct ch.value from \"channels\", json_each(\"channels\".\"participants\") ch where ch.value like '%?%'",
  params: [ "some-user-id-1" ]
}
This query is returning an empty result [] But if I manually substitute the string in the query, it works: 
await db.selectDistinct({jval: sql<string>`ch.value`})
      .from(sql<string>`${channels}, json_each(${channels.participants}) ch`)
.where(sql`ch.value like '%some-user-id-1%`)
await db.selectDistinct({jval: sql<string>`ch.value`})
      .from(sql<string>`${channels}, json_each(${channels.participants}) ch`)
.where(sql`ch.value like '%some-user-id-1%`)
Generated SQL 
Query: select distinct ch.value from "channels", json_each("channels"."participants") ch where ch.value like '%some-user-id-1%'
sql {
  sql: "select distinct ch.value from \"channels\", json_each(\"channels\".\"participants\") ch where ch.value like '%some-user-id-1%'",
  params: []
}
Query: select distinct ch.value from "channels", json_each("channels"."participants") ch where ch.value like '%some-user-id-1%'
sql {
  sql: "select distinct ch.value from \"channels\", json_each(\"channels\".\"participants\") ch where ch.value like '%some-user-id-1%'",
  params: []
}
Result is: 
[
  {
    jval: "{\"id\":\"some-user-id-1\",\"name\":\"User 2\"}"
  }
]
[
  {
    jval: "{\"id\":\"some-user-id-1\",\"name\":\"User 2\"}"
  }
]
Is this a bug or is there something I am doing wrong with respect to params?
Angelelz
Angelelz•190d ago
Your problem is that you need to pass the parameter with the % already in 
.where(sql`ch.value like '${'%' + userId + '%'}`)
.where(sql`ch.value like '${'%' + userId + '%'}`)
VRN
VRN•190d ago
oh interesting. let me try
Angelelz
Angelelz•190d ago
.where(sql`ch.value like ${'%' + userId + '%'}`)
.where(sql`ch.value like ${'%' + userId + '%'}`)
VRN
VRN•190d ago
Query: select distinct ch.value from "channels", json_each("channels"."participants") ch where ch.value like '?' -- params: ["%some-id%"] Still giving me empty result sorry missed out no single quotes It works now. thanks! Although isn't this approach a bit inorganic?
Angelelz
Angelelz•190d ago
There is no way for drizzle to detect that you have % around your parameters I mean, there is, but it would add a lot of complexity For now it has to be done in user land. Maybe later we could offer some nice helpers
VRN
VRN•190d ago
thanks @Angelelz
Want results from more Discord servers?
Add your server
More Posts
Foreign key truncated for being too long?Hi, I have multiple warnings / notices: `{ severity_local: 'NOTICE', severity: 'NOTICE', codedrizzle-zod with custom typesI have defined opaque types in my application and have implemented those in the drizzle schema (awesUsing transactions in automated testing like JestI have a testing set-up as follows: 1. Start postgres container 2. Apply migrations to db 3. Jest tTypeScript complaining about using a spread operator in partial select queryTypeScript complaining about me using a spread operator in partial select query. I have the followiUnable to read error message when inserting withdb.insert().values()Hey there, i am using visual studio code and am developing with nuxt3. I am trying to insert an objehow to add new driver to drizzle?what is the procedure of adding a new driver to drizzle orm?`drizzle-kit --custom` is not giving me a blank migration fileI'm doing the following: ```sh drizzle-kit generate:pg --custom ``` But it's not giving me a blankNo id returned (mysql2)``` async function create(data: NewBusiness) { return client.insert(business).values(data) } ``` Studio when using Turso `Error: Cannot find module 'better-sqlite3'`Error when trying to start Studio and push `Error: Cannot find module 'better-sqlite3'`[BUG]: React Hook Form doesn't submit when using drizzle-zod to create schemes to use in zodResolverWhen using `drizzle-zod` to generate schemas from database schemas and using them in the `useForm` rAppending SQL chunks to a queryHey everyone, I'm trying to see if there is a way where I can build SQL queries conditionally (basedPostgresjs with Neon giving connnection refusal errorI am unable to connect to my neon database. I tried using `postgresjs` and the `neon serverless` drierror: column "id" cannot be cast automatically to type uuidim using drizzle, and this is my schema: ```typescript export const tags = pgTable("tags", { id: uinArray() using sql`` operator in JS and PostgresHey, how do I use sql operator with arrays in JS? ```javascript const array = [1,2,3]; const sqlQuerUnable to infer relationI have the following tables and relations: ```javascript export const platforms = pgTable('platformsmigrate with node-postgresI had migration working well with postgresjs but need to switch to node-postgres for other reasons. .all() API not availableI have two tables that I want to join, and I want to join them to get the following result: ```javasGet subquery columns in a way similar to `getTableColumns`Keeping track manually of all subquery fields can quickly become annoying, and I'm looking for a simSlow queries with relationshipsHi all, I recently moved from querying 4 tables concurrently, to using drizzle relations and using dCannot read properties of undefined (reading 'referencedTable')const result = await db.query.ads.findMany({ with: { owner: true