Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

add querybuilder auto-join support for update queries #319

Closed
ogrodnek opened this issue Jan 30, 2020 · 2 comments
Closed

add querybuilder auto-join support for update queries #319

ogrodnek opened this issue Jan 30, 2020 · 2 comments
Labels
enhancement New feature or request
Milestone
4.0

Comments

@ogrodnek
Copy link

Is your feature request related to a problem? Please describe.

The querybuilder auto-join support is really great! Trying to run as part of select works fine, i.e.

qb.select("*").where({activity: {enabled: true}})

select "e0".* from "tasks" as "e0" left join "activities" as "e1" on "e0"."activity_id" = "e1"."id" where "e1"."enabled" = $1

but changing to use update, i.e.

qb.update({someField: someValue})

produces an error:

    TypeError: Cannot read property 'slice' of undefined
at ObjectCriteriaNode.autoJoin (node_modules/mikro-orm/dist/query/CriteriaNode.js:205:37)
      at ObjectCriteriaNode.process (node_modules/mikro-orm/dist/query/CriteriaNode.js:159:26)
      at node_modules/mikro-orm/dist/query/CriteriaNode.js:163:39
          at Array.reduce (<anonymous>)
      at ObjectCriteriaNode.process (node_modules/mikro-orm/dist/query/CriteriaNode.js:161:42)
      at QueryBuilder.where (node_modules/mikro-orm/dist/query/QueryBuilder.js:83:99)

Describe the solution you'd like

The same auto-join support for update queries.

Describe alternatives you've considered

Going to try the hand-rolled joins next
@ogrodnek ogrodnek added the enhancement New feature or request label Jan 30, 2020
@B4nan
Copy link
Member

B4nan commented Jan 30, 2020

I will need to investigate a bit, the thing is that with update query you can't use aliases like this, also this is not natively supported in knex (which is used to create and run the query).

But it could be possible to do this via subquery...

@B4nan B4nan mentioned this issue Apr 30, 2020
Closed
46 tasks
@B4nan B4nan added this to the 4.0 milestone May 2, 2020
B4nan added a commit that referenced this issue May 2, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries
509aefa 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
@B4nan B4nan mentioned this issue May 2, 2020
Merged
B4nan added a commit that referenced this issue May 2, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries
577cc36 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
B4nan added a commit that referenced this issue May 2, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries
ad6427e 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
B4nan added a commit that referenced this issue May 2, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries (#537)
f3918a9 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
@B4nan
Copy link
Member

B4nan commented May 2, 2020

Closing as implemented via #537, which is now merged in v4 (dev branch). Subscribe here for updates #527.

@B4nan B4nan closed this as completed May 2, 2020
B4nan added a commit that referenced this issue May 3, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries (#537)
15a6878 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
B4nan added a commit that referenced this issue May 21, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries (#537)
56e3cc4 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
B4nan added a commit that referenced this issue Jun 1, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries (#537)
9a593fe 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
B4nan added a commit that referenced this issue Jun 5, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries (#537)
f060c24 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
B4nan added a commit that referenced this issue Jun 16, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries (#537)
175094a 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
B4nan added a commit that referenced this issue Aug 2, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries (#537)
d31d058 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
B4nan added a commit that referenced this issue Aug 9, 2020
@B4nan
feat(sql): allow update queries with auto-joining via sub-queries (#537)
cff9a3a 
```typescript
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ books: { author: 123 } });`
```

will result in following query:

```sql
update `publisher2` set `name` = ?, `type` = ? where `id` in (select `e0`.`id` from (
  select distinct `e0`.`id` from `publisher2` as `e0` left join `book2` as `e1` on `e0`.`id` = `e1`.`publisher_id` where `e1`.`author_id` = ?
) as `e0`)
```

Closes #319
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement New feature or request
Projects
None yet
Milestone
4.0
Development

No branches or pull requests
2 participants
@ogrodnek @B4nan