indirect relationships possible?

[Michael Lackhoff]

I have three tables/objects that are chained like this:
top_level -> indermediate_objects -> sub_sub_objects
and there are one to many relationships between them (and many to one
relationships in the opposite direction).

Now I tried to create an indirect relationship to get the most recent
sub_sub_object for my top_level objects. A working SQL looks like this:

select * from sub_sub_objects
where intermediate_id IN
(select id from intermediates where top_level_id = ?)
ORDER BY sub_sub_objects.somedate DESC
LIMIT 1;

The following doesn't work since the indirect column_map is not allowed
(a many to one relationship 'intermediate' exists):
my_relationship => {
type => 'one to many', # or 'one to one' ?
class => 'MyApp::SubSubObjects',
column_map => { id => 'intermediate.top_level_id'},
manager_args => {
sort_by => 'somedate DESC',
limit => 1,
},
},

Is it possible to create such an indirect relationship or should I use a
manager method for this kind of task? If so, do I need a 'with_objects'
or 'require_objects' parameter?

-Michael

[John Siracusa]

On Wed, Jan 25, 2012 at 11:55 AM, Michael Lackhoff <mic...@lackhoff.de> wrote:
> Is it possible to create such an indirect relationship or should I use a
> manager method for this kind of task? If so, do I need a 'with_objects'
> or 'require_objects' parameter?

I think you’d need to include intermediates and filter on
intermediates.top_level_id in your Manager query (or use a literal SQL
subquery in your Manager query) to get that result. The only
"indirect" relationship with any sort of shortcut support is a
many-to-many, and those assume you mostly want to ignore the "middle"
table.

-John

[Michael Lackhoff]

On 25.01.2012 18:01 John Siracusa wrote:

> I think you�d need to include intermediates and filter on

> intermediates.top_level_id in your Manager query (or use a literal SQL
> subquery in your Manager query) to get that result. The only
> "indirect" relationship with any sort of shortcut support is a
> many-to-many, and those assume you mostly want to ignore the "middle"
> table.

I don't understand yet how that would look like but I tried something
that should work if I follow the example in
<http://search.cpan.org/~jsiracusa/Rose-DB-Object-0.797/lib/Rose/DB/Object/Manager.pm#query_ARRAYREF>

[now with real identifier names]
my ($lb) = PVonline::Behandlungen->get_behandlungen(
query => [
'rezept.therapie_id' => $self->nr(),
],
sort_by => 'behandlung.termin DESC',
limit => 1,
);

There is a relationship 'rezept' in PVonline::Behandlungen but I still
get an error:
Invalid query parameter: rezept.therapie_id
Shouldn't that work? After all
'rezept.therapie_id' looks very much like 'vendor.region.name'

-Michael

[John Siracusa]

On Wed, Jan 25, 2012 at 2:33 PM, Michael Lackhoff <mic...@lackhoff.de> wrote:
> There is a relationship 'rezept' in PVonline::Behandlungen but I still
> get an error:
> Invalid query parameter: rezept.therapie_id
> Shouldn't that work? After all
> 'rezept.therapie_id' looks very much like 'vendor.region.name'

You need to include that relationship in with_objects or
require_objects before you can filter on its column values.

-John

[Michael Lackhoff]

On 25.01.2012 21:04 John Siracusa wrote:

> You need to include that relationship in with_objects or
> require_objects before you can filter on its column values.

Thanks, now it is working fine.

-Michael