Hi Jörg, > what we‘re doing in our project sounds similar and might be helpful for > Henning’s request. SelectQuery provides asTable() so it can be used anywhere > or normal table can be used. [...]
Thanks for sharing these ideas. That is of course another valid way to tackle the problem discussed in this thread. Cheers Lukas 2012/11/16 Schönfisch, Jörg <[email protected]>: > Hi, > > > > what we‘re doing in our project sounds similar and might be helpful for > Henning’s request. SelectQuery provides asTable() so it can be used anywhere > or normal table can be used. To get the correct field out of the view we use > this method (with some sanity checks whether the field exists or not): > > private Field<?> getSelectField(SelectQuery query, String name) { > > List<Field<?>> fields = query.getFields(); > > int index = fields.indexOf(field); > > return query.getSelect().get(index); > > } > > So something like this can be done: > > Factory create = … > > SelectQuery minorView = … > > SelectQuery queryName = > create.selectQuery(minorView.asTable()).where(getSelectField(minorView, > “name”).eq(“smith”)); > > SelectQuery queryAge = > create.selectQuery(minorView.asTable()).where(getSelectField(minorView, > “age”).greaterThan(16)); > > > > getSelectField was used because otherwise the aliasing of the field somehow > got screwed up. AFAIR it used the tableAlias inside the SelectQuery instead > of the alias of the select itself, which results in errors if you use the > view in joins or where clauses. > > Maybe wrap the query for the view and the getSelectField calls in their own > class with getTable(), getMinorName, etc and it should be very close to what > you want. > > > > I hope this approach is somehow useful or did I totally misunderstand the > question? > > > > Cheers, > > Jörg > > > > > > > > > > Von: [email protected] [mailto:[email protected]] Im > Auftrag von Henning Blohm > Gesendet: Freitag, 16. November 2012 14:03 > An: [email protected] > Betreff: Re: Views and aliasing in jOOQ > > > > Hi Lukas, > > thanks for that extremely quick and detailed reply! > > Henning > > Am Freitag, 16. November 2012 13:34:51 UTC+1 schrieb Lukas Eder: > > Hello Henning, > > Unfortunately, this isn't possible out of the box, right now. It > sounds like a very nice idea, though. I have registered feature > request #1969 for this. > https://github.com/jOOQ/jOOQ/issues/1969 > > Of course, you can always write actual database views and let the code > generator generate meta-data for those. > > Also, you could manually implement some org.jooq.Table objects, but > that might be quite a bit of work. There are CustomTable types, which > unfortunately aren't as powerful as you would like to see them: > http://www.jooq.org/doc/2.6/manual/sql-building/queryparts/custom-queryparts/ > > This is due to the methods toSQL() and bind() being final in > CustomTable's class hierarchy, which is probably a design flaw. It > should be fixed in jOOQ 3.0 as of #1970: > https://github.com/jOOQ/jOOQ/issues/1970 > > Cheers > Lukas > > 2012/11/16 Henning Blohm <[email protected]>: >> Hi, >> >> I am trying to achieve the construction of a (logical) view using jOOQ. As >> a >> simple case, assume there is a table students with name and age and I want >> to turn this into >> >> minors = select s.name as minor_name from students s where s.age<21; >> >> Some other piece of code can then filter those further as in >> >> select m.minor_name from minors where m.minor_name='smith'; >> >> In Java terms, "minors" would be a typed "queryable" with a custom POJO >> type >> (with field "minorName"). >> >> Real world examples would be more complex of course >> >> I spent some time looking for a suitable construct in jOOQ but was not >> succesful. >> >> Any helpful directions would be great! >> >> Thanks, >> Henning
