• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

OnetoOne, Left Join and Count

 
GB Smith
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • 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;
}
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic