[Catalyst] Constants that refer to rows in a lookup table.

[mla]

Bill Moseley wrote:
> This is suppose to be an ORM-neutral question.
> 
> I often use lookup tables in the database, even for things like, say,
> cart_status:
> 
>     create table cart_status (
>         id      SERIAL PRIMARY KEY,
>         name    text NOT NULL,
>         active  boolean NOT NULL DEFAULT TRUE
>     );
> 
>     -- trust the sequence, Luke
>     insert into cart_status (name) values ('Pending');
>     insert into cart_status (name) values ('Completed');

Just try clarify... you're saying you have a foreign key from your
carts table to this table in order to keep things normalized, yes?

So...

CREATE TABLE carts (
   id SERIAL PRIMARY KEY
   ,cart_status_id integer NOT NULL REFERENCES cart_status
     ON UPDATE CASCADE ON DELETE CASCADE
   ...
);

Something of that sort?

And so you'd really like to select the carts based on the name
or some other descriptive field...

SELECT *
   FROM carts
        JOIN cart_status USING (cart_status_id)
  WHERE cart_status.name = 'Pending'

?

The PKs seem arbitrary in this case. I don't know if they
should be referenced at all. I'd tend to use a subselect...

   UPDATE carts SET cart_status_id = (
     SELECT id FROM cart_status WHERE name = 'Completed'
   );

But in a more ORM way, maybe something like

   my $status = Cart::Status->fetch(name => 'Pending');
   $cart_class->search(cart_status => $status->id);

Or if it can handle an object:

   my $status = Cart::Status->fetch(name => 'Completed');
   $cart_class->search(cart_status => $status);

Maurice