Hi Rainer, thanks for your answer, now I see this could be an endless debate between us. ;-) It's interesting what you say about command reusing. For me DBCommand is some kind of an "immutable", short-lived object: create - configure - use once - drop away. I've never used the same DBCommand more than once. This is a different approach than your's, and partly explains why we see this issue so different.
Thanks, Ivan (Sorry for my english, I know sometimes it is hard to understand what I want to say). On Tue, Sep 15, 2015 at 4:54 PM, Rainer Döbele <[email protected]> wrote: > 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]> 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("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]] > *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 > > > > > > > > >
