Drizzle Team•2y ago

.andWhere()

I know that .andWhere() isn't a function, but I'm wondering if there's a way to start a query in one function, and return it. And then have the receiver be able to continue to add to the where clause? Something like:

function startIt() {
   return db.select().from(users).where(eq(users.developer, true));
}

const query = startIt().andWhere(eq(users.application, 'approved'));

function startIt() {
   return db.select().from(users).where(eq(users.developer, true));
}

const query = startIt().andWhere(eq(users.application, 'approved'));

12 Replies

alexblokh•2y ago

we have

.where(and(eq(users.developer, true), eq(users.application, "approved")))

.where(and(eq(users.developer, true), eq(users.application, "approved")))

we have both and and or combine operators, if that's what you need

Dan•2y ago

I think the key point here is to be able to modify the condition after it was created somewhere else And add it to the partially built query Let me think about how to best implement it with what we have

JTOP•2y ago

you are correct. the idea is not to add a .andWhere() function, but just to be able to extend a preexisting query the ORM i use in a programming language called perl, has a search() that you can call over and over again on itself.

model.search({ where clauses can go here}, {other options can go here}).search().search().search().search().search()

model.search({ where clauses can go here}, {other options can go here}).search().search().search().search().search()

It's super handy to be able to do that.

alexblokh•2y ago

we tend to keep our design as simple as possible and to not implement non-obvious behaviour I, by just looking at .search().search() don't know whether it's going to be first one or second one or both with and nor it is SQL like so you literally have to learn a specific behaviour of a particular library, yet we are very keen to stay close to 0 learning curve

Dan•2y ago

So what you can do here is the inversion of control. Instead of customising the query outside of the function, you can make the function accept the customisation pieces that you need.

function startIt(whereCallback: (condition: SQL) => SQL | undefined = (c) => c) {
    return db.select().from(users).where(whereCallback(eq(users.developer, true)));
}

const query = startIt((c) => and(c, eq(users.application, 'approved')));

function startIt(whereCallback: (condition: SQL) => SQL | undefined = (c) => c) {
    return db.select().from(users).where(whereCallback(eq(users.developer, true)));
}

const query = startIt((c) => and(c, eq(users.application, 'approved')));

So here you're accepting an optional callback into the startIt function which allows the users to customise the .where() condition. Not very good-looking, I know, but that's what you can do with the current API. We'll probably need to think about how to make it easier to use, since we initially planned the API primarily for the cases when you specify the whole query in one place.

JTOP•2y ago

wow whoever @alexblokh is really likes this answer 🙂 i don't like that you're making me think backwards from how i already think, but i absolutely love that you have a solution for this! thank you so much

Unknown User•2y ago

Message Not Public

JTOP•2y ago

@bloberenober had a reasonable solution, but I ended up finding that it was ultimately too complicated for me. So instead of passing a complete query around, I would just pass around the where clause. and that would allow me to then and(existingWhereClause, eq(someColumn, someValue)) as needed Or if you don't like that solution, another solution I came up with in another situation was to pass around an object like :

const ands = [];
ands.push(eq(someColumn, someValue));
ands.push(or(like(column1, `%${query.search}%`), like(column2, `%${query.search}%`)));
// ands gets passed around to various functions and built up over a series of complex conditional operations
// and when it comes time to merge it all together, do:
for (const item of ands) {
        if (where === undefined) {
            where = item;
        }
        else {
            where = and(where, item);
        }
    }

const ands = [];
ands.push(eq(someColumn, someValue));
ands.push(or(like(column1, `%${query.search}%`), like(column2, `%${query.search}%`)));
// ands gets passed around to various functions and built up over a series of complex conditional operations
// and when it comes time to merge it all together, do:
for (const item of ands) {
        if (where === undefined) {
            where = item;
        }
        else {
            where = and(where, item);
        }
    }

admittedly it would be easier if there was an .andWhere() option, but there are work arounds

Unknown User•2y ago

Message Not Public

alexblokh•2y ago

@atintel we're not going to add .andWhere and we've spent so much time designing our current API so you can build those helpers in the matter of minutes/hrs, we truly believe this to be the proper approach

Unknown User•2y ago

Message Not Public

Afi•17mo ago

im having a hard time migrating from typeorm without an andWhere does anybody have an utils lib for this? this works for me for now,

  const queries = [eq(flowEntity.workspaceId, workspaceId)];

  if (flowName) {
    queries.push(ilike(flowEntity.name, `%${flowName}%`));
  }
  if (status) {
    queries.push(eq(flowEntity.status, status));
  }

  const flows = db
    .select()
    .from(flowEntity)
    .where(and(...queries));
  if (limit) {
    flows.limit(limit);
  }
  const result = flows.orderBy(desc(flowEntity.createdAt)).offset(offset);
  return await result;

  const queries = [eq(flowEntity.workspaceId, workspaceId)];

  if (flowName) {
    queries.push(ilike(flowEntity.name, `%${flowName}%`));
  }
  if (status) {
    queries.push(eq(flowEntity.status, status));
  }

  const flows = db
    .select()
    .from(flowEntity)
    .where(and(...queries));
  if (limit) {
    flows.limit(limit);
  }
  const result = flows.orderBy(desc(flowEntity.createdAt)).offset(offset);
  return await result;

even if there is only a single value in and operator, it still works in the three scenarios, the sql are gentting generated accordingly. Not sure if this would break tho

{
  sql: 'select "id", "name", "status", "workspaceId", "flowFilter", "triggerFilter", "createdAt" from "flow" where "flow"."workspaceId" = $1 order by "flow"."createdAt" desc limit $2',
  params: [ '0b89b583-6714-4c24-be19-70f3a358a60b', 10 ]
}
GET /interaction-flows?_data=routes%2Finteraction-flows._index 200 - - 12.110 ms
{
  sql: 'select "id", "name", "status", "workspaceId", "flowFilter", "triggerFilter", "createdAt" from "flow" where ("flow"."workspaceId" = $1 and "flow"."name" ilike $2) order by "flow"."createdAt" desc limit $3',
  params: [ '0b89b583-6714-4c24-be19-70f3a358a60b', '%onb%', 10 ]
}
GET /interaction-flows?name=onb&_data=root 200 - - 2.608 ms
GET /interaction-flows?name=onb&_data=routes%2Finteraction-flows._index 200 - - 5.796 ms
{
  sql: 'select "id", "name", "status", "workspaceId", "flowFilter", "triggerFilter", "createdAt" from "flow" where ("flow"."workspaceId" = $1 and "flow"."name" ilike $2 and "flow"."status" = $3) order by "flow"."createdAt" desc limit $4',
  params: [ '0b89b583-6714-4c24-be19-70f3a358a60b', '%onb%', 'DRAFT', 10 ]
}

{
  sql: 'select "id", "name", "status", "workspaceId", "flowFilter", "triggerFilter", "createdAt" from "flow" where "flow"."workspaceId" = $1 order by "flow"."createdAt" desc limit $2',
  params: [ '0b89b583-6714-4c24-be19-70f3a358a60b', 10 ]
}
GET /interaction-flows?_data=routes%2Finteraction-flows._index 200 - - 12.110 ms
{
  sql: 'select "id", "name", "status", "workspaceId", "flowFilter", "triggerFilter", "createdAt" from "flow" where ("flow"."workspaceId" = $1 and "flow"."name" ilike $2) order by "flow"."createdAt" desc limit $3',
  params: [ '0b89b583-6714-4c24-be19-70f3a358a60b', '%onb%', 10 ]
}
GET /interaction-flows?name=onb&_data=root 200 - - 2.608 ms
GET /interaction-flows?name=onb&_data=routes%2Finteraction-flows._index 200 - - 5.796 ms
{
  sql: 'select "id", "name", "status", "workspaceId", "flowFilter", "triggerFilter", "createdAt" from "flow" where ("flow"."workspaceId" = $1 and "flow"."name" ilike $2 and "flow"."status" = $3) order by "flow"."createdAt" desc limit $4',
  params: [ '0b89b583-6714-4c24-be19-70f3a358a60b', '%onb%', 'DRAFT', 10 ]
}

ohh im missing the fourth scenario btw

Gaming

Programming

.andWhere()