Hi Ivan, Thanks for giving us your option on this. However, I don’t quite share your point of view (Provided that I understood your concern correctly).
In the same way that we’re not selecting the same column twice (unless you explicitly want it) we also don’t – by default - add two or more constraints on the same column. In 99.9% of all statements you won’t have two contraints on the same column – especially not with an AND operator. And in 99.9% of all scenarios where you reuse a command and put a constraint on a column that already has a constraint on that column the user wants to replace the comparison value (e.g. in loops). Most constraints are equal-constraints anyway and two constraints on the same column with different values would always result in an empty result. Besides that I also don’t see the point that the other methods of DBCommand would behave differently. Select, OrderBy, GroupBy and Having behave in the same way: they will not add the same column twice. So I think everything is pretty consistent except for the issue that you reported with the addWhereConstraints method, when you add a list of constraints. I agree, that to be 100% consistent we would have to treat every item of the list as if it was added separately and hence allow only on constraint on each column. This would really be an issue to talk about. Regards, Rainer from: Ivan Nemeth [mailto:[email protected]] to: user re: Re: Constraint on the same column Hi Rainer, I know it's not a bug and this is my problem. Empire has a very good balance between pure SQL and Java and it's a very intuitive framework: you can think in SQL and code in Java at the same time. But this method is an exception. I would look DBCommand.where as an alternative to SQL's WHERE...AND construct, but this is wrong. Unlike other Empire methods (select(), orderBy()) this method doesn't do what I would expect: when you write a SQL you can put as many constraints on the same column with an AND operator, and the last one doesn't hide the previous ones. So in my opinion this method (in this form) slightly breaks Empire's logic. Regards, Ivan On Fri, Sep 11, 2015 at 2:15 PM, Rainer Döbele <[email protected]<mailto:[email protected]>> wrote: Hi Ivan, generally if you want two constraints on the same column you must combine them with an AND or OR operator. e.g. cmd.where(EMP.LASTNAME.like("%foo%").and(EMP.LASTNAME.isNot("fool"))); or: cmd.where(EMP.LASTNAME.like("%foo%").or(EMP.LASTNAME.is<http://EMP.LASTNAME.is>("master"))); The behavior to replace an existing constraint is by design. IMO the behavior when providing a list of constraints is also OK, although one can argue that the behavior should be the same. But your other issue when providing an empty list is certainly a bug. I will fix it immediately. Regards Rainer from: Ivan Nemeth [mailto:[email protected]<mailto:[email protected]>] to: user re: Constraint on the same column Hi, another issue with constraints. you can't add two constraint on the same column with DBCommand.where(DBCommandExpr expr) method. The documentation says "If another restriction already exists for the same column it will be replaced." So if I want to query persons who's name starts with "A" but is not "Adam", I can do the following: cmd.select(TABLE.getColumns()); cmd.where(TABLE.NAME.like("A%")); cmd.where(TABLE.NAME.isNot("Adam")); Due to the above mentioned restriction this won't work because the first constraint will be dropped away, and I'll get ALL persons except Adam. But if I use constraint list it's OK: cmd.select(TABLE.getColumns()); List wheres = new ArrayList(); wheres.add(TABLE.NAME.like("A%")); wheres.add(TABLE.NAME.isNot("Adam")); cmd.addWhereConstraints(wheres); Thanks, Ivan
