queryBuilder EXISTS query

Issue type:

[ ] question [ ] bug report [x] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [x] mysql / mariadb [ ] oracle [ ] postgres [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[x] latest [ ] @next [ ] 0.x.x (or put your version here)

Feature request:

Looks like EXISTS is optimised more, if you need to check only rows existence. So, instead of

SELECT COUNT(*) FROM mytable WHERE a = "foo" AND b = "bar";

better do

SELECT EXISTS(SELECT * FROM mytable WHERE a = "foo" AND b = "bar");

But now query builder doesn’t support such kind of queries, you need to write a raw query. I think it would be cool to have something like

queryBuilder()
  .select('*')
  .from(MyTable, 'tbl')
  .where('tbl.a = :foo AND tbl.b = :bar', { foo, bar })
  .isExists(); // <-- this row

Issue Analytics

State:
Created 5 years ago
Reactions:80
Comments:19 (3 by maintainers)

Top GitHub Comments

32reactions

panzicommented, Feb 22, 2021

I use this:

import { SelectQueryBuilder } from 'typeorm';

// TODO: remove this once it is provided by TypeORM (in case that ever happens)
declare module 'typeorm' {
    interface SelectQueryBuilder<Entity> {
        whereExists<T>(query: SelectQueryBuilder<T>): this;
        andWhereExists<T>(query: SelectQueryBuilder<T>): this;
        orWhereExists<T>(query: SelectQueryBuilder<T>): this;
    }
}

SelectQueryBuilder.prototype.whereExists = function (query: SelectQueryBuilder<any>): SelectQueryBuilder<any> {
    return this.where(`EXISTS (${query.getQuery()})`, query.getParameters());
};

SelectQueryBuilder.prototype.andWhereExists = function (query: SelectQueryBuilder<any>): SelectQueryBuilder<any> {
    return this.andWhere(`EXISTS (${query.getQuery()})`, query.getParameters());
};

SelectQueryBuilder.prototype.orWhereExists = function (query: SelectQueryBuilder<any>): SelectQueryBuilder<any> {
    return this.orWhere(`EXISTS (${query.getQuery()})`, query.getParameters());
};

23reactions

victordidenkocommented, Mar 20, 2019

I made little helper

export const isExistsQuery = (query: string) =>
  `SELECT EXISTS(${query}) AS "exists"`;

and use it like this:

const [{ exists }] = await manager.query(isExistsQuery(
  manager.createQueryBuilder()
    .select('*')
    .from(MyTable, 'tbl')
    .where('tbl.a = ? AND tbl.b = ?')
    .getQuery(),
), [foo, bar]);

But native support from TypeORM would be better, of course 😃

Top Results From Across the Web

Running "exists" queries in Laravel query builder

I'm using MySQL and have a table of 9 million rows and would like to quickly check if a record (id) exists or...

Java Examples & Tutorials of QueryBuilders.existsQuery (org ...

Best Java code snippets using org.elasticsearch.index.query.QueryBuilders.existsQuery (Showing top ... existsQuery(field)); filterQuery.must(QueryBuilders.

Query Builder - Laravel - The PHP Framework For Web Artisans

Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database ......

Exists query | Elasticsearch Guide [8.5] | Elastic

Exists query edit. Returns documents that contain an indexed value for a field. An indexed value may not exist for a document's field...

Laravel Query Builder Where Exists Example - Medium

The database query builder in Laravel provides a simple, intuitive interface for generating and performing database queries. It works with every one of ......