[Dbix-class] Trying to construct some DBIC SQL

[Matt S Trout]

On Mon, Apr 16, 2007 at 11:00:33AM +0100, Chisel Wright wrote:
> 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?

Custom from attr. Or unpick the DB design so you aren't relying on NULLs.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
Shadowcat Systems Ltd.   Contact mst (at) shadowcatsystems.co.uk for a quote
                                          http://www.shadowcatsystems.co.uk/