[Dbix-class] Trying to construct some DBIC SQL
Chisel Wright
chisel at herlpacker.co.uk
Mon Apr 16 11:00:33 GMT 2007
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.
More information about the Dbix-class
mailing list