Record Wrappers

[Dan]

It would be great if it were easy to have a Record implementation that delegated to another, especially for the generated record classes.

Example use case 1: I have a record that maps to a few rows joined together from 2-3 tables. I can set fields on it using the (verbose) non-typesafe methods, but it would be great to use generated setFoo(), setBar(), etc methods. I can't go record.into(FirstTable.class).setFoo() because that creates a copy - it would be great if I could easily go FirstTable.wrap(record).setFoo(x) and have that setFoo() method delegate to the underlying wrapped record, SecondTable.wrap(record).setBar(y) affects the same record, and so on.

Example use case 2: Disambiguate fields. If I go dsl.select()... with some joins, and the table field names clash, jooq / sql will not complain because when generating the SQL jooq disambiguates the field names with tables. However in the returned records, it seems that table names are ignored and whatever fields came last clobber earlier fields (usually one would prefer the other way round, but that's not 100% of the time either). Being able to get a specific "view" of the record would again be very helpful, e.g.  FooTable.wrap(record).getId() vs BarTable.wrap(record).getId(). Of course that doesn't disambiguate the case of table aliasing but that should be easily handled with modest extensions to this concept.  I think this would be more convenient than being forced to explicitly list all the fields to select() and aliasing the clashing ones. 

How does this sound? Or is there a better way to achieve 1/2?

Dan

[Lukas Eder]

2013/9/2 Dan <daniel.d...@gmail.com>

It would be great if it were easy to have a Record implementation that delegated to another, especially for the generated record classes.

Example use case 1: I have a record that maps to a few rows joined together from 2-3 tables. I can set fields on it using the (verbose) non-typesafe methods, but it would be great to use generated setFoo(), setBar(), etc methods. I can't go record.into(FirstTable.class).setFoo() because that creates a copy - it would be great if I could easily go FirstTable.wrap(record).setFoo(x) and have that setFoo() method delegate to the underlying wrapped record, SecondTable.wrap(record).setBar(y) affects the same record, and so on.

Yes, these kinds of one-to-one relations would often be useful if known to jOOQ. There had been quite a few discussions around this subject in the recent past. Currently, jOOQ does not add such cleverness to records. However, if your join product is guaranteed to be "updatable", why not just create a database view and make these things transparent to jOOQ?

 

Example use case 2: Disambiguate fields. If I go dsl.select()... with some joins, and the table field names clash, jooq / sql will not complain because when generating the SQL jooq disambiguates the field names with tables. However in the returned records, it seems that table names are ignored and whatever fields came last clobber earlier fields (usually one would prefer the other way round, but that's not 100% of the time either).

That shouldn't be the case. If you have an exact match of table name / field name, you should get the right value, even if field names clash. Can you provide a test case to reproduce the issue?

 

Being able to get a specific "view" of the record would again be very helpful, e.g.  FooTable.wrap(record).getId() vs BarTable.wrap(record).getId(). Of course that doesn't disambiguate the case of table aliasing but that should be easily handled with modest extensions to this concept.  I think this would be more convenient than being forced to explicitly list all the fields to select() and aliasing the clashing ones.

There's http://www.jooq.org/javadoc/latest/org/jooq/Record.html#into(org.jooq.Table), instead of Table.wrap(Record). Does this help?

Cheers

Lukas

[Daniel Danilatos]

Thanks Lukas

On Tue, Sep 3, 2013 at 1:39 AM, Lukas Eder <lukas...@gmail.com> wrote:

2013/9/2 Dan <daniel.d...@gmail.com>

It would be great if it were easy to have a Record implementation that delegated to another, especially for the generated record classes.

Example use case 1: I have a record that maps to a few rows joined together from 2-3 tables. I can set fields on it using the (verbose) non-typesafe methods, but it would be great to use generated setFoo(), setBar(), etc methods. I can't go record.into(FirstTable.class).setFoo() because that creates a copy - it would be great if I could easily go FirstTable.wrap(record).setFoo(x) and have that setFoo() method delegate to the underlying wrapped record, SecondTable.wrap(record).setBar(y) affects the same record, and so on.

Yes, these kinds of one-to-one relations would often be useful if known to jOOQ. There had been quite a few discussions around this subject in the recent past. Currently, jOOQ does not add such cleverness to records. However, if your join product is guaranteed to be "updatable", why not just create a database view and make these things transparent to jOOQ?

I remember looking into creating a view and realising mysql couldn't handle insert/update/delete fully properly. Currently I am porting code from a PHP ORM into JOOQ so looking to get things up and running - doesn't necessarily have to be done in the same fashion. In the old code base, one could represent "inheritance" by having table B join to table A in a 1:1 relationship (they shared primary keys) and that would be abstracted away (very similar to a view).

I guess a related question is this: Is there a simple way to implement the Record interface? E.g. where one could override key methods such as getValue(field) and have the rest work correctly. (The full interface is huge) If I could even just construct a record with a list of Fields that might be enough for my purposes, but any constructors that work that way are all package private on on package private classes.

 

Example use case 2: Disambiguate fields. If I go dsl.select()... with some joins, and the table field names clash, jooq / sql will not complain because when generating the SQL jooq disambiguates the field names with tables. However in the returned records, it seems that table names are ignored and whatever fields came last clobber earlier fields (usually one would prefer the other way round, but that's not 100% of the time either).

That shouldn't be the case. If you have an exact match of table name / field name, you should get the right value, even if field names clash. Can you provide a test case to reproduce the issue?

I'll try to reproduce this again and make sure I wasn't doing something wrong. This observation is unrelated to what I'm trying to do above.

 

 

Being able to get a specific "view" of the record would again be very helpful, e.g.  FooTable.wrap(record).getId() vs BarTable.wrap(record).getId(). Of course that doesn't disambiguate the case of table aliasing but that should be easily handled with modest extensions to this concept.  I think this would be more convenient than being forced to explicitly list all the fields to select() and aliasing the clashing ones.

There's http://www.jooq.org/javadoc/latest/org/jooq/Record.html#into(org.jooq.Table), instead of Table.wrap(Record). Does this help?

As far as I can tell that creates copies of the source record, not views.

 

Cheers

Lukas

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Lukas Eder]

Hi Daniel,

You have some interesting ideas, there...

2013/9/3 Daniel Danilatos <dan...@danilatos.com>

Thanks Lukas

On Tue, Sep 3, 2013 at 1:39 AM, Lukas Eder <lukas...@gmail.com> wrote:

2013/9/2 Dan <daniel.d...@gmail.com>

It would be great if it were easy to have a Record implementation that delegated to another, especially for the generated record classes.

Example use case 1: I have a record that maps to a few rows joined together from 2-3 tables. I can set fields on it using the (verbose) non-typesafe methods, but it would be great to use generated setFoo(), setBar(), etc methods. I can't go record.into(FirstTable.class).setFoo() because that creates a copy - it would be great if I could easily go FirstTable.wrap(record).setFoo(x) and have that setFoo() method delegate to the underlying wrapped record, SecondTable.wrap(record).setBar(y) affects the same record, and so on.

Yes, these kinds of one-to-one relations would often be useful if known to jOOQ. There had been quite a few discussions around this subject in the recent past. Currently, jOOQ does not add such cleverness to records. However, if your join product is guaranteed to be "updatable", why not just create a database view and make these things transparent to jOOQ?

I remember looking into creating a view and realising mysql couldn't handle insert/update/delete fully properly.

True, MySQL has some harder restrictions there, compared to Oracle, for instance.

 

Currently I am porting code from a PHP ORM into JOOQ so looking to get things up and running - doesn't necessarily have to be done in the same fashion. In the old code base, one could represent "inheritance" by having table B join to table A in a 1:1 relationship (they shared primary keys) and that would be abstracted away (very similar to a view).

Interesting. What ORM is this? Maybe I can study how they implemented this inheritance notion. There are generally three ways of implementing inheritance in relational models. One has to be careful not to map such a model too strictly to a Java domain, as the two domains will remain independent.

Nonetheless, I think that supporting "inheritance" modelled as a set of 1:1 relationships with some sort of discriminator might be a good thing to implement in jOOQ. Obviously, such an implementation should be somewhat compatible with JPA, to follow the principle of least astonishment.

I guess a related question is this: Is there a simple way to implement the Record interface? E.g. where one could override key methods such as getValue(field) and have the rest work correctly. (The full interface is huge) If I could even just construct a record with a list of Fields that might be enough for my purposes, but any constructors that work that way are all package private on on package private classes.

Currently, no, there is no simple way to do this. I can see two possible method additions, though:

- Record DSLContext.newRecord(Field<?>...)

- <T1> Record1<T1> DSLContext.newRecord(Row1<T1>)

- <T1, T2> Record2<T1, T2> DSLContext.newRecord(Row2<T1, T2>)

- <T1, T2, ..., T[N]> Record2<T1, T2, ..., T[N]> DSLContext.newRecord(Row[N]<T1, T2, ..., T[N]>)

I will register a feature request for this change #2722:

https://github.com/jOOQ/jOOQ/issues/2722

Obviously, this would be yet another way to copy record data into a new record type. Maybe, some more useful API could be added to jOOQ to cover other parts of your use-case?

Note that another, related feature is already on the roadmap: https://github.com/jOOQ/jOOQ/issues/1838

Example use case 2: Disambiguate fields. If I go dsl.select()... with some joins, and the table field names clash, jooq / sql will not complain because when generating the SQL jooq disambiguates the field names with tables. However in the returned records, it seems that table names are ignored and whatever fields came last clobber earlier fields (usually one would prefer the other way round, but that's not 100% of the time either).

That shouldn't be the case. If you have an exact match of table name / field name, you should get the right value, even if field names clash. Can you provide a test case to reproduce the issue?

I'll try to reproduce this again and make sure I wasn't doing something wrong. This observation is unrelated to what I'm trying to do above.

OK, thanks

Being able to get a specific "view" of the record would again be very helpful, e.g.  FooTable.wrap(record).getId() vs BarTable.wrap(record).getId(). Of course that doesn't disambiguate the case of table aliasing but that should be easily handled with modest extensions to this concept.  I think this would be more convenient than being forced to explicitly list all the fields to select() and aliasing the clashing ones.

There's http://www.jooq.org/javadoc/latest/org/jooq/Record.html#into(org.jooq.Table), instead of Table.wrap(Record). Does this help?

As far as I can tell that creates copies of the source record, not views.

True. Currently, there is no easy way to create views of records, short of implementing Record. I had recently thought about this myself in the context of Result.intoGroups(). This method performs grouping of a Result by several group fields. The group key is a Record "copy", not a Record "view". Making this a view, however, would be quite complicated.

Anyway, most of jOOQ's API operates on copies, where the semantics of subsequent CRUD operations may be a bit surprising / annoying. E.g. if copying a record, a subsequent store() performs an INSERT in jOOQ, not an UPDATE. With views, the operations would remain the same.

I'll have to give this some more thought. I have registered #2721 for this:

https://github.com/jOOQ/jOOQ/issues/2721

Cheers

Lukas

[Daniel Danilatos]

Sorry it took me a while to reply...

On Tue, Sep 3, 2013 at 5:39 PM, Lukas Eder <lukas...@gmail.com> wrote:

Hi Daniel,

You have some interesting ideas, there...

2013/9/3 Daniel Danilatos <dan...@danilatos.com>

Thanks Lukas

On Tue, Sep 3, 2013 at 1:39 AM, Lukas Eder <lukas...@gmail.com> wrote:

2013/9/2 Dan <daniel.d...@gmail.com>

It would be great if it were easy to have a Record implementation that delegated to another, especially for the generated record classes.

Example use case 1: I have a record that maps to a few rows joined together from 2-3 tables. I can set fields on it using the (verbose) non-typesafe methods, but it would be great to use generated setFoo(), setBar(), etc methods. I can't go record.into(FirstTable.class).setFoo() because that creates a copy - it would be great if I could easily go FirstTable.wrap(record).setFoo(x) and have that setFoo() method delegate to the underlying wrapped record, SecondTable.wrap(record).setBar(y) affects the same record, and so on.

Yes, these kinds of one-to-one relations would often be useful if known to jOOQ. There had been quite a few discussions around this subject in the recent past. Currently, jOOQ does not add such cleverness to records. However, if your join product is guaranteed to be "updatable", why not just create a database view and make these things transparent to jOOQ?

I remember looking into creating a view and realising mysql couldn't handle insert/update/delete fully properly.

True, MySQL has some harder restrictions there, compared to Oracle, for instance.

 

Currently I am porting code from a PHP ORM into JOOQ so looking to get things up and running - doesn't necessarily have to be done in the same fashion. In the old code base, one could represent "inheritance" by having table B join to table A in a 1:1 relationship (they shared primary keys) and that would be abstracted away (very similar to a view).

Interesting. What ORM is this? Maybe I can study how they implemented this inheritance notion. There are generally three ways of implementing inheritance in relational models. One has to be careful not to map such a model too strictly to a Java domain, as the two domains will remain independent.

An ORM I wrote ages ago before ORM was a TLA :)  You probably don't want to study what I did.

 

Nonetheless, I think that supporting "inheritance" modelled as a set of 1:1 relationships with some sort of discriminator might be a good thing to implement in jOOQ. Obviously, such an implementation should be somewhat compatible with JPA, to follow the principle of least astonishment.

If jOOQ provides the right set of primitives, there should be no need to bake in this feature, as it would be easily implementable on top.  I know I'm oversimplifying, and I don't have all the hundreds of use cases in mind that you need to juggle, but as a simple example:  say the generated record classes did not fit into a large inheritance hierarchy, but instead subclassed a very simple delegating base class (merely to take care of most of the boilerplate) and then were implemented by receiving an inner record in their constructor. The actual code would be very similar - the setFoo and getBar methods would still delegate to setValue & getValue, except on an inner object, instead of their parent class. Except now I can provide any other backing record of my choosing in the constructor, and reuse all this conveniently generated code. For example, I can pass in a record that is the result of a join query, to get a certain view of it, read values with nice getters, make changes with nice setters, etc.

 

I guess a related question is this: Is there a simple way to implement the Record interface? E.g. where one could override key methods such as getValue(field) and have the rest work correctly. (The full interface is huge) If I could even just construct a record with a list of Fields that might be enough for my purposes, but any constructors that work that way are all package private on on package private classes.

Currently, no, there is no simple way to do this. I can see two possible method additions, though:

- Record DSLContext.newRecord(Field<?>...)

- <T1> Record1<T1> DSLContext.newRecord(Row1<T1>)

- <T1, T2> Record2<T1, T2> DSLContext.newRecord(Row2<T1, T2>)

- <T1, T2, ..., T[N]> Record2<T1, T2, ..., T[N]> DSLContext.newRecord(Row[N]<T1, T2, ..., T[N]>)

I will register a feature request for this change #2722:

https://github.com/jOOQ/jOOQ/issues/2722

Obviously, this would be yet another way to copy record data into a new record type. Maybe, some more useful API could be added to jOOQ to cover other parts of your use-case?

Note that another, related feature is already on the roadmap: https://github.com/jOOQ/jOOQ/issues/1838

Example use case 2: Disambiguate fields. If I go dsl.select()... with some joins, and the table field names clash, jooq / sql will not complain because when generating the SQL jooq disambiguates the field names with tables. However in the returned records, it seems that table names are ignored and whatever fields came last clobber earlier fields (usually one would prefer the other way round, but that's not 100% of the time either).

That shouldn't be the case. If you have an exact match of table name / field name, you should get the right value, even if field names clash. Can you provide a test case to reproduce the issue?

I'll try to reproduce this again and make sure I wasn't doing something wrong. This observation is unrelated to what I'm trying to do above.

OK, thanks

Being able to get a specific "view" of the record would again be very helpful, e.g.  FooTable.wrap(record).getId() vs BarTable.wrap(record).getId(). Of course that doesn't disambiguate the case of table aliasing but that should be easily handled with modest extensions to this concept.  I think this would be more convenient than being forced to explicitly list all the fields to select() and aliasing the clashing ones.

There's http://www.jooq.org/javadoc/latest/org/jooq/Record.html#into(org.jooq.Table), instead of Table.wrap(Record). Does this help?

As far as I can tell that creates copies of the source record, not views.

True. Currently, there is no easy way to create views of records, short of implementing Record. I had recently thought about this myself in the context of Result.intoGroups(). This method performs grouping of a Result by several group fields. The group key is a Record "copy", not a Record "view". Making this a view, however, would be quite complicated.

Anyway, most of jOOQ's API operates on copies, where the semantics of subsequent CRUD operations may be a bit surprising / annoying. E.g. if copying a record, a subsequent store() performs an INSERT in jOOQ, not an UPDATE. With views, the operations would remain the same.

Heh, actually, this caused a moment of confusion the first time I did this, when I thought I was going to get an update but instead got an insert because of the copy. I think either way is fine though, as long as the documentation is clear. 

 

I'll have to give this some more thought. I have registered #2721 for this:

https://github.com/jOOQ/jOOQ/issues/2721

Cheers

Lukas

--

[Lukas Eder]

2013/9/20 Daniel Danilatos <dan...@danilatos.com>

Sorry it took me a while to reply...

No worries, it happens :-)

 

On Tue, Sep 3, 2013 at 5:39 PM, Lukas Eder <lukas...@gmail.com> wrote:

Hi Daniel,

You have some interesting ideas, there...

2013/9/3 Daniel Danilatos <dan...@danilatos.com>

Thanks Lukas

On Tue, Sep 3, 2013 at 1:39 AM, Lukas Eder <lukas...@gmail.com> wrote:

2013/9/2 Dan <daniel.d...@gmail.com>

It would be great if it were easy to have a Record implementation that delegated to another, especially for the generated record classes.

Example use case 1: I have a record that maps to a few rows joined together from 2-3 tables. I can set fields on it using the (verbose) non-typesafe methods, but it would be great to use generated setFoo(), setBar(), etc methods. I can't go record.into(FirstTable.class).setFoo() because that creates a copy - it would be great if I could easily go FirstTable.wrap(record).setFoo(x) and have that setFoo() method delegate to the underlying wrapped record, SecondTable.wrap(record).setBar(y) affects the same record, and so on.

Yes, these kinds of one-to-one relations would often be useful if known to jOOQ. There had been quite a few discussions around this subject in the recent past. Currently, jOOQ does not add such cleverness to records. However, if your join product is guaranteed to be "updatable", why not just create a database view and make these things transparent to jOOQ?

I remember looking into creating a view and realising mysql couldn't handle insert/update/delete fully properly.

True, MySQL has some harder restrictions there, compared to Oracle, for instance.

 

Currently I am porting code from a PHP ORM into JOOQ so looking to get things up and running - doesn't necessarily have to be done in the same fashion. In the old code base, one could represent "inheritance" by having table B join to table A in a 1:1 relationship (they shared primary keys) and that would be abstracted away (very similar to a view).

Interesting. What ORM is this? Maybe I can study how they implemented this inheritance notion. There are generally three ways of implementing inheritance in relational models. One has to be careful not to map such a model too strictly to a Java domain, as the two domains will remain independent.

An ORM I wrote ages ago before ORM was a TLA :)  You probably don't want to study what I did.

 

Nonetheless, I think that supporting "inheritance" modelled as a set of 1:1 relationships with some sort of discriminator might be a good thing to implement in jOOQ. Obviously, such an implementation should be somewhat compatible with JPA, to follow the principle of least astonishment.

If jOOQ provides the right set of primitives, there should be no need to bake in this feature, as it would be easily implementable on top.  I know I'm oversimplifying, and I don't have all the hundreds of use cases in mind that you need to juggle, but as a simple example:  say the generated record classes did not fit into a large inheritance hierarchy, but instead subclassed a very simple delegating base class (merely to take care of most of the boilerplate) and then were implemented by receiving an inner record in their constructor. The actual code would be very similar - the setFoo and getBar methods would still delegate to setValue & getValue, except on an inner object, instead of their parent class. Except now I can provide any other backing record of my choosing in the constructor, and reuse all this conveniently generated code. For example, I can pass in a record that is the result of a join query, to get a certain view of it, read values with nice getters, make changes with nice setters, etc.

Aha, I see what you mean. Interesting. Let me challenge that :-)

jOOQ doesn't rely on any setters or getters, such as setFoo() or getBar(). jOOQ will map values through Field<?> references in setValue() and getValue(). The reason is simple: Many queries performed with jOOQ are not queries against a single table, where the resulting record has well-defined setters and getters. Most queries will produce "ad-hoc", generic records. But this is feasible, of course. The simple delegating base class that you mentioned would allow for overriding setValue() and getValue() behaviour.

It would also need to allow for overriding the changed() behaviour, as tracking jOOQ's internal "changed" flag would need to be delegated as well.

Now, I don't know if overriding the behaviour of single set/get methods is good enough. If you want to perform sophisticated record <-> domain mapping algorithms, you might want to combine two database values into one domain object. For instance (FROM_DATE, TO_DATE) <-> MyObject.DateRange. But maybe, that's feasible through only set/get.

But generally, I feel that such a model solves only few problems while adding a lot of complexity to the already very complex org.jooq.UpdatableRecord type...

[Daniel Danilatos]

On Sat, Sep 21, 2013 at 12:20 PM, Lukas Eder <lukas...@gmail.com> wrote:

Nonetheless, I think that supporting "inheritance" modelled as a set of 1:1 relationships with some sort of discriminator might be a good thing to implement in jOOQ. Obviously, such an implementation should be somewhat compatible with JPA, to follow the principle of least astonishment.

If jOOQ provides the right set of primitives, there should be no need to bake in this feature, as it would be easily implementable on top.  I know I'm oversimplifying, and I don't have all the hundreds of use cases in mind that you need to juggle, but as a simple example:  say the generated record classes did not fit into a large inheritance hierarchy, but instead subclassed a very simple delegating base class (merely to take care of most of the boilerplate) and then were implemented by receiving an inner record in their constructor. The actual code would be very similar - the setFoo and getBar methods would still delegate to setValue & getValue, except on an inner object, instead of their parent class. Except now I can provide any other backing record of my choosing in the constructor, and reuse all this conveniently generated code. For example, I can pass in a record that is the result of a join query, to get a certain view of it, read values with nice getters, make changes with nice setters, etc.

Aha, I see what you mean. Interesting. Let me challenge that :-)

jOOQ doesn't rely on any setters or getters, such as setFoo() or getBar(). jOOQ will map values through Field<?> references in setValue() and getValue(). The reason is simple: Many queries performed with jOOQ are not queries against a single table, where the resulting record has well-defined setters and getters. Most queries will produce "ad-hoc", generic records. But this is feasible, of course. The simple delegating base class that you mentioned would allow for overriding setValue() and getValue() behaviour.

It would also need to allow for overriding the changed() behaviour, as tracking jOOQ's internal "changed" flag would need to be delegated as well.

Now, I don't know if overriding the behaviour of single set/get methods is good enough. If you want to perform sophisticated record <-> domain mapping algorithms, you might want to combine two database values into one domain object. For instance (FROM_DATE, TO_DATE) <-> MyObject.DateRange. But maybe, that's feasible through only set/get.

But generally, I feel that such a model solves only few problems while adding a lot of complexity to the already very complex org.jooq.UpdatableRecord type...

What I'm proposing hopefully shouldn't add any complexity (I'm really just advocating composition over inheritance) - basically saying (approximately) whatever you do now that delegates to an abstract base class, delegate to a wrapped record. It may even reduce complexity, and it will allow more combinations of possibilities hence more flexibility (as a minimum I could implement the fancy features described above, but I predict there is much more).

Of course I don't want to come across as suggesting you go and refactor everything... just throwing out an idea, and one that could potentially be done incrementally (though that would by definition add complexity because you'd have two ways of doing things, if you purely added facilities for composition without removing much of the inheritance).

For the record I solved my problem at the application level using a high-level wrapper class, which ended up OK, but it means the application code has two ways of doing things depending on whether or not I'm dealing with these joined records via a wrapper, or directly with regular records.

Best,

Dan

[Lukas Eder]

2013/9/21 Daniel Danilatos <dan...@danilatos.com>

I think it's hard to assess how complex support for view records would be without going through the exercise of actually trying to implement it. #2721 has already been registered, hopefully for jOOQ 3.3.0, which will certainly be released in 2013:

https://github.com/jOOQ/jOOQ/issues/2721

Anyway, no matter if I will come to the conclusion that this is a valuable addition in terms of cost/effect ratio, it's certainly a very interesting idea!

Cheers

Lukas

[Daniel Danilatos]

Great, thanks. I'll make further comments there to avoid spamming this list.

--

[Daniel Danilatos]

On Tue, Sep 3, 2013 at 1:39 AM, Lukas Eder <lukas...@gmail.com> wrote:

Example use case 2: Disambiguate fields. If I go dsl.select()... with some joins, and the table field names clash, jooq / sql will not complain because when generating the SQL jooq disambiguates the field names with tables. However in the returned records, it seems that table names are ignored and whatever fields came last clobber earlier fields (usually one would prefer the other way round, but that's not 100% of the time either).

That shouldn't be the case. If you have an exact match of table name / field name, you should get the right value, even if field names clash. Can you provide a test case to reproduce the issue?

I've run into this again. To clarify, I'm not aliasing fields; I'm just doing what is effectively:

select().from(FOO.join(BAR).on(FOO.ID.eq(BAR.ID))).where(...conds...).fetchOneInto(FooRecord.class)

And jooq is generating

select foo.id, foo.xxx, bar.id, bar.yyy from foo join bar on (foo.id=bar.id) where ...

In my use case, the main records I'm after here are "foo" records, and the joins are for various filterings and so on. But because "bar" comes after, and happens to also have a field named "id", I guess jooq is using bar's value of "id" even though in the generated SQL it's qualifying all the fields. So I end up with an instance of FooRecord which has bar's id in it, that seems wrong no matter which way you look at it. Somewhere in there, jooq's lost the fact that the "id" came from "bar", rather than disambiguating when placing values into the FooRecord. I think it would equally make sense that if I went .fetchOneInto(BarRecord.class) that i'd get bar's id in there.

The workaround is to explicitly enumerate the fields, e.g.

select(FOO.ID, FOO.XXX).from(.... rest is the same...

(or FOO.fields())

This is OK because I don't need any of Bar's fields. But I feel uneasy knowing that if I accidentally neglect to be explicit about the fields, everything will "seem to work" except I'll have wrong data in my retrieved records.

[Lukas Eder]

Hi Daniel,

I'm sorry for the delay. I've been presenting jOOQ at the Topconf in Tallinn, so a couple of E-Mails have been piling up.

2013/11/5 Daniel Danilatos <dan...@danilatos.com>

Yes, this does indeed seem problematic. So far, I might not have been paying enough attention to the fact that you're using .fetchOneInto(BarRecord.class). I will have to play around with a couple of scenarios and I've registered #2836 for this

https://github.com/jOOQ/jOOQ/issues/2836

I'll have to check if there are sufficient formal connections between the resulting FOO.ID column and the FooRecord.setId() setter, to make assignment unambiguous.

Cheers

Lukas

[Lukas Eder]

This issue is in fact the same as the one being discussed here:

https://groups.google.com/d/msg/jooq-user/OTXq3ePpdtw/AMKaFH9s8bIJ

The problem arises when people want to fetch an active record (FooRecord), which they can store after fetching. At the same time, they want to use a JOIN to express a semi-join predicate, which could also be expressed with IN:

// This query...

select().from(FOO.join(BAR).on(...)).where(...conditions...).fetchOneInto(FooRecord.class);

// ... has the same intent as this one:

selectFrom(FOO).where(FOO.ID.in(select(BAR.ID).from(BAR)).and(...conditions...).fetch();

For your particular solution, I guess that the DefaultRecordMapper should be fixed in a way that whenever the passed <E> type (FooRecord.class) is a type implementing TableRecord, then the mapping should take that Record's TableFields into consideration.

The algorithm might be quite difficult to implement correctly without introducing regressions.

I'll think about these things a bit longer.

Cheers

Lukas

2013/11/10 Lukas Eder <lukas...@gmail.com>

[Lukas Eder]

Hi Daniel,

Issue #2836 could be solved for jOOQ 3.3:

https://github.com/jOOQ/jOOQ/issues/2836

I won't merge this fix to jOOQ 3.2, as this fix introduces a subtle change of behaviour to the DefaultRecordMapper, which might be unexpected

Cheers

Lukas

2013-12-07 Lukas Eder <lukas...@gmail.com>: