Rails - Feb 2006 - Select articles from group with id ''1'' and id ''2''

I have to tables: "articles" and "groups". Between I have
article_groups as a jointable. Now I want to select every article who
is a member of group with id ''1'' and id ''2''.
Whats best way to get
that?


Extract from schemas:

CREATE TABLE articles (
id int auto_increment,
ingress text NOT NULL,
story_text longtext,
constraint fk_items_users foreign key (user_id) references users(id),
primary key (id)
);
CREATE TABLE groups (
id int auto_increment,
name varchar(16) NOT NULL,
description varchar(32) NOT NULL,
constraint fk_items_users foreign key (user_id) references users(id),
primary key (id)
);
CREATE TABLE article_groups (
id int auto_increment,
expires_date datetime,
suspend datetime,
group_id int NOT NULL, # FOREIGN KEY groups.id,
article_id int NOT NULL, # FOREIGN KEY articles.id
constraint fk_items_groups foreign key (group_id) references
groups(id),
constraint fk_items_articles foreign key (article_id) references
article(id),
primary key (id)
);

- Henrik

Henrik:
Untested of course, but it will set you on the right track. This is
far from the best way, but it''s the most obvious, given your schema.
Replace <model> with your model name.
On 21/02/06, Henrik Orm?sen <henrik.ormasen@sos-rasisme.no>
wrote:
> I have to tables: "articles" and "groups". Between I
have
> article_groups as a jointable. Now I want to select every article who
> is a member of group with id ''1'' and id
''2''. Whats best way to get
> that?
<model>.find_by_sql(''SELECT articles.id FROM
articles,groups,article_groups WHERE article_groups.article_id = 1 or
article_groups.article_id = 2'')

--
Cheers,
Hasan Diwan <hasan.diwan@gmail.com>

I''m sure you can use AR''s HABTM relationship to do this
instead of
writing out the SQL.

Additionally, I''d use SQL''s IN function versus saying if this
OR if that. e.g.

find :all, :conditions => "#{ article_groups.article_id } IN (1,
2)"

- Rabbit

---

On 2/21/06, Hasan Diwan <hasan.diwan@gmail.com>
wrote:
> Henrik:
> Untested of course, but it will set you on the right track. This is
> far from the best way, but it''s the most obvious, given your
schema.
> Replace <model> with your model name.
> On 21/02/06, Henrik Orm?sen <henrik.ormasen@sos-rasisme.no> wrote:
> > I have to tables: "articles" and "groups". Between
I have
> > article_groups as a jointable. Now I want to select every article who
> > is a member of group with id ''1'' and id
''2''. Whats best way to get
> > that?
>
> <model>.find_by_sql(''SELECT articles.id FROM
> articles,groups,article_groups WHERE article_groups.article_id = 1 or
> article_groups.article_id = 2'')
>
> --
> Cheers,
> Hasan Diwan <hasan.diwan@gmail.com>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

I assume you meant articles which belong to group 1 *and* group 2.

I''m a RoR newbie, but with sql (and hence find_by_sql) you could select
them like this:

SELECT article_id, count(distinct group_id)
FROM article_groups
WHERE group_id in (1,2)
GROUP BY article_id
HAVING count(distinct group_id)=2

so you take the article_groups table (FROM), only keep the records that 
link articles to group 1 or group 2 (WHERE), then count how many groups 
each article is linked to (GROUP BY) and only keep the articles that 
link to two different groups (HAVING).

-- 
Posted via http://www.ruby-forum.com/.

Tue, 21 Feb 2006, Filip Godsmurf skrev:
> I assume you meant articles which belong to group 1 *and* group 2.
Thanks thats right!

But I also strive to find out how to count rows from this sql
call... How can I do that? (or is it recommended to do that in ruby?)

- Henrik

Sorry, I think I was a bit unclear here. I try again:

I have the following sql for fetching articles:

    @articles = Article.find_by_sql(["SELECT
                                           a.*
                                           FROM articles a
                                           JOIN article_groups ag ON
					   ag.article_id = a.id
                                           WHERE
                                           ag.group_id IN (10, 8)
                                           GROUP BY a.id
                                           HAVING COUNT(DISTINCT
					   ag.group_id) = 2
                                           ORDER BY a.pri desc,
                                           a.created_on desc limit ?,
					   ?",
                                           @article_pages.current.offset,
                                           @article_pages.items_per_page])

Now I need pagination, witch are done with:


   @article_pages = Paginator.new self,
   ArticleGroup.count_by_sql(["#{count_condition_sql}", id]), 2, page

If I try to set count_condition_sql to:

    count_condition_sql = ("SELECT count(*) FROM articles a
                        JOIN article_groups ag ON ag.article_id = a.id
                        WHERE
                        ag.group_id IN (10, 8)
                        GROUP BY a.id
                        HAVING COUNT(DISTINCT ag.group_id) = 2")

I just get 2 articles, and no links for pagination, even if there are
4 articles witch matches the sql call.

What should i set count_condition_sql to?

- Henrik

Haven''t got any answer yet, but I got this (less pretty perhaps)
solution:

    csql = Article.find_by_sql("SELECT a.* FROM articles a
                        JOIN article_groups ag ON ag.article_id = a.id
                        WHERE
                        ag.group_id IN (10, 8)
                        GROUP BY a.id
                        HAVING COUNT(DISTINCT ag.group_id) = 2")


    count_condition_sql = "SELECT FOUND_ROWS()"

If anybody has a better solution, they''re welcome :-).

- Henrik