[Dbix-class] help with dbix::class syntax

[Mark Smith]

> I am writing a small catalyst application for online maintenance of leave
> records at the office. I need to ensure that a dept head is not flooded with
> all the leave applications, but gets only those pertaining to his dept. I
> have a users table and a leave_application table. The first table contains
> the user_id, user_name and dept_id columns, while the second one contains
> the user_id and leave_id columns. I have declared a has_many relationship
> named 'leave_apps' and  a belongs_to relationship named 'usr' between the
> two tables. I am unable to put the following SQL query into DBIx::Class
> syntax.
>
> select l.leave_id from leave_application l,
>     users u,
> where u.user_id = l.user_id
> and u.dept_id = $dept_id_of_dept_head;

You should be able to do something like this:

my $apps = $c->model( 'YourDB::AppClass' )->search(
  {
    'usr.dept_id' => $dept_id,
  },
  {
    join => 'usr',
  }
);

The idea being, you are searching on your app class, but joining it
with the 'usr' relationship and doing the WHERE class on that
relationship.

There may be a simpler way to do this, something more natural, but
this should work.  :-)


-- 
Mark Smith / xb95
smitty at gmail.com