OYΞD Posts - Answer Overflow

OYΞD

Explore posts from servers

•Created by OYΞD on 9/12/2024 in #help

Struggling with a GROUP BY count

with this query,

const data = await db
  .select({
    ...getTableColumns(songHistory),
    count: sql<number>`cast(count(${songHistory.id}) as int)`,
  })
  .from(songHistory)
  .where(and(eq(songHistory.user_id, user!).if(typeof user === 'number'), gte(songHistory.played_at, from!).if(!!from)))
  .groupBy(songHistory.song_title)
  .orderBy(desc(songHistory.played_at));

const data = await db
  .select({
    ...getTableColumns(songHistory),
    count: sql<number>`cast(count(${songHistory.id}) as int)`,
  })
  .from(songHistory)
  .where(and(eq(songHistory.user_id, user!).if(typeof user === 'number'), gte(songHistory.played_at, from!).if(!!from)))
  .groupBy(songHistory.song_title)
  .orderBy(desc(songHistory.played_at));

i'm getting the error,

column "song_history.id" must appear in the GROUP BY clause or be used in an aggregate function

column "song_history.id" must appear in the GROUP BY clause or be used in an aggregate function

which i'm a bit confused on? i'm using the id col is in the aggregate function already

4 replies

DTDrizzle Team

•Created by OYΞD on 3/8/2024 in #help

Struggling to work with JSONB `where`

So I have a jsonb column in Postgres (Using the Neon HTTP driver), and I'm trying to run a query to filter out based on the jsonb column:

await db()
  .select({ id: sites.id })
  .from(sites)
  .where(sql`metadata @> '{"key":"${key}"}'::jsonb`)
  .then(takeFirst);

await db()
  .select({ id: sites.id })
  .from(sites)
  .where(sql`metadata @> '{"key":"${key}"}'::jsonb`)
  .then(takeFirst);

Neon keeps coming back with the following error:

NeonDbError: bind message supplies 1 parameters, but prepared statement "" requires 0

NeonDbError: bind message supplies 1 parameters, but prepared statement "" requires 0

3 replies

DTDrizzle Team

•Created by OYΞD on 4/23/2023 in #help

ERR_PACKAGE_PATH_NOT_EXPORTED on 0.25.1

12 replies

DTDrizzle Team

•Created by OYΞD on 4/11/2023 in #help

Selecting multiple relating rows

A more generic SQL question (Been out of SQL a while now 🙃 ) Given the following schema:

import { index, int, mysqlTable, serial, varchar } from 'drizzle-orm/mysql-core/index.js';

export const systems = mysqlTable('systems', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
});

export const stars = mysqlTable(
  'stars',
  {
    id: serial('id').primaryKey(),
    system_id: int('system_id').notNull(),
    name: varchar('name', { length: 256 }).notNull(),
  },
  table => ({
    systemIdIdx: index('system_id_idx').on(table.system_id),
  }),
);

import { index, int, mysqlTable, serial, varchar } from 'drizzle-orm/mysql-core/index.js';

export const systems = mysqlTable('systems', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
});

export const stars = mysqlTable(
  'stars',
  {
    id: serial('id').primaryKey(),
    system_id: int('system_id').notNull(),
    name: varchar('name', { length: 256 }).notNull(),
  },
  table => ({
    systemIdIdx: index('system_id_idx').on(table.system_id),
  }),
);

What would be the most efficient method of fetching all systems and all of their relating stars? In SQL (Not sure how), or by just fetching all rows from both tables separately and stitching them together afterwards, given all rows will be fetched regardless?

22 replies

DTDrizzle Team

•Created by OYΞD on 3/29/2023 in #help

Randomly stopped being able to generate migrations (MySQL)

Hey! Been trying things out the past day and everything was going great - until I deleted my migrations folder, attempted to re-generate migrations, and was met with 0 tables 🤣 I've killed-off all of my schema, except for one simple table, and ran generate:mysql with DEBUG;

> drizzle-kit "generate:mysql" "--schema" "orm/src/schema" "--out" "orm/migrations"

drizzle-kit: v0.17.1
drizzle-orm: v0.23.2

  esbuild-register compiled /home/oyed/sites/teris/orm/src/schema/index.ts +0ms
  esbuild-register const __esbuild_register_import_meta_url__ = require('url').pathToFileURL(__filename).href;
var __defProp = Object.defineProperty;
var __getOwnPropDesc = Object.getOwnPropertyDescriptor;
var __getOwnPropNames = Object.getOwnPropertyNames;
var __hasOwnProp = Object.prototype.hasOwnProperty;
var __export = (target, all) => {
  for (var name in all)
    __defProp(target, name, { get: all[name], enumerable: true });
};
var __copyProps = (to, from, except, desc) => {
  if (from && typeof from === "object" || typeof from === "function") {
    for (let key of __getOwnPropNames(from))
      if (!__hasOwnProp.call(to, key) && key !== except)
        __defProp(to, key, { get: () => from[key], enumerable: !(desc = __getOwnPropDesc(from, key)) || desc.enumerable });
  }
  return to;
};
var __toCommonJS = (mod) => __copyProps(__defProp({}, "__esModule", { value: true }), mod);
var schema_exports = {};
__export(schema_exports, {
  guilds: () => guilds
});
module.exports = __toCommonJS(schema_exports);
var import_mysql_core = require("drizzle-orm/mysql-core");
const guilds = (0, import_mysql_core.mysqlTable)("guilds", {
  id: (0, import_mysql_core.serial)("id").primaryKey(),
  faction_id: (0, import_mysql_core.serial)("faction_id").notNull(),
  user_id: (0, import_mysql_core.serial)("user_id").notNull(),
  name: (0, import_mysql_core.varchar)("name", { length: 256 }).notNull(),
  ticker: (0, import_mysql_core.varchar)("ticker", { length: 3 }).notNull(),
  created_at: (0, import_mysql_core.timestamp)("created_at").defaultNow()
}, (table) => ({
  factionIdIdx: (0, import_mysql_core.index)("faction_id_idx").on(table.faction_id),
  userIdIdx: (0, import_mysql_core.uniqueIndex)("user_id_idx").on(table.user_id),
  tickerIdx: (0, import_mysql_core.uniqueIndex)("ticker_idx").on(table.ticker)
}));
//# sourceMappingURL=[redacted so discord will let me post this in-line]
 +1ms
0 tables


No schema changes, nothing to migrate 😴

> drizzle-kit "generate:mysql" "--schema" "orm/src/schema" "--out" "orm/migrations"

drizzle-kit: v0.17.1
drizzle-orm: v0.23.2

  esbuild-register compiled /home/oyed/sites/teris/orm/src/schema/index.ts +0ms
  esbuild-register const __esbuild_register_import_meta_url__ = require('url').pathToFileURL(__filename).href;
var __defProp = Object.defineProperty;
var __getOwnPropDesc = Object.getOwnPropertyDescriptor;
var __getOwnPropNames = Object.getOwnPropertyNames;
var __hasOwnProp = Object.prototype.hasOwnProperty;
var __export = (target, all) => {
  for (var name in all)
    __defProp(target, name, { get: all[name], enumerable: true });
};
var __copyProps = (to, from, except, desc) => {
  if (from && typeof from === "object" || typeof from === "function") {
    for (let key of __getOwnPropNames(from))
      if (!__hasOwnProp.call(to, key) && key !== except)
        __defProp(to, key, { get: () => from[key], enumerable: !(desc = __getOwnPropDesc(from, key)) || desc.enumerable });
  }
  return to;
};
var __toCommonJS = (mod) => __copyProps(__defProp({}, "__esModule", { value: true }), mod);
var schema_exports = {};
__export(schema_exports, {
  guilds: () => guilds
});
module.exports = __toCommonJS(schema_exports);
var import_mysql_core = require("drizzle-orm/mysql-core");
const guilds = (0, import_mysql_core.mysqlTable)("guilds", {
  id: (0, import_mysql_core.serial)("id").primaryKey(),
  faction_id: (0, import_mysql_core.serial)("faction_id").notNull(),
  user_id: (0, import_mysql_core.serial)("user_id").notNull(),
  name: (0, import_mysql_core.varchar)("name", { length: 256 }).notNull(),
  ticker: (0, import_mysql_core.varchar)("ticker", { length: 3 }).notNull(),
  created_at: (0, import_mysql_core.timestamp)("created_at").defaultNow()
}, (table) => ({
  factionIdIdx: (0, import_mysql_core.index)("faction_id_idx").on(table.faction_id),
  userIdIdx: (0, import_mysql_core.uniqueIndex)("user_id_idx").on(table.user_id),
  tickerIdx: (0, import_mysql_core.uniqueIndex)("ticker_idx").on(table.ticker)
}));
//# sourceMappingURL=[redacted so discord will let me post this in-line]
 +1ms
0 tables


No schema changes, nothing to migrate 😴

So at the very least, the paths I'm giving seem to work fine, as it can transpile the schema + outputs a journal with zero entries under the migrations path

10 replies

Gaming

Programming