Rose DB子选择(嵌套查询)

Question

我正在尝试运行一个查询，其中有一个子选择。我已经设置了Manager方法，一切工作正常。唯一的问题是我不知道如何继续这个查询:

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

重要的是要记住tableA和tableB是两个不同的表。到目前为止，我已经达到了这个查询:

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);

请帮忙，以便我可以将子查询添加到主查询

Thanks in advance

Answer 1

您可以使用clauses函数在查询的WHERE部分中包含任意子句。

它看起来像这样。

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