DT
Drizzle Team•4mo ago
sativo

Why does the same query not work using `select`?

I'm using SQLite and the "tags" field is a json data type. This the following works fine: 
const statement = sql`select * from ${items}, json_each(${items.tags}) as "jsonTags"`
  .append(sql` where "jsonTags"."value" ->> '$.name' = 'hello'`)

await searchDB().all(statement);
const statement = sql`select * from ${items}, json_each(${items.tags}) as "jsonTags"`
  .append(sql` where "jsonTags"."value" ->> '$.name' = 'hello'`)

await searchDB().all(statement);
Using select throws an error: 
const columns = getTableColumns(items);

await db
  .select(columns)
  .from(sql`${items}, json_each(${items.tags}) as "jsonTags"`)
  .where(sql` "jsonTags"."value" ->> '$.name' = 'hello'`)
const columns = getTableColumns(items);

await db
  .select(columns)
  .from(sql`${items}, json_each(${items.tags}) as "jsonTags"`)
  .where(sql` "jsonTags"."value" ->> '$.name' = 'hello'`)
Error: 
Your "id" field references a column "items"."id", but the table "items" is not part of the query! Did you forget to join it?
Your "id" field references a column "items"."id", but the table "items" is not part of the query! Did you forget to join it?
1 Reply
Нарбек
Нарбек•3mo ago
You can use toSql drizzle method to compare them

Did you find this page helpful?