[Catalyst] Class::DBI, Controller::Root, set_sql "redefined"

[Marc Brooks]

So basically I must emulate the set_sql() method using the CDBICompat
(Class::DBI Ima::DB).  By doing so still doesn't solve my initial issue.  On
another note can the DBIx::Class handle complex updates like the one below.
Sorry about all the questions.  I didn't come across any examples from
within the DBIx::Class documentation.

        UPDATE catalog
           SET lft = lft + CASE
                           WHEN $rgt2 < $lft1
                           THEN CASE
                                WHEN lft BETWEEN $lft1 AND $rgt1
                                THEN $rgt2 - $lft1
                                WHEN lft BETWEEN $rgt2 AND $lft1 - 1
                                THEN $rgt1 - $lft1 + 1
                                ELSE 0 END
                           WHEN $rgt2 > $rgt1
                           THEN CASE
                                WHEN lft BETWEEN $lft1 AND $rgt1
                                THEN $rgt2 - $rgt1 - 1
                                WHEN lft BETWEEN $rgt1 + 1 AND $rgt2 - 1
                                THEN $lft1 - $rgt1 - 1
                                ELSE 0 END
                           ELSE 0 END,
               rgt = rgt + CASE
                           WHEN $rgt2 < $lft1
                           THEN CASE
                                WHEN rgt BETWEEN $lft1 AND $rgt1
                                THEN $rgt2 - $lft1
                                WHEN rgt BETWEEN $rgt2 AND $lft1 - 1
                                THEN $rgt1 - $lft1 + 1
                                ELSE 0 END
                           WHEN $rgt2 > $rgt1
                           THEN CASE
                                WHEN rgt BETWEEN $lft1 AND $rgt1
                                THEN $rgt2 - $rgt1 - 1
                                WHEN rgt BETWEEN $rgt1 + 1 AND $rgt2 - 1
                                THEN $lft1 - $rgt1 - 1
                                ELSE 0 END
                           ELSE 0 END;

-----Original Message-----
From: catalyst-bounces at lists.rawmode.org
[mailto:catalyst-bounces at lists.rawmode.org]On Behalf Of Adam Jacob
Sent: Tuesday, June 06, 2006 11:11 AM
To: The elegant MVC web framework
Subject: Re: [Catalyst] Class::DBI, Controller::Root, set_sql
"redefined" [ff]


On Jun 6, 2006, at 10:21 AM, Marc Brooks wrote:

> The problem at hand though is the complexity of the statements I am  
> using.
> I use the set_sql() from the Class::DBI since I can naturally write my
> statements.  Does the DBIx::Class have an equivalent of the set_sql()?
>
> EXAMPLE:
>
>         SELECT *
>           FROM catalog AS node1, catalog AS node2
>          WHERE node2.lft BETWEEN node1.lft AND node1.rgt
>            AND $level = (
>                SELECT COUNT(*)
>                  FROM catalog AS node3
>                 WHERE node3.lft BETWEEN node1.lft AND node1.rgt
>                   AND node2.lft BETWEEN node3.lft AND node3.rgt
>                   AND node3.id
>                       NOT IN (node2.id, node1.id)
>            )
>            AND node2.id != node1.id AND node1.id = '$id'

It has that, and so much more.  DBIx::Class works by letting you  
create ResultSet's from your data; looking at the database from the  
point of view of getting the data you want out of it (and inflating  
that data into an object you can use).  While DBIx::Class doesn't  
have direct support for sub-selects (yet) you can still accomplish  
them quite easily.

Love the DBIx::Class.  It doesn't solve every problem, but it solves  
the vast majority of them.. and where it makes compromises they tend  
to be acceptable ones.

Adam


_______________________________________________
Catalyst mailing list
Catalyst at lists.rawmode.org
http://lists.rawmode.org/mailman/listinfo/catalyst