mysql - 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 是 2 个不同的表。截至目前我已经达到了这个查询:

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

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

提前致谢

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