Where Col is Null

[blalan05]

Is your feature request related to a problem? Please describe.
I've got a table with parentId which is a self relationship, I would like to be able to select just the parents.

Describe the solution you'd like
with .find({ where: { parentId: null } }) or .find({ where: { $isNull: parentId } }) or similar

Describe alternatives you've considered
I tried with parentId: undefined... and that returns all rows. I've looked through source code and API has isNull options, but its not available to FindOptions or related types.

[noam-honig]

Hi, @blalan05,

This should work today - please make sure that the parentId has allowNull set to true - when it is, it shoud be:
where:{parentId:null}

Check it and let me know

[blalan05]

Version 0.26.12
Using JSON files for db

export class Customer {
  @Fields.cuid()
  id = ''

  @Fields.integer({ allowNull: true })
  appId = 0

  @Fields.string()
  firstName = ''

  @Fields.string()
  lastName = ''

  @Fields.date()
  customerAt?: Date

  @Fields.string({ allowNull: true })
  parentId?: string
  @Relations.toOne(() => Customer, 'parentId')
  parent?: Customer
}

[blalan05]

Wait... This is working as expected, my data was saving as empty string instead of null... is there a way to match empty string/ or falsey?

[noam-honig]

You can use the valueConverter to control how it's being loaded to and from the database:
https://remult.dev/docs/field-types#customize-db-value-conversions

Or you can use the saving hook to map that:
@fields.string({saving:(_,ref){
if (ref.value=='')
ref.value=null
} })

[blalan05]

Great! Is there something I can do to remove the type error when doing this?

[noam-honig]

Can you reproduce the typing error, I want to investigate it.

Try reproducing it here:
https://stackblitz.com/github/noam-honig/ready-to-play

[blalan05]

https://stackblitz.com/edit/github-bxxyjv?file=src%2Fshared%2Fmodel.ts,src%2FApp.tsx Error replicated.
Solution though title?: string | null, but I guess I expected to not have to add | null... probably my misunderstanding of TS.

[noam-honig]

To be honest, in these cases I do title:null! and that works just fine

[noam-honig]

Did this workout for you?

[blalan05]

I haven't seen null! before and was trying to learn what this is... I'm not sure where this snippet fits?

[noam-honig]

  useEffect(() => {
    taskRepo
      .find({
        limit: 20,
        orderBy: { createdAt: 'asc' },
        where: { title: null! }, //here
      })
      .then(setTasks)
  }, [])