Hello, Thanks for clarifying. I understand now. That's an interesting case.
When you denormalise your tables using a JOIN, the resulting table type is indeed unknown to the Java compiler, unless you explicitly specify the select list. Even then, it would still not formally be of type MerchantRecord. One way to map a Result<?> to a Result<MerchantRecord> is by calling into(Tables.MERCHANT): http://www.jooq.org/javadoc/latest/org/jooq/Result.html#into-org.jooq.Table- return using(configuration()) .select() .from(Tables.MERCHANT) .join(Tables.MERCHANT_GROUP_MAPPING) .onKey() .where(Tables.MERCHANT_GROUP_MAPPING.GROUP_ID.eq(groupId)) .fetch() .into(Tables.MERCHANT) .map(mapper()); This will keep only those columns that are effectively selected from the MERCHANT table and produce a MerchantRecord for the mapper. Another option would be to use a semi-join instead of a join: return using(configuration()) .selectFrom(Tables.MERCHANT) .where(Tables.MERCHANT.MERCHANT_ID.in( select(Tables.MERCHANT_GROUP_MAPPING.MERCHANT_ID) .from(Tables.MERCHANT_GROUP_MAPPING) .where(Tables.MERCHANT_GROUP_MAPPING.GROUP_ID.eq(groupId))) )) .fetch() .map(mapper()); In some databases, using joins or semi-joins can be the same thing to the optimiser, depending on your constraints and indexes. To me it seems like when you use selectFrom(? extends Table) its able to > hint to the sql builder the type of the Record. When you do just a > select() it only knows it will be a Record but not what type. Am I missing > an alternate way to do this? Yes, the difference is explained here: http://www.jooq.org/doc/latest/manual/sql-execution/fetching/record-vs-tablerecord I hope this helps, Lukas 2015-01-23 7:13 GMT+01:00 Bill O'Neil <[email protected]>: > Sorry about the first example I just wrote it off the top of my head. > Here is an actual example I attempted. > > This is a method inside of a class that extends the auto generated > MerchantDao. The mapper() is for a Merchant Pojo also auto generated. > > public class MerchantDaoExt extends MerchantDao { > > ... > > public List<Merchant> fetchMerchantsByGroupId(Long groupId) { > return using(configuration()) > .select() > .from(Tables.MERCHANT) > .join(Tables.MERCHANT_GROUP_MAPPING) > .onKey() > .where(Tables.MERCHANT_GROUP_MAPPING.GROUP_ID.eq(groupId)) > .fetch() > .map(mapper()); > } > > The compilation error I get is. > > The method map(RecordMapper<? super Record,E>) in the type Result<Record> > is not applicable for the arguments (RecordMapper<MerchantRecord,Merchant>) > To me it seems like when you use selectFrom(? extends Table) its able to > hint to the sql builder the type of the Record. When you do just a > select() it only knows it will be a Record but not what type. Am I missing > an alternate way to do this? > > Thanks > > On Thursday, January 22, 2015 at 12:33:45 PM UTC-5, Lukas Eder wrote: >> >> Hello, >> >> >> 2015-01-22 5:01 GMT+01:00 Bill O'Neil <[email protected]>: >> >>> 1. Is it possible to reuse auto generated RecordMappers from the dao >>> objects? I have been trying to find a way to do something like. >>> >>> List<User> users = using(configuration()) >>> .selectFrom(Tables.USER) >>> .from(Tables.USER) >>> .join(Tables.ROLES) >>> .onKey() >>> .where(Tables.ROLES.ROLE.eq("admin")) >>> .fetch() >>> .map(mapper()); >>> >>> This doesn't quite work is there a way to do it? Essentially a simple >>> join and only return a pojo from one of the tables. >>> >> >> The DAO.mapper() method is public. Why doesn't calling it work for you? >> Do note, it doesn't do anything magic, out-of-the-box. It simply calls >> through to the DefaultRecordMapper, caching the instance in the DAO >> instance. >> >> The one thing that is obviously not correct is your repeating the "FROM >> USER" clause twice. You should write >> >> using(configuration()) >> .select() >> .from(Tables.USER) >> .join(Tables.ROLES) >> .onKey() >> >> 2. Is there a way to return multiple mapped pojos from a query. Possibly >>> Combining JOOQ and JOOL or something. >>> >>> List<Tuple2<User, Role>> users = using(configuration()) >>> .selectFrom(Tables.USER) >>> .from(Tables.USER) >>> .join(Tables.ROLES) >>> .onKey() >>> .fetch() >>> .map(user.mapper(), role.mapper()); >>> >>> I have been using slick in scala which allows you to tuple a bunch of >>> case classes into a single result. I was curious if you can do something >>> similar in JOOQ easily. >>> >> >> That sounds very interesting indeed! We haven't thought about this yet. >> Do note, though, that this only works if your USER and ROLES tables do >> not share the same column names, though. In case they don't, you can do >> something like that: >> >> List<Tuple2<User, Role>> users = using(configuration()) >> .select() >> .from(Tables.USER) >> .join(Tables.ROLES) >> .onKey() >> .fetch() >> .map(r -> tuple(user.mapper().map(r), role.mapper().map(r))); >> >> We'll keep this use-case in mind when implementing jOOQ 3.6, where we'll >> tackle support for nested records both on a SQL and on a mapping level. >> > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
