Select articles from group with id '1' and id '2'

Rails
1

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
2

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 with your model name.
On 21/02/06, Henrik Ormåsen [email protected] 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?

.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 D. [email protected]

3

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).

4

Tue, 21 Feb 2006, Filip G. 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
5

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
6

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
7

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