[v5] whereNotExists strange behaviour/problem

[rbartholomay]

Hi!

i try to use whereNotExists in v5 like below. The first on via Database methods the second one via RAW-Statement

1:

    const data = await Database
      .from('wally_user_collector_idr_catalog_items')
      .whereNotExists(builder => {
        builder
          .from('collector_idr_catalog_items')
          .where(
            'collector_idr_catalog_items.item_id',
            'wally_user_collector_idr_catalog_items.collector_idr_catalog_item_item_id',
          )
      }).debug(true)

2.

    const data = await Database.rawQuery(
      'select * ' +
      'from `wally_user_collector_idr_catalog_items` ' +
      'where not exists ' +
      '  (' +
      '     select * from `collector_idr_catalog_items` ' +
      '     where `collector_idr_catalog_items`.`item_id` = wally_user_collector_idr_catalog_items.collector_idr_catalog_item_item_id' +
      ')'
    ).debug(true)

Both will return the statement:

sql: "select * from `wally_user_collector_idr_catalog_items` where not exists   (     select * from `collector_idr_catalog_items`      where `collector_idr_catalog_items`.`item_id` = wally_user_collector_idr_catalog_items.collector_idr_catalog_item_item_id)"

But only the second (raw) query will return the correct result, the first one will return all items from wally_user_collector_idr_catalog_items. Bug or did i miss something?

[thetutlage]

In the non-raw version the where statement treats the 2nd argument as a value and not a column reference. You need to write it as follows:

builder
    .from('collector_idr_catalog_items')
    .where(
        'collector_idr_catalog_items.item_id',
        Database.ref('wally_user_collector_idr_catalog_items.collector_idr_catalog_item_item_id'),
    )

[rbartholomay]

Nice! Thanks!