Drizzle Team•12mo ago

Dynamic Drizzle Relational Queries

Hi everybody, hope all is well. I was wondering if there is a way to build dynamic queries when using drizzle (relational) queries. I read the section on dynamic query building but it doesn't cover relational queries. I'm building a rest API and I want users to be able to pass filters via query params. I have a tours table, tours can have many images as well as many start dates. Is there a way of doing something like this? And make it flexible enough so that the user can filter based on various operations for different columns (ie: equal, lt, lte, gt, gte, like, etc...).

// pseudo code

db.query.tours.findMany({
  where: <dynamically generated where filters for tour>,
  with: { images: {
    where: <dynamically generated where filters for images>
  }, startDates: {
    where: <dynamically generated where filters for start dates>
  } },
})

// pseudo code

db.query.tours.findMany({
  where: <dynamically generated where filters for tour>,
  with: { images: {
    where: <dynamically generated where filters for images>
  }, startDates: {
    where: <dynamically generated where filters for start dates>
  } },
})

How would you solve this? Thanks in advance, this would help me a lot.

5 Replies

ericmartinezr•12mo ago

I have something similar in my code, it may be useful

const table1Conditions: SQLWrapper[] = [];
const table2Conditions: SQLWrapper[] = [];

if (frontendFilter1) {
    table1Conditions.push( inArray(table1.field, [1,2,3]) );
}

if (frontendFilter2) {
    table2Conditions.push( eq(table2.field, frontendFilter2) );
}

db.query.table1.findMany({
    where: and(...table1Conditions),
    with {
        table2: {
            where: and(...table2Conditions)
        }
    }
});

const table1Conditions: SQLWrapper[] = [];
const table2Conditions: SQLWrapper[] = [];

if (frontendFilter1) {
    table1Conditions.push( inArray(table1.field, [1,2,3]) );
}

if (frontendFilter2) {
    table2Conditions.push( eq(table2.field, frontendFilter2) );
}

db.query.table1.findMany({
    where: and(...table1Conditions),
    with {
        table2: {
            where: and(...table2Conditions)
        }
    }
});

If the arrays are empty the where condition won't even show up so it's not a problem. Hopefully it makes sense.

Angelelz•12mo ago

The problem with this approach is that the types will be wrong You need to wrap your findMany call in a generic fuction for it to properly infer the types dynamically I'll put together an example as this seems to be an issue people run into frequently and don't know how to figure it out

Angelelz•12mo ago

I took the example asked for here: https://github.com/drizzle-team/drizzle-orm/discussions/1483

GitHub

Relations input · drizzle-team drizzle-orm · Discussion #1483

I have a schema that looks like somewhat like this: export const employeesSchema = pgTable('employees', { firstName: varchar('first_name', { length: 256 }), lastName: varchar('l...

Angelelz•12mo ago

Here is a working implementation: https://www.typescriptlang.org/play?#code/JYWwDg9gTgLgBAbzmA5gFQIYCMA2BTAGjgDcMoBjACzKIFdbgATOAXzgDMoIQ4AiRqMABeQ-AFpoIAPSox5aHl4AoUJFiI4A4aLxtO3PlpHjJMiAGcYKKHnNiAVueWro8JDZwYYwCADtzRHgAjqwcXDz8gsZ4ElAgykp4AB5q8PL+8HjgOBAAnni2AMpUWRhwALzI6Nj4ABQA5FlgOfm29UQISnBwTABccPRMDUz1AJQEXRzAUJYAchggeP2kFNRQDezTlgD6vgt47Rr4vigwlP0ATACsAGys45Oec-vLZFRkDU8wu-uHSMenc5wa53FgPbqlYA4V6rD6NEAYKF-OAAs6XW73AB0tF8wCCtDwtXBcAwpBgZAAkox+oNGA1SV4yNsRqNsbj8YSHmCANxKRIpVxwdKWZCUCAwCzFSilCpVTC4Qn1MBiiXmP6Tbp9AYMOn1FlKMF85KpIV+EVNFoFABKeE83jNso8Xh8-lqFryBXMUtKRFqSD8hDgCN8uXuFQAfHA-ZMGeSoP0A7VleLJSUER0NVNbYxzP0ANru1petMYTGxymMAC6E263Rs7DwNl85Fs+eTqu9CMxTGrkzBNdFKdzQYwIaTKtT0oRXNGo15SmF8EYWFlRh0frYGHMJJDHXMJf6CELns7GCI7cnPrgx7wNrtLvMLENShguTAeDgAHVgGcKb4wLQ8CVLMfizLQOCeAqAA8AAKZD7DAjbmFBr7vhA7CaFgeb1ByUC5PUlbYTexZThgBHYZsviMAAYlsMAEeGeYAAyVox9QAO4-pQBF8luuTNhwOLkPavhwCgeAwAAotkHp4AAQrkVJQWg35nNeSSIVR26qZQf4ATA4a1FqliCCcRCcb+-6Af0KlcaMiCTDYMC0FAonLpiuG5JixGnpilE0XR0a1nA7HSjY-TBG6MlFr5TBEEwxLdBZQLJXpgF9nOBp8ouAzmI2soYOxiLwOJUnRQUClUrUvAANb2LwHTlvGcAwFABKGsFcBSFIcAAHoAPxAA

TS Playground - An online editor for exploring TypeScript and JavaS...

The Playground lets you write TypeScript or JavaScript online in a safe and sharable way.

mono2712OP•12mo ago

@Angelelz I created this buildAndConditions function to generate the where, but typed it as any would you happen to know how I could type this properly?

    const testQueryObject = {
      price: { gte: 2000 },
      difficulty: 'medium',
    };


// how would you type this without using any?

    const buildAndConditions = ({ table, operations, queryObject }: any) => {
      const conditions = [];

      for (const key in queryObject) {
        const filter = queryObject[key];

        if (filter instanceof Object) {
          Object.entries(filter).forEach(([operator, value]) => {
            conditions.push(operations[operator](table[key], value));
          });
        } else {
          conditions.push(operations.eq(table[key], filter));
        }
      }

      return operations.and(...conditions);
    };

    const query = db.query.tours.findMany({
      where: (tours, operations) => {
        const conditions = buildAndConditions({
          table: tours,
          operations,
          queryObject: testQueryObject,
        });

        return conditions;
      },
      with: {
        images: true,
        startDates: true,
      },
    });

    const testQueryObject = {
      price: { gte: 2000 },
      difficulty: 'medium',
    };


// how would you type this without using any?

    const buildAndConditions = ({ table, operations, queryObject }: any) => {
      const conditions = [];

      for (const key in queryObject) {
        const filter = queryObject[key];

        if (filter instanceof Object) {
          Object.entries(filter).forEach(([operator, value]) => {
            conditions.push(operations[operator](table[key], value));
          });
        } else {
          conditions.push(operations.eq(table[key], filter));
        }
      }

      return operations.and(...conditions);
    };

    const query = db.query.tours.findMany({
      where: (tours, operations) => {
        const conditions = buildAndConditions({
          table: tours,
          operations,
          queryObject: testQueryObject,
        });

        return conditions;
      },
      with: {
        images: true,
        startDates: true,
      },
    });

Gaming

Programming

Dynamic Drizzle Relational Queries