[Dbix-class] Can I get help composing a query?

[Dennis Daupert]

On Fri, Apr 24, 2009 at 11:03 AM, Rob Kinyon <rob.kinyon at gmail.com> wrote:

> On Fri, Apr 24, 2009 at 10:54, Dennis Daupert <ddaupert at gmail.com> wrote:
> > But I have a question: given the warning you gave me regarding Cartesian
> > joins, would you mind pointing me towards the right kind of query for
> this?
>
> You just need to make sure you have JOIN conditions.
>

I've worked out the SQL that gives me the results I need,
at least at the database level:

*SELECT *
FROM docs
LEFT JOIN files
ON docs.id =3D files.doc_id
WHERE docs.project_id =3D 94
AND files.id IN ( '2036', '2033' );*

Trying to twist this into a working data structure has eluded me.
I would appreciate a peek in the answers in the back of the book
at this point.

Here are a number of formats I've tried, along with error results
(at the top of each code segment):

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Does not work. Error: No such relationship files
(I'm not sure why he can't find the relationship; it's defined
as a many_to_many. I've read thru the documentation until
my eyes are ready to fall out.)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
$c->stash->{docs}    =3D [$c->model('HdeDB::Docs')->search(
                      {
                        project_id =3D> $project_id,

                      },
                      {
                        join     =3D> [qw/ files /],
                      }
                      )];
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
This runs, but it isn't filtered to yield the highest versioned
files. Trying that next.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
$c->stash->{docs}    =3D [$c->model('HdeDB::Docs')->search(
                      {
                        project_id =3D> $project_id,

                      },
                      {
                        join     =3D> [qw/ doc_files /],
                      }
                      )];
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Does not work: Error: no such column: files_id
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
$c->stash->{docs}    =3D [$c->model('HdeDB::Docs')->search(
                      {
                        project_id =3D> $project_id,
                        files_id =3D> { '-in' =3D> \@ids }
                      },
                      {
                        join     =3D> [qw/ doc_files /],
                      }
                      )];
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Does not work: Unknown error (Catalyst app doesn't even start up)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
$c->stash->{docs}    =3D [$c->model('HdeDB::Docs')->search(
                      {
                        project_id =3D> $project_id,
                        doc_files.files_id =3D> { '-in' =3D> \@ids }
                      },
                      {
                        join     =3D> [qw/ doc_files /],
                      }
                      )];
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Does not work: Error: no such column: files_id
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
$c->stash->{docs}    =3D [$c->model('HdeDB::Docs')->search(
                      {
                        project_id =3D> $project_id,

                      },
                      {
                        join     =3D> [qw/ doc_files /],
                      }
                      )->search_related( 'doc_files',
                      {
                        files_id =3D> { '-in' =3D> \@ids }
                      },
                      )];
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Does not work: Error: search_related: result source 'Docs'
has no such relationship files
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
$c->stash->{docs}    =3D [$c->model('HdeDB::Docs')->search(
                      {
                        project_id =3D> $project_id,

                      },
                      {
                        join     =3D> [qw/ doc_files /],
                      }
                      )->search_related( 'files',
                      {
                        files_id =3D> { '-in' =3D> \@ids }
                      },
                      )];
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Help?

/dennis
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090424/143=
7eb25/attachment.htm