Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

hide (4398)

hide
  (email not shown publicly)
http://blogs.sackheads.org/hide

hide has been programming in Perl since 1998. Much of this time has been focused on system automation and data warehousing.

On CPAN as: SSORICHE [cpan.org]

Journal of hide (4398)

Thursday December 16, 2004
09:01 PM

Class::DBI OH MY!

[ #22347 ]
I've had this idea for a home project for a number of months and just recently started working on it. I quickly created a database design (that was scrapped and rewritten based on input) and a SAX parser to load the data. Having heard good things about CDBI I felt this would be a good project for me to learn it on. I've been using DBI and SQL at work for a while now, and I'm quite comfortable with writing SQL queries to retrieve the data I need, but as this was an exercise in learning CDBI I really wanted to make it work.

The problem being that I want to retrieve data from one table that matched criteria in two other tables:

            -----------
            - table_1 -
            -----------
     -------- pkey    ------
    |       - title   -     |
    |       - descr   -     |
   /|\      -----------    /|\
-----------             -----------
- table_2 -             - table_3 -
-----------             -----------
- pkey    -             - pkey    -
- fkey    -             - fkey    -
- date    -             - field_3 -
-----------             -----------

select title, descr
  from table_1, table_2, table_3
  where table_1.pkey=table_2.fkey and date='2004-12-16' and
     table_1.pkey=table_3.fkey and field_3 = 'test';

(please note these are only examples and haven't been tested)

While this was easy to come up with in SQL, I was having a tough time figuring out how to achieve the same results in CDBI. At this point I turned to rjbs for guidance. He knew what I was looking for and explained to me what I needed to do. I just wasn't getting it, what seems so simple in SQL was becoming stressful in CDBI. rjbs stuck with me and answered my questions.

I think I've got a solution now that involves retrieving the values of fkey from both table_2 and table_3, finding their intersection and returning the intersection through _ids_to_objects. Resulting in a list from table_1.

I want to thank rjbs for his help - Thank you!


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Class::DBI OH MY! 2 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • There is a UsingJoins [class-dbi.com] section on the wiki that is quite good.

    The documentation also has quite a lot about using custom sql and Class::DBI provides things like sth_to_objects which allows you to 'SELECT __ESSENTIAL__ from table_foo a, table_bar b where %s' when used with SQL::Abstract.

    I was working on something like this last night and it works a treat. I was working from Randal's rather handy example.

    --

    @JAPH = qw(Hacker Perl Another Just);
    print reverse @JAPH;

  • package TableOne;
     
    # set up columns and table here...
     
    __PACKAGE__->set_sql('by_join' => qq{
    select title, descr
    from table_1, table_2, table_3
    where table_1.pkey=table_2.fkey and date='2004-12-16' and
    table_1.pkey=table_3.fkey and
    field_3 = 'test'});
     
    # then, in some nearby code:
     
    my @table_ones = TableOne->search_by_join();

    Using SQL directly is what makes Class::DBI so useful, in my opinion. You always have an escape hatch when you need it.

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.