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!
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.
Using SQL directly is what makes Class::DBI so useful, in my opinion. You always have an escape hatch when you need it.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();