• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

OnetoOne, Left Join and Count

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So I have these two POJOs as coded below. They are OneToOne and share a common PK/id value. Every Bar must have a parent Foo, but not every Foo has a Bar. Maybe we want to iterate over the Foo objects and do some kind of lookup to create the Bar. As such, we would like to
a) Get a count of all Foo objects with no child Bar then
b) Get a list of all Foo objects with no child Bar

When I do something like

select f from Foo where f.createdAt = ateParam and f.bar is null;

I always get an empty list since it seems to figure there is no need to do any left joins with the OneToOne w/shared PK scheme.

So if I do an explicit LJ:

select f from Foo f
left join fetch f.bar
where f.createdAt = ateToProcess
and f.bar.someData is null

That seems to work for (b). But then if I do:

select count(f) from Foo f
left join fetch f.bar
where f.createdAt = ateToProcess
and f.bar.someData is null

Then I get this kind of error:

"owner of the fetched association was not present "

So it does not like the aggregate function in there. So what is the solution? Do I have to fall back to Natice SQL for (a) part of this? I am not sure a result.size() on the 2nd query is such a good idea - this query could return up to a million rows if left unlimited.


@Entity
@Table(name = "foo")
public class Foo
{
@Id @GeneratedValue
private Long id;

@OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY, optional=true)
@PrimaryKeyJoinColumn
private Bar bar;

@Column(name = "created_at")
@Temporal(TemporalType.TIMESTAMP)
private Date createdAt;
}

@Entity
@Table(name="bar")
public class Bar
{
@OneToOne(cascade=CascadeType.ALL, optional=true)
@PrimaryKeyJoinColumn
private Foo foo;

@Id @GeneratedValue(generator="myForeignGenerator")
@org.hibernate.annotations.GenericGenerator(
name = "myForeignGenerator",
strategy = "foreign",
parameters = @Parameter(name="property", value="foo")
)
private Long id;

@Column(name = "some_data", length = 100)
private String someData;
}
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic