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

[Rob Kinyon]

On Fri, Apr 24, 2009 at 09:11, Dennis Daupert <ddaupert at gmail.com> wrote:
> On Fri, Apr 24, 2009 at 4:40 AM, Peter Rabbitson <rabbit+dbic at rabbit.us>
> wrote:
>>
>> 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.
>
> Thanks for your help, Peter.
>
> I get this STDERR output from the query I posted:
>
> SELECT doc_files.doc_id, doc_files.file_id
> FROM docs me
> LEFT JOIN doc_files doc_files
> ON ( doc_files.doc_id = me.id )
> WHERE ( ( ( doc_id IN ( ?, ? ) )
> AND ( project_id = ? ) ) ): '2036', '2033', '94'
>
> That looks all wrong. I need something like this
> (pseudo-sql coming up):
>
> SELECT docs.* files.*
> FROM docs, files
> WHERE project_id = ?
> AND doc_id IN ( ?, ? )

That second query is most certainly NOT what you want. That would be a
cartesian join - bad stuff. DBIC's query is doing exactly the right
thing based on your relationships and what I can figure from your
description. At this point, I'm willing to bet that your data isn't
what you think it is. Specifically, that files 2036 and 2033 aren't
associated with documents belonging to project 94. In other words, the
subroutine coming up with 2033 and 2036 is broken somehow.

-- 
Thanks,
Rob Kinyon