nelsonprsousa Posts - Answer Overflow

nelsonprsousa

Explore posts from servers

•Created by nelsonprsousa on 3/27/2024 in #help

Sub query with sql magic operator (for common table expression)

Hi friends, I have the following CTE that I can express using the magic sql operator:

const statement = sql`
    WITH RECURSIVE location_tree AS
      (SELECT *
      FROM locations
      WHERE id = 1
      UNION ALL SELECT l.*
      FROM locations l
      INNER JOIN location_tree lt ON l.parent_id = lt.id)
    SELECT DISTINCT package.*
    FROM PACKAGE
    INNER JOIN packages_locations ON package.id = packages_locations.package_id
    INNER JOIN location_tree ON packages_locations.location_id = location_tree.id;`;

const statement = sql`
    WITH RECURSIVE location_tree AS
      (SELECT *
      FROM locations
      WHERE id = 1
      UNION ALL SELECT l.*
      FROM locations l
      INNER JOIN location_tree lt ON l.parent_id = lt.id)
    SELECT DISTINCT package.*
    FROM PACKAGE
    INNER JOIN packages_locations ON package.id = packages_locations.package_id
    INNER JOIN location_tree ON packages_locations.location_id = location_tree.id;`;

This statement should return the package table. Now, I'd like to include this expression as a table, in order to be able to join it, something like this:

    const result= await db
      .select()
      .from(packages)
      .innerJoin("subQuery (above)", eq("subQuery (above)".packageId, packages.id));

    const result= await db
      .select()
      .from(packages)
      .innerJoin("subQuery (above)", eq("subQuery (above)".packageId, packages.id));

Are we able to do it? Maybe with some alias?! Thank you

3 replies

DTDrizzle Team

•Created by nelsonprsousa on 3/23/2024 in #help

How to alter database with drizzle (sql magic operator)?

Hi everyone, I am trying to use the sql magic operator to alter a postgres database, but no luck yet. Here's what I am trying to do:

await db.execute(
    sql`ALTER DATABASE ${databaseName} SET pg_trgm.similarity_threshold = 0.2;`
  );

await db.execute(
    sql`ALTER DATABASE ${databaseName} SET pg_trgm.similarity_threshold = 0.2;`
  );

However, I am getting PostgresError: syntax error at or near "$1", looks like this operator does not support to use variables when using ALTER DATABASE. Any solution on how to actually perform this operation? It is only running on script (CI/CD), it is not exposed to the public, so security here isn't a huge concern. Thank you!

2 replies

DTDrizzle Team

•Created by nelsonprsousa on 2/27/2024 in #help

What is nameIndex?

In the docs we have this example:

export const countries = pgTable('countries', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
}, (countries) => {
  return {
    nameIndex: uniqueIndex('name_idx').on(countries.name),
  }
});

export const countries = pgTable('countries', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
}, (countries) => {
  return {
    nameIndex: uniqueIndex('name_idx').on(countries.name),
  }
});

What does nameIndex mean here? Is it a random text or it actually means something? Also, let's say we have another column called chineseName for example. If an app can search using a where statement with the name columns and, on another query, use a where clause with the chineseName, how should we treat indices? Thank you

1 replies

DTDrizzle Team

•Created by nelsonprsousa on 2/27/2024 in #help

How to reference the same table in the table's definition?

2 replies

DTDrizzle Team

•Created by nelsonprsousa on 11/6/2023 in #help

Foreign key truncated for being too long?

Hi, I have multiple warnings / notices:

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42622',
  message: 'identifier "packagesToCategories_package_city_region_country_countryLanguages" will be truncated to "packagesToCategories_package_city_region_country_countryLanguag"',
  file: 'scansup.c',
  line: '99',
  routine: 'truncate_identifier'
}

Is there a way to name this identifier or silence this warning? Otherwise I am afraid this will start polluting our logs. Thank you

3 replies

DTDrizzle Team

•Created by nelsonprsousa on 10/27/2023 in #help

How to filter query based on children table?

6 replies

DTDrizzle Team

•Created by nelsonprsousa on 10/25/2023 in #help

How to run migration script?

I am looking into this doc (https://orm.drizzle.team/kit-docs/overview#running-migrations) regarding running migrations and you provide a script that we can call. My question is how can we call this script outside our app. Maybe through the bash/terminal?

8 replies

DTDrizzle Team

•Created by nelsonprsousa on 10/24/2023 in #help

How to express "children" or "parent" relation with typescript?

Let's say I have a country and region tables. A region belongs to a country, so on the regiontable, there's a foreign key country_id to the country table. The question is if it is possible to have a Country type generated like this:

type Country {
  id: number;
}

type Region {
  id: number;
  country_id: number;
  country: Country; // This one is the difficult/impossible one to get? 
}

type Country {
  id: number;
}

type Region {
  id: number;
  country_id: number;
  country: Country; // This one is the difficult/impossible one to get? 
}

The goal is to be able to query a region and join it with the country table as well. Thank you!

17 replies

Gaming

Programming