[Dbix-class] DBIx::Class query with UNION and RIGHT JOIN

[edwrb .]

Hey gang,

I have a UNION query with a right join that I need to implement in DBIx::Cl=
ass.
Here's the query:

SELECT me.content_id
     , ''
     , ''
  FROM content me =

  JOIN category category ON ( category.category_id =3D me.category_id )
 WHERE ( category.category_name =3D 'family-photos' =

   AND   me.active_flg          =3D 'Y' )
 UNION =

SELECT  i.content_id
     ,  i.image_name
     ,  i2.image_name
  FROM  image i
 RIGHT  JOIN  content c  ON (c.content_id =3D i.content_id)
        JOIN  image   i2 ON (i.content_id =3D i2.content_id)
 WHERE  i.image_type  =3D 'thumbnail'  =

   AND  i2.image_type =3D 'main'

I'm basically trying to get a thumbnail and main image for a content record=
 and if there's no image records, then get the content record anyways.
Does anyone have any recommendations for this?  I read in some previous pos=
ts (http://www.mail-archive.com/dbix-class@lists.rawmode.org/msg03061.html) =

that you can put the UNION statement in the order_by field in search and I =
also read about how you can "create a result source for the query".  I
don't really understand the result source for the query solution, but it se=
ems like that one worked for that situation.  How would I implement the abo=
ve
query in such a format? :
package MySchema::Whatever;
use base qw/DBIx::Class/;

__PACKAGE__->table('DUMMY');
__PACKAGE__->add_columns(qw/a b c v/);
__PACKAGE__->result_source_instance->name(\<<'');
(select a, b, c, 'var1' as v from table1
where d=3D'value'
union select a, b, c, 'var2' as v from table2
where d=3D'value')

Many thanks in advance,

-ed

P.S. Thanks to Zbigniew for his help with my previous conundrum!

_________________________________________________________________
Connect and share in new ways with Windows Live.
http://www.windowslive.com/share.html?ocid=3DTXT_TAGHM_Wave2_sharelife_0120=
08
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080127/a9d=
1ec7d/attachment.htm