[Catalyst] Read operation over multiple tables

[John Napiorkowski]

-- Nagarajan M <naga_cit at yahoo.co.in> wrote:

> Hi, 
>    
>   I have three tables and the relations described as
> below
>    
>   1. books
>        - book_id
>        - book_name
>    
>   (has many authors)
>    
>   2.  author
>        - author_id
>        - author_name
>    
>   (has many books)
>    
>   3. book_author
>       - book_author_id
>       - book_id (from book table)
>       - author_id (from author table)
>       - comments
>    
>   belongs to book
>   belongs to author
>    
>   Its all loaded successfully when I start the
> catalyst developement server.
>    
>   Here is the problem,
>    
>   I want to read data which involves all three
> tables,
>    
>   Here is the SQL,
>   =============
>   select c.* from book c, author b, book_author t
> where t.author_id=b.author_id and
> t.book_id=c.book_id and c.book_name='something';
>    
>   In the catalyst example, I seeing  only
>   $c->stash->{books} = [$c->model('NetDB::Book)->all
> statement to read elements in a table.
>    
>   How to do the above sql in catalyst DBIC plugin
> ...

You might find a better expert over at the DBIx
mailing list but I'll try to help (I am far from
expert, sorry)

Something like:

$c->model('NetDB::Book')->search({book_name=>$something},{join=>[qw/author
book_author/]});

But I am not sure at all what you are trying to get
from the query.  You want to get all the books
matching a given bookname which have authors?  Maybe
you want to get the author name from a given book
name?  If you set  up your manytomany correctly you
should just be able to do

$c->model('NetDB::Book')->search({bookname=>$bn})->authors;

should return an interator for the book authors if you
set up the many_to_many as suggested at:

http://search.cpan.org/~danieltwc/DBIx-Class-0.07002/lib/DBIx/Class/Relationship.pm#many_to_many

Check out the cookbook that is part of the docs for
DBIx::Class as well, that should help.

If you are still stuck, post on the DBIx mailing lisst
but make sure you show how you defined the classes.

Good luck,
john





>    
>    
>   Thanks for your time ....
>   Nagarajan
>    
>    
>    
> 
>  				
> ---------------------------------
>  Find out what India is talking about on  - Yahoo!
> Answers India 
>  Send FREE SMS to your friend's mobile from Yahoo!
> Messenger Version 8. Get it NOW>
_______________________________________________
> List: Catalyst at lists.rawmode.org
> Listinfo:
> http://lists.rawmode.org/mailman/listinfo/catalyst
> Searchable archive:
>
http://www.mail-archive.com/catalyst@lists.rawmode.org/
> Dev site: http://dev.catalyst.perl.org/
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com