[Dbix-class] refining conditions in a "has_many" constraint

[John Napiorkowski]

I have the following sitution:  I have a table that is
a foreign key to another table.  The second table has
an additional self referent in it.  Like this:

Documents
  --document_id (primary key)
  --(etc)

Folders
  --folder_id (primary key)
  --document_id (FK to Document.document_it)
  --parent_id (FK to self, Folder.folder_id)
  --(etc)

In my schema declaration for the Document table I can
use the 'has_many' method so that I can easily access
Folders through Documents.  However I would like to
add a restriction to this 'has_many' declaration.  The
restriction I would like to add is to only allow
access to folders through a Document that 1) match the
document_id (easy) and 2) where the parent_id equals
the folder_id (hard for me).

This way when I do
"$db->resultset('Documents')->Folders" I get just the
Folders that are 'parents to themselves' (root folders
in this case) and not all the folders in the Folders
table.

Now the documentation says you can pass conditions
formated using SQL::Abstract, but for the life of me I
can't get it to work.  Here's stuff I tried:

package Schema::Documents;

use base 'DBIx::Class';

__PACKAGE__->load_components(qw/ PK::Auto Core /);
__PACKAGE__->table('Documents');
__PACKAGE__->add_columns(qw/document_id ...etc.../);
__PACKAGE__->set_primary_key('document_id');

__PACKAGE__->has_many(

  'Folders' => 'Schema::Folders',
  {
    'foreign.document_id' => 'self.document_id',
    'foreign.parent_folder_id' => 'foreign.parent_id'
  },

);

But I just get an error when I run this that says I
have an invalid relation.  Looking at the SQL from the
debug mode doesn't add any enlightenment.

For the life of me I can't find any examples of what
to do here.  I tried all sorts of different things but
nothing seems to work.  SQL::Abstract is quite dense
for me, sadly.  Is there anyone out there that know
what to do to accomplish this task?

Here is an example of the SQL that would perform the
function I am trying to accomplish:

select * from Folders where folder_id = parent_id and
document_id = ?

Where '?' is bound to the document_id from Documents.

Please let me know if I can elucidate this issue more
clearly.

I know I've asked a lot of questions on this list in a
short time, so please pardon if I am spamming the
group.  I hope I can make this example work well.  If
I can make this work I will make time to write a
detailed explaination of all I did here and maybe we
can add it someplace where it could help other people.

Thanks again,

John Napiorkowski

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com