Performing table level joins (rather than joins across associations for a single object)

[Diab Jerius]

Please pardon the less than helpful subject line; I'm not quite sure
how to summarize my question.

I have a database with the following two tables:

CREATE TABLE observed_profile (
object_id integer NOT NULL,
profile_id integer NOT NULL,
filename text NOT NULL,
PRIMARY KEY (object_id, profile_id),
);


CREATE TABLE simulated_profile (
object_id integer NOT NULL,
profile_id integer NOT NULL,
model_id integer NOT NULL,
shell integer NOT NULL,
filename text NOT NULL,
PRIMARY KEY (object_id, profile_id, model_id, shell),
);

with a one-to-many association between the two tables. Here's the Schema

DBIx::DataModel
->Schema('Scatter')
->Table(qw/ObservedProfile observed_profile object_id profile_id
/)
->Table(qw/SimulatedProfile simulated_profile object_id profile_id
model_id shell/)
->Association( [ qw/ ObservedProfile observed_profile 1 object_id
profile_id / ],
[ qw/ SimulatedProfile simulated_profile * object_id
profile_id / ],
);

There's no guarantee that every row in simulated_profile has a
corresponding row in observed_profile.

I'd like to discover for which model_id's I have certain number of
rows in the simulated_profile table for each (object_id, profile_id)
tuple which is in the observed_profile table.

The hand-written SQL looks like this:

select
model_id
from simulated_profile join observed_profile using (object_id,
profile_id)
group by model_id having count(*) = 100

I'm unable to figure out how to create that join. I've tried

Scatter->table('ObservedProfile')
->join( 'simulated_profile' )
->select( -columns => 'model_id',
-group_by => 'model_id',
-having => 'count(*) = 100',
-result_as => 'sql' )

Which gives this:

0 'SELECT model_id FROM simulated_profile WHERE ( ( object_id = ? AND
profile_id = ? ) ) GROUP BY model_id HAVING ( count(*) = 100 )'
1 '?:object_id'
2 '?:profile_id'

which isn't quite the same.

I'd appreciate any clues on this.

Thanks,

Diab

[Dami Laurent (PJ)]

Hi Diab,

Beware that the join() method is polymorphic : it can be applied to a schema (for joining several tables),or to a table or row (for accessing related rows).
See https://metacpan.org/pod/distribution/DBIx-DataModel/lib/DBIx/DataModel/Doc/Design.pod#Polymorphic-join .
So here you probably want to join from the schema :

Scatter->join(qw/Observed_profile simulated_profiles/)->select(....).

Good luck, Laurent D.

> -----Message d'origine-----
> De : dbix-da...@googlegroups.com [mailto:dbix-
> data...@googlegroups.com] De la part de Diab Jerius
> Envoyé : jeudi 20 août 2015 23:45
> À : dbix-datamodel
> Objet : [dbix-datamodel] Performing table level joins (rather than joins across
> associations for a single object)

> --
>
> ---
> You received this message because you are subscribed to the Google Groups
> "dbix-datamodel" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to dbix-datamode...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Diab Jerius]

Laurent,

Thanks. I'll have to reread the Design document more carefully.

That works nicely.

Diab