Wt

Did you ever figure this out?

Not Really,

But it somehow makes sense from the documentation.

Multiple conditions may be provided by successive calls to orWhere(), and are concatenated together using 'or'. Previous conditions will be surrounded by brackets and the new condition will be concatenated using 'or'. For example:

query.where("column_a = ?").bind("A")
  .where("column_b = ?").bind("B")
  .orWhere("column_c = ?").bind("C");

results in: "where ((column_a = 'A') and (column_b = 'B')) or column_c = 'C'"

https://www.webtoolkit.eu/wt/doc/reference/html/classWt_1_1Dbo_1_1Query.html#a693ed2ff14acaeffe45b8b6c09b6da8f

Why this only happens when the collection is empty, I did not research.

I just do a precheck to see if the collection is empty for a quick return for now

Hello Christian,

I believe this is indeed expected behavior. A collection is not fully like a result set, but rather a "wrapper" around a query. This means it is not stable, and its size, is dependent on when it's accessed (and thus on how the database has changed).

If you enable the logging of queries you will see this behavior.
A small example:

I have set up a List class, which contains multiple Item, mapped with a ManyToOne (and belongsTo respectively). I have greatly simplified here.

ListList

class List : public Wt::Dbo::Dbo<List>
{
public:
  List() = default;

  List(int id)        
    : listId_(id)
  {
  }

  int count() const
  {               
    auto query = listItems_.find()                   
                           .where("item_count != 0")
                           .orWhere("item_count != 0")
                           .resultList();

    int total = 0;
    for(const auto& item : query)
    {
      // expected 0 Items when collection empty, got several
      total += item->count();
    }

    return total;
  }

  template<class Action>
  void persist(Action& a)
  {
    Wt::Dbo::field(a, listId_, "listid");
    Wt::Dbo::hasMany(a, listItems_, Wt::Dbo::ManyToOne, "list"); 
  }

private:
  int listId_ = 0;
  Wt::Dbo::collection<Wt::Dbo::ptr<Item>> listItems_;
};

ItemItem

class Item : public Wt::Dbo::Dbo<Item>                                                                                           
{                                      
public:
  Item() = default;

  Item(int itemCount, const Wt::Dbo::ptr<List>& list)
    : itemCount_(itemCount),
      list_(list)
  {
  }

  int count() const
  {
    return itemCount_;
  }

  template<class Action>
  void persist(Action& a)
  {
    Wt::Dbo::field(a, itemCount_, "item_count");
    Wt::Dbo::belongsTo(a, list_, "list");
  }

private:
  int itemCount_ = 0;
  Wt::Dbo::ptr<List> list_;
};

When calling my equivalent of the above function, this does NOT work correctly, not even for non-empty sets. Say that A, B, and C are all conditions. It will return (A && B) || C).
In the case of a relation between two classes, like with List and Item, the collection is retrieved as SELECT * FROM item WHERE list_id = ?. So the relation itself is added to the query of the collection.

Take that together with the above point, and it will (WRONGLY!) make the query SELECT * FROM item WHERE (list_id = ? AND item_count != 0) OR item_count != 0. This essentially OMITS the foreign key constraint, and only focuses on the last clause (unless of course, both first clauses are satisfied at once).

So, the "solution" is to write a new complete query, or to retrieve the whole result set col_Items, and only then perform filtering on it, in the code, not in the database (mind that can be "heavy, depending on the table's size).

I hope that helps, and let me know if there is anything unclear.

Best,
Matthias