typeorm: Unable to query data where relationship needs to be null using Repository method

Issue Description

When I’m trying to query a model, where I need the relationship to be NULL, the SQL query doesn’t add the WHERE IS NULL.

Expected Behavior

const box = await AppDataSource.getRepository(BoxModel).find({
      relations: {
        pallet: true,
      },
      where: {
        pallet: IsNull(),
      },
    });
SELECT * FROM "boxes" "BoxModel" LEFT JOIN "pallets" "BoxModelBoxModel_pallet" ON "BoxModelBoxModel_pallet"."id"="BoxModel"."pallet_id" AND ("BoxModel_BoxModel_pallet"."deleted_at" IS NULL) WHERE "BoxModel"."deleted_at" IS NULL AND  "BoxModel"."pallet_id" IS NULL;

Actual Behavior

SELECT * FROM "boxes" "BoxModel"   LEFT JOIN "pallets" "BoxModelBoxModel_pallet" ON BoxModelBoxModel_pallet"."id"="BoxModel"."pallet_id" AND ("BoxModel_BoxModel_pallet"."deleted_at" IS NULL) WHERE "BoxModel"."deleted_at" IS NULL;

Steps to Reproduce

  1. Create a model with a nullable ManyToOne relationship.
  2. Try to query from the children model, where the parent relationship is NULL.
@Entity({ name: 'pallets' })
export class PalletModel extends CustomBaseEntity {
  @OneToMany(() => BoxModel, (box) => box.pallet)
  boxes!: BoxModel[];
}

@Entity({ name: 'boxes' })
export class BoxModel extends CustomBaseEntity {
  @ManyToOne(() => PalletModel, (pallet) => pallet.boxes, { nullable: true })
  @JoinColumn({ name: 'pallet_id' })
  pallet?: PalletModel | null;
}

My Environment

Dependency Version
Operating System MacOS 12.1
Node.js version 16.13.2
Typescript version 4.5.2
TypeORM version 0.3.5

Additional Context

Relevant Database Driver(s)

DB Type Reproducible
aurora-mysql yes
aurora-postgres yes
better-sqlite3 yes
cockroachdb yes
cordova yes
expo yes
mongodb yes
mysql yes
nativescript yes
oracle yes
postgres yes
react-native yes
sap yes
spanner yes
sqlite yes
sqlite-abstract yes
sqljs yes
sqlserver yes

Are you willing to resolve this issue by submitting a Pull Request?

  • ✖️ Yes, I have the time, and I know how to start.
  • ✅ Yes, I have the time, but I don’t know how to start. I would need guidance.
  • ✖️ No, I don’t have the time, but I can support (using donations) development.
  • ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Reactions: 5
  • Comments: 19 (2 by maintainers)

Commits related to this issue

Most upvoted comments

I’ve solved it with the reverse column IsNull() instead of it’s object.


const box = await AppDataSource.getRepository(BoxModel).find({
      relations: {
        pallet: true,
      },
      where: {
        pallet: {
           box_id: IsNull();
          }      
        },
    });

@growms 0.3.x fixed it for me.

A workaround for this issue that does not require any fix is to specify a JoinColumn on the ManyToOne side and then query the JoinColumn directly.

@Entity()
export class Author {
    @PrimaryColumn()
    id: number

    @OneToMany(() => Post, (post) => post.author)
    posts: Post[]
}

@Entity()
export class Post {
    @PrimaryGeneratedColumn()
    id: number

    @ManyToOne(() => Author, (author) => author.posts, {
        nullable: true,
    })
    @JoinColumn({name: 'author_id'})
    author: Author | null

    @Column({
        nullable: true,
        select: false,
    })
    author_id: number | null
}

const posts = await dataSource.getRepository(Post)
    .find({
        relations: ['author'],
        where: [
            {
                author_id: In([2, 3]),
            },
            {
                author_id: IsNull(),
            },
        ],
        order: {
            id: 'ASC'
        }
    })

Hi there,

I may say that I’m facing an issue that is really close to this one, but mine still doesn’t work, after having upgraded the package’s version. In a OnetoOne relationship, between 2 repositories: Address and Warehouse, I am trying to fetch all the addresses that do not have a warehouse relation. I have been trying to use Repository manager instead of the QueryBuilder.

These are my models:

@Entity()
export class Address {
	[key: string]: string | string[] | number | Record<string, unknown> | Date;
	@PrimaryGeneratedColumn({
		type: "bigint",
		name: "address_id",
	})
	id: string;

	@Column({
		nullable: true,
	})
	customerId?: string;

	@Field()
	@OneToOne(() => Warehouse, (warehouse: Warehouse) => warehouse.address, { nullable: true })
	warehouse?: string | null;
}

@Entity()
export class Warehouse {
	@PrimaryGeneratedColumn({
		type: "bigint",
	})
	id: string;

	@Field()
	@Column()
	name: string;

	@Field()
	@Column({ nullable: false })
	merchantId: string;

	@OneToOne(() => Address, { eager: true, cascade: true })
	@JoinColumn()
	address: Address;
}

Trying this code:

    findAllAddresses(@Query() query: any) {
        return this.addressesService.find({
            relations: {
                warehouse: true
            },
            where: {
                warehouse: null
            }
        });
    }

Returns all of my results. Even the ones with the warehouse: null field.

I found and tried with

            where: {
                warehouse: IsNull()
            }

But my query actually returns an error (not in TS):

This relation isn't supported by given find operator

How can I filter on this field/relation?

@pleerock is it possible to prioritize this issue? .find() is broken for a long time and prevent us using typeorm.

@pleerock Any estimated for this? It’s somehow a critical one and a fix is available by @pixtron. I already tested his PR and works as expected.

Seems like the problem is in buildWhere method of SelectQueryBuilder<Entity> class:

  1. Starting from line number 3966 we are skipping all FindOptionsWhere like { someRelation: isNull()}
        } else {
            let andConditions: string[] = []
            for (let key in where) {
                if (where[key] === undefined || where[key] === null) continue
  1. And next starting line number 4053 we are skipping all options like { someRelation: {id: isNulll()}}
                } else if (relation) {
                    // if all properties of where are undefined we don't need to join anything
                    // this can happen when user defines map with conditional queries inside
                    if (typeof where[key] === "object") {
                        const allAllUndefined = Object.keys(where[key]).every(
                            (k) => where[key][k] === undefined,
                        )
                        if (allAllUndefined) {
                            continue
                        }
                    }

Same here. Version 0.3.6.

@pleerock This is one of many issues of this kind opened here for the last couple of weeks. At least two others: https://github.com/typeorm/typeorm/issues/8816 and https://github.com/typeorm/typeorm/issues/8945.

The problem is really serious. Looks like typeorm 0.3 is not production-ready yet.

Are there any problems with the reproduce case or something? Do you need any assistance to fix this asap?

I’ve solved it with the reverse column IsNull() instead of it’s object.

const box = await AppDataSource.getRepository(BoxModel).find({
      relations: {
        pallet: true,
      },
      where: {
        pallet: {
           box_id: IsNull();
          }      
        },
    });

Love you man 🫶

@pleerock Is there any chance to get this fixed soon? I know it’s vacation time but I, and I think many others, would appreciate it.