[Dbix-class] Trying to construct some DBIC SQL

[Chisel Wright]

I've read some of the docs, had a play around and just stared blankly at
my screen.
I can't see how to build some SQL that would be useful for one of my
projects.

The raw SQL looks like this:

   SELECT  "stuff"
   FROM    thread me
       JOIN post last_post
       ON ( last_post.post_id = me.last_post )

       LEFT JOIN forum_moderator forum_moderators
       ON (    forum_moderators.forum = me.forum
               and
               forum_moderators.person = NULL)
   WHERE ( active = true );

I have ::Schema::Thread and ::Schema::ForumModerator

Thread.pm has a has_many defined:

  __PACKAGE__->has_many(
    'forum_moderators',
    'ForumModerator',
    {
        'foreign.forum' => 'self.forum',
    }
  );

I can't quite work out what to throw at my ->search() to get DBIC to
generate the appropriate query.

If I have

  ->search( { 'forum_moderators.person' => $user_id }, ... );

I end up with

  WHERE ( active = true and forum_moderators.person = ? )

which isn't correct 'forum_moderators.person = ?' needs to be in the ON
( ... ) for the LEFT JOIN.

I did try

  __PACKAGE__->has_many(
    'forum_moderators',
    'ForumModerator',
    {
        'foreign.forum' => 'self.forum',
		'foreign.person' => 'self.creator',
    }
  );

Which put the clause in the desired place, but hard-wired it to the
wrong value (thread.creator) instead of a dynamic value for me to throw
at it.


It looks like I need to do:

  __PACKAGE__->has_many(
    'forum_moderators',
    'ForumModerator',
    {
        'foreign.forum' => 'self.forum',
		'foreign.person' => $user_id, 
    }
  );

which of course doesn't make sense (as has_many() is called on package
creation, not per-query).

Assuming someone read this far, any idea how I achieve this behaviour?

TIA,

Chisel
-- 
Chisel Wright
e: chisel at herlpacker.co.uk
w: http://www.herlpacker.co.uk/

  The following sentence is true.
  The previous sentence is false.