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

[Peter Rabbitson]

Dennis Daupert wrote:
> 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 => { '-in' => \@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}    = [$c->model('HdeDB::Docs')->search(
>                           project_id => $project_id,
>                           )->search_related('doc_files',
>                           { doc_id => { '-in' => \@ids }
>                           })
>                           ];
> 
For starters search takes a reference as the first argument. So
the above should be:

...->search ({ project_id => $pr_id})->...

The rest seems fine. Use $c->model ('HdeDB')->storage->debug (1)
or DBIC_TRACE=1 to output the actual sql on STDERR - it will help a
lot with figuring out what is going (or not) on.