Rose DB subselect (nested query ) on select

Question

I am trying to run a query, that has a subselect in it. I have set up the Manager method, and everything works fine. The only problem is i dont know how to proceed with this query :

SELECT * FROM tableA WHERE 
          name = 'Me' AND 
          class='Tester' AND 
          ( ( Department IN ( SELECT Department FROM 
                                     tableB WHERE 
                                     leader = 'Joe')  
                             OR 
              Leader  in ('','all')  )
         );

Its important to remember that tableA and tableB are 2 different tables . As of now i have reached this query :

my @leader = ('','all');

DB::tableA::Manager->get_tableA ( with_object => ['tableB'] , 
             query => [ name => 'Me',
                        class => 'Tester',
                        OR => [
                              leader => \@leader,
                              Department => [*** this is
     where i have to make the sub select. 
     Dont know how though **** ]
          ]
        ], 
debug => 1);

please help so that i can add that sub query to this main query

Thanks in advance

Answer 1

You can use the clauses function to include arbitrary clauses in the query's WHERE portion.

It would look like this.

DB::tableA::Manager->get_tableA ( with_object => ['tableB'] , 
  query => [ 
    name => 'Me',
    class => 'Tester',
  ],
  clauses => ["( Department IN ( SELECT Department FROM tableB WHERE leader = 'Joe' ) OR Leader  in ('','all') )"
);

CPAN: Rose::DB::Object::QueryBuilder, Functions