> There is a 1-[0,1] relationship between Person and Address with address_id
> being the nullable foreign key.
I think you should be good with single query/entity then (no need for
nested entities)
<entity name="person" query="select person.id, person.name,
person.address_id, address.zipcode from person left join address on
address.id=person.address_id">
On Sunday, June 13, 2010, Holmes, Charles V. <chol...@mitre.org> wrote:
> I'm putting together an entity. A simplified version of the database schema
> is below. There is a 1-[0,1] relationship between Person and Address with
> address_id being the nullable foreign key. If it makes any difference, I'm
> using SQL Server 2005 on the backend.
>
> Person [id (pk), name, address_id (fk)]
> Address [id (pk), zipcode]
>
> My data config looks like the one below. This naturally fails when the
> address_id is null since the query ends up being "select * from user.address
> where id = ".
>
> <entity name="person"
> Query="select * from user.person">
> <entity name="address"
> Query="select * from user.address where id = ${person.address_id}"
> </entity>
> </entity>
>
> I've worked around it by using a config like this one. However, this makes
> the queries quite complex for some of my larger joins.
>
> <entity name="person"
> Query="select * from user.person">
> <entity name="address"
> Query="select * from user.address where id = (select address_id
> from user.person where id = ${person.id})">
> </entity>
> </entity>
>
> Is there a cleaner / better way of handling these type of relationships?
> I've also tried to specify a default in the Solr schema, but that seems to
> only work after all the data is indexed which makes sense but surprised me
> initially. BTW, thanks for the great DIH tutorial on the wiki!
>
> Thanks!
> Charles
>
