[Dbix-class] Many to many relationship searching

[Zbigniew Lukasiak]

2008/6/23 jakac <jakac at iprom.si>:
> Sorry for a typo...
>
> __PACKAGE__->belongs_to( "article_id", "MyDB::Articles
> ", { recipients_id => "recipients_id" }, );
>
> should be
>
> __PACKAGE__->belongs_to( "article_id", "MyDB::Articles", { article_id =>
> "article_id" }, );
>
> Still... the question is the same :)
>
>
> jakac wrote:
>
> Hello,
>
> I am developing application using Catalyst & Postgres and I have a simple
> many to many
> relationship that includes following three tables:
>
> - categories (category_id primary key, category_name, users_id, ......)
> - articles (article_id primary key, title, content.......)
> - categories_articles_rel (rel_id primary key, category_id, article_id)
>
> My schema includes:
> # CATEGORIES
> __PACKAGE__->has_many( "categories_articles_rel",
> "MyDB::CategoriesArticlesRel",  { "foreign.category_id" =>
> "self.category_id" }, );
>
> # ARTICLES
> __PACKAGE__->has_many( "categories_articles_rel",
> "MyDB::CategoriesArticlesRel", { "foreign.article_id" => "self.article_id"
> }, );
>
> # CATEGORIES_ARTICLES_REL
> __PACKAGE__->belongs_to( "category_id", "MyDB::Categories",  { category_id
> => "category_id" },);
> __PACKAGE__->belongs_to( "article_id", "MyDB::Articles", { recipients_id =>
> "recipients_id" }, );
>
>
> Now I want to get a list of all articles that are in categories that are
> owned by
> certain user and order them by some field... Using the plain SQL I would
> normally write:
>
> select a.* from articles a, categories c, categories_articles_rel rel
> where
> a.article_id = rel.article_id
> and
> c.category_id = rel.category_id
> and
> c.user_id = '$someuser'
> order by a.title;
>
> (if I want to order by title)
>
> So now my question is - how to do this using DBIx::Class?
>

Read perldoc DBIx::Class::Manual::Joining

Something like that should work:

$articles_rs->search({ ... } { join => { categories_articles_rel =>
category_id } })


And by the way I would call the relation category - to differ it from
the name of the column:
 __PACKAGE__->belongs_to( "article", "MyDB::Articles", { article_id =>
 "article_id" }, );

Cheers,
Zbigniew


> Thank you!
>
> ________________________________
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.rawmode.org
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.rawmode.org
>



-- 
Zbigniew Lukasiak
http://brudnopis.blogspot.com/
http://perlalchemy.blogspot.com/