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

[Dennis Daupert]

I am not terribly good at sql, or I'd show what I'm
trying to do that way.

I have these three tables: docs, files, doc_files.
docs has titles and summaries; files has file system paths,
filenames.

Users upload multiple edited copies of files, so there
are multiple, incremented versions. I need to get a resultset
of doc objects and related files objjects for just the highest
versions of each file; this gets handed over to my Catalyst
web page for display. For example, here is a short list of
ids and filenames:

2030,Installation_Manual.draft0.doc

2031,Installation_Manual.draft1.doc

2032,Installation_Manual.draft2.doc

2033,Installation_Manual.draft3.doc

2034,Configuration_Guide.draft0.doc

2035,Configuration_Guide.draft1.doc
2036,Configuration_Guide.draft2.doc

I have a separate subroutine that gives me just the ids
and filenames for the highest versions:

2033,Installation_Manual.draft3.doc
2036,Configuration_Guide.draft2.doc

So, I am trying to build a query that can use
that info, like this:

{ doc_id =3D> { '-in' =3D> \@ids }

I've juggled lots of bits around, but still
haven't found the magic fairy dust. Just one example,
from trying to learn how to chain resultsets... This doesn't
do the job; it doesn't give any error; it just doesn't
get any data.

  $c->stash->{docs}    =3D [$c->model('HdeDB::Docs')->search(
                          project_id =3D> $project_id,
                          )->search_related('doc_files',
                          { doc_id =3D> { '-in' =3D> \@ids }
                          })
                          ];

For reference, here are bits from my tables (snipped):

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
package hde::Schema::Result::Docs;

__PACKAGE__->table("docs");
__PACKAGE__->add_columns(
  "id",
  { data_type =3D> "INTEGER", is_nullable =3D> 0, size =3D> undef },
  "project_id",
  { data_type =3D> "INTEGER", is_nullable =3D> 0, size =3D> undef },
  "title",
  { data_type =3D> "TEXT", is_nullable =3D> 0, size =3D> undef },);
__PACKAGE__->has_many('doc_files' =3D> 'hde::Schema::Result::DocFiles',
'doc_id');
__PACKAGE__->many_to_many('files' =3D> 'doc_files', 'file');
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
package hde::Schema::Result::Files;

__PACKAGE__->table("files");
__PACKAGE__->add_columns(
  "id",
  { data_type =3D> "INTEGER", is_nullable =3D> 0, size =3D> undef },
  "project_id",
  { data_type =3D> "INTEGER", is_nullable =3D> 0, size =3D> undef },
  "doc_id",
  { data_type =3D> "INTEGER", is_nullable =3D> 0, size =3D> undef },
  "path",
  { data_type =3D> "TEXT", is_nullable =3D> 0, size =3D> undef },
  "filename",
  { data_type =3D> "TEXT", is_nullable =3D> 0, size =3D> undef },);
__PACKAGE__->has_many('doc_files' =3D> 'hde::Schema::Result::DocFiles',
'file_id');
__PACKAGE__->many_to_many('docs' =3D> 'doc_files', 'doc');
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
package hde::Schema::Result::DocFiles;

__PACKAGE__->table("doc_files");
__PACKAGE__->add_columns(
  "doc_id",
  { data_type =3D> "INTEGER", is_nullable =3D> 0, size =3D> undef },
  "file_id",
  { data_type =3D> "INTEGER", is_nullable =3D> 0, size =3D> undef },
);
__PACKAGE__->belongs_to('doc' =3D> 'hde::Schema::Result::Docs', 'doc_id');
__PACKAGE__->belongs_to('file' =3D> 'hde::Schema::Result::Files', 'file_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

Any help is much appreciated.

/dennis
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090423/c8f=
2738f/attachment.htm