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

rlb3 has asked for the wisdom of the Perl Monks concerning the following question:

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
[download]

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
[download]

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,

rlb3

Comment on Class::DBI - Sorting by key in related table. Select or Download Code

Replies are listed 'Best First'.
Re: Class::DBI - Sorting by key in related table. by zby (Vicar) on Apr 24, 2005 at 20:34 UTC
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.	[reply]
Re^2: Class::DBI - Sorting by key in related table. by itub (Priest) on Apr 24, 2005 at 20:57 UTC
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);` [download] The example above is not very general because it only searches those specific fields, but sometimes it is all you need.	[reply] [d/l] [select]
Re^3: Class::DBI - Sorting by key in related table. by cbrandtbuffalo (Deacon) on Apr 25, 2005 at 16:02 UTC
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.	[reply]