[Dbix-class] Joins and subqueries

[Jess Robinson]

First, you convert those particular ones to using JOIN instead, then you 
solve ;)

    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 = ?)

is also:

    SELECT *
       FROM fw_menu
     JOIN fw_menulink ON fw_menu.menu_id != fw_menulink.child_id
     JOIN fw_usermenu ON fw_menu.menu_id != fw_usermenu.menu_id AND
                         fw_usermenu.usergroup_id = ?

This has the added side effect on running faster, since it only does the 
conditions once, and not once per row, as the SELECTs in the where clause 
would do..

As for actual subselects, they're in the pipeline!

Jess


On Wed, 18 Jan 2006, Kaare Rasmussen wrote:

> 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
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> 
>