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

[daweto]

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.

[florianvandamme]

I have exactly the same issue. Any fix in the making?

[justvamp]

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: #8816 and #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?

[jimOnAir]

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

2. 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
                        }
                    }

[GM-Alex]

@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.

[eduardmarcinco]

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

[pixtron]

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'
        }
    })

[GM-Alex]

@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.

[m3c-ode]

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?

[cognot]

Hi,

facing the same issue with a ManyToMany relation. I managed to work around the problem using a SelectQueryBuilder, but still it would be nice if it worked with the entity repository approach.

Regards,
Richard.

[s1njar]

Hi,

Is there any news on this topic. This is a plausible use case to search for entities that do not have a specific relation!

Kind regards,
s1njar

[GM-Alex]

@s1njar The issue is closed and resolved.

[m3c-ode]

@s1njar The issue is closed and resolved.

@GM-Alex Where can we find the solution?

[GM-Alex]

@m3c-ode Just use the latest version of typeorm.

[growms]

On 0.3.16 i still have the problem with a OneToOne. @GM-Alex Do you talk about the 0.4 alpha ?

[GM-Alex]

@growms 0.3.x fixed it for me.

[MatheusLeitao]

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();
          }      
        },
    });

[diorgeneseugenio]

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 🫶

[dawidpstrak]

Using IsNull() instead of null working for me.

where: { id, replies: { parent: IsNull() } }