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

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

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

@growms 0.3.x fixed it for me.

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?

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