VRN Posts - Answer Overflow

VRN

•Created by VRN on 11/7/2023 in #help

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!!

23 replies

Gaming

Programming