Class::DBI - Sorting by key in related table.

[rlb3]

Hello,

I not sure how to do this and maybe someone here can help. I the actual database there are five tables but only want to deal with two in this example. I have a table called IssuedKey that is a linking table. I looks something like:

IssuedKey:
    id
    person_id
    auth_id
    building_id
    issued_date

I want to create a report of the people, who authorized, what building, and when they got the key, but I want to order by lname in the person table.

    Person:
        person_id
        fname
        lname
        status

And to add more confusion, I want to use Class::DBI::Plugin::Pager to divide the results by pages.

Can someone point me in the right direction.

Thanks,

[zby]

Having a similar problem - searching by a field from a related table - I resigned finding a CDBI solution and solved it in the database by creating a view.

[itub]

I've had similar problems but tackled them using the lower-level set_sql method in Class::DBI (views weren't available):

# search for keys in a given building, 
# issued to persons with a given status
IssuedKey->set_sql(building_id_person_status =>
  'SELECT id FROM IssuedKey, Person
   WHERE Person.person_id=IssuedKey.person_id
   AND IssuedKey.building_id=? AND Person.status=?');

my (@keys) = IssuedKey->search_building_id_person_status(
  $building, $status);

The example above is not very general because it only searches those specific fields, but sometimes it is all you need.

[cbrandtbuffalo]

For something like this, I would agree that set_sql is a good approach. There are things databases are pretty good at, and I think this is one of them. Note that if you are going to reference columns from another table in a join, you'll need to add them as TEMP columns in the class with the sql code.