[Dbix-class] Joins and subqueries

[Kaare Rasmussen]

I remember there was a thread asking for SQL examples. So I'd like to present 
two rather simple SQL queries for which I'll ask how to do it with DBIC.

   SELECT *
      FROM fw_menu
    WHERE menu_id NOT IN (SELECT child_id FROM fw_menulink)
    AND menu_id NOT IN
    (SELECT menu_id FROM fw_usermenu WHERE usergroup_id = ?)

and

   SELECT m.*
      FROM fw_menu m JOIN fw_menulink
      ON (fw_menu.menu_id = fw_menulink.child_id)
      WHERE parent_id = ? AND fw_menu.menu_id NOT IN
      (SELECT menu_id FROM fw_usermenu WHERE usergroup_id = ?)

In CDBI you'll have to use add_constructor and/or set_sql. 

-- 

Med venlig hilsen
Kaare Rasmussen, Jasonic

Jasonic                 Telefon: +45 3816 2582
Nordre Fasanvej 12
2000 Frederiksberg      Email: kaare at jasonic.dk