Forum: Ruby on Rails Select articles from group with id '1' and id '2'

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
8520bd1ba7ef07ee81484fbda8ef4cbb?d=identicon&s=25 Henrik =?iso-8859-1?Q?Orm=E5sen?= (Guest)
on 2006-02-21 13:59
(Received via mailing list)
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
895a112c66d93cefcd06d2cf371219e6?d=identicon&s=25 Hasan Diwan (Guest)
on 2006-02-21 20:04
(Received via mailing list)
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>
42292bf8a0acbc98862d9306b501275d?d=identicon&s=25 Daniel Waite (rabbitcreative)
on 2006-02-21 20:14
(Received via mailing list)
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

---
39045f22049b1db3e3dd08c3bffac17e?d=identicon&s=25 Filip Godsmurf (godsmurf)
on 2006-02-21 20:35
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).
8520bd1ba7ef07ee81484fbda8ef4cbb?d=identicon&s=25 Henrik =?iso-8859-1?Q?Orm=E5sen?= (Guest)
on 2006-02-23 11:47
(Received via mailing list)
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
8520bd1ba7ef07ee81484fbda8ef4cbb?d=identicon&s=25 Henrik =?iso-8859-1?Q?Orm=E5sen?= (Guest)
on 2006-02-23 13:30
(Received via mailing list)
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
8520bd1ba7ef07ee81484fbda8ef4cbb?d=identicon&s=25 Henrik =?iso-8859-1?Q?Orm=E5sen?= (Guest)
on 2006-02-23 14:46
(Received via mailing list)
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
This topic is locked and can not be replied to.