Michael Schaufelberger Posts

Michael Schaufelberger

Explore posts from servers

TtRPC TTCTheo's Typesafe Cult XXata DTDrizzle Team

•Created by Michael Schaufelberger on 8/29/2024 in #help

How can I create a `nulls not distinct` index in Postgres 15 for `push`?

Hi 👋 I'm having trouble to create such an index. This

  (table) => ({
    uniqueIdx: unique("mytable_uniq_idx_composite").on(table.foo, table.bar).nullsNotDistinct(),
  }),

  (table) => ({
    uniqueIdx: unique("mytable_uniq_idx_composite").on(table.foo, table.bar).nullsNotDistinct(),
  }),

somehow does not create an index that has the nulls not distinct constraint. Whereas a query like this would work.

(table) => ({
    uniqueIdx: uniqueIndex('mytable_uniq_idx_composite')
      .on(table.foo, table.bar)
      .where(sql`NULLS NOT DISTINCT`),
  }),

(table) => ({
    uniqueIdx: uniqueIndex('mytable_uniq_idx_composite')
      .on(table.foo, table.bar)
      .where(sql`NULLS NOT DISTINCT`),
  }),

However, there's a WHERE clause that breaks it.

CREATE UNIQUE INDEX IF NOT EXISTS "mytable_uniq_idx_composite" ON "mytable" USING btree ("foo","bar") WHERE NULLS NOT DISTINCT;

CREATE UNIQUE INDEX IF NOT EXISTS "mytable_uniq_idx_composite" ON "mytable" USING btree ("foo","bar") WHERE NULLS NOT DISTINCT;

2 replies

DTDrizzle Team

•Created by Michael Schaufelberger on 7/10/2024 in #help

RQB: Why is a where clause inside a many-to-many relation not allowed?

Say we have posts and tags. Every post can have multiple tags, but a tag can have many posts. So a classical many-to-many relation. Note: we have a junction table to store the relation posts 1-n postsToTags n-1 tag Now we want to fetch a post with all the tags. We can just do this:

db.query.postsTbl.findMany({
  where: (postsTbl, { eq }) => {
    return eq(postsTbl.owner, authId)
  },
  with: {
    postsToTags: {
      with: {
        tag: {
          columns: {
            id: true,
            label: true,
          },
        },
      },
    },
  },
});

db.query.postsTbl.findMany({
  where: (postsTbl, { eq }) => {
    return eq(postsTbl.owner, authId)
  },
  with: {
    postsToTags: {
      with: {
        tag: {
          columns: {
            id: true,
            label: true,
          },
        },
      },
    },
  },
});

But say we only want the tags that contain "hello" in the label:

db.query.postsTbl.findMany({
  where: (postsTbl, { eq }) => {
    return eq(postsTbl.owner, authId);
  },
  with: {
    postsToTags: {
      with: {
        tag: {
          // @ts-ignore
          where: (_, { like }) => like(tagsTbl.label, "%hello%"),
          columns: {
            id: true,
            label: true,
          },
        },
      },
    },
  },
});

db.query.postsTbl.findMany({
  where: (postsTbl, { eq }) => {
    return eq(postsTbl.owner, authId);
  },
  with: {
    postsToTags: {
      with: {
        tag: {
          // @ts-ignore
          where: (_, { like }) => like(tagsTbl.label, "%hello%"),
          columns: {
            id: true,
            label: true,
          },
        },
      },
    },
  },
});

Why is it not allowed in the types? Is there something that could easily break if we add a where clause to the subquery?

1 replies

DTDrizzle Team

•Created by Michael Schaufelberger on 5/12/2023 in #help

MySQL (Planetscale): Cannot read properties of undefined (reading 'name')

Hi Hopefully someone can help me. I'm getting the above error in my insert query.

try {
  const sql = ctx.db.insert(ticketInvitations).values([
    {
      guestId: 1,
      ticketTypeId: '6458eeb763aee9ab82d8b79b',
      eventId: 1,
      amount: 1,
      status: 'pending' as const,
    }
  ]).toSQL();
  console.log(sql);
} catch (error) {
  console.error(error);
}

try {
  const sql = ctx.db.insert(ticketInvitations).values([
    {
      guestId: 1,
      ticketTypeId: '6458eeb763aee9ab82d8b79b',
      eventId: 1,
      amount: 1,
      status: 'pending' as const,
    }
  ]).toSQL();
  console.log(sql);
} catch (error) {
  console.error(error);
}

I have no idea what I'm doing wrong...

34 replies

Gaming

Programming