Hey gang,
I have a UNION query with a right join that I need to implement in DBIx::Class.
Here's the query:
SELECT me.content_id
, ''
, ''
FROM content me
JOIN category category ON ( category.category_id = me.category_id )
WHERE ( category.category_name = 'family-photos'
AND me.active_flg = 'Y' )
UNION
SELECT i.content_id
, i.image_name
, i2.image_name
FROM image i
RIGHT JOIN content c ON (c.content_id = i.content_id)
JOIN image i2 ON (i.content_id = i2.content_id)
WHERE i.image_type = 'thumbnail'
AND i2.image_type = '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 posts
(http://www.mail-archive.com/[EMAIL PROTECTED]/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 seems
like that one worked for that situation. How would I implement the above
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='value'
union select a, b, c, 'var2' as v from table2
where d='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=TXT_TAGHM_Wave2_sharelife_012008_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
