VRN Comments - Answer Overflow

VRN

Posts Comments

DTDrizzle Team

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

SQLite json_each

thanks @Angelelz

23 replies

DTDrizzle Team

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

SQLite json_each

It works now. thanks! Although isn't this approach a bit inorganic?

23 replies

DTDrizzle Team

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

SQLite json_each

sorry missed out no single quotes

23 replies

DTDrizzle Team

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

SQLite json_each

Query: select distinct ch.value from "channels", json_each("channels"."participants") ch where ch.value like '?' -- params: ["%some-id%"]

Still giving me empty result

23 replies

DTDrizzle Team

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

SQLite json_each

oh interesting. let me try

23 replies

DTDrizzle Team

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

SQLite json_each

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?

23 replies

DTDrizzle Team

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

SQLite json_each

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 !

23 replies

DTDrizzle Team

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

SQLite json_each

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

23 replies

DTDrizzle Team

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

SQLite json_each

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

23 replies

Gaming

Programming