# Select articles from group with id '1' and id '2' **URL:** \ **Category:** Rails\ **Created:** [February 21, 2006, 1:59pm UTC](https://www.ruby-forum.com/t/select-articles-from-group-with-id-1-and-id-2/54039 "2006-02-21T13:59:57Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [February 21, 2006, 1:59pm UTC](https://www.ruby-forum.com/t/select-articles-from-group-with-id-1-and-id-2/54039/1 "2006-02-21T13:59:57Z")
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 ---
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [February 21, 2006, 8:04pm UTC](https://www.ruby-forum.com/t/select-articles-from-group-with-id-1-and-id-2/54039/2 "2006-02-21T20:04:59Z")
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 [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [February 21, 2006, 8:35pm UTC](https://www.ruby-forum.com/t/select-articles-from-group-with-id-1-and-id-2/54039/3 "2006-02-21T20:35:58Z")
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). ---
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [February 23, 2006, 11:47am UTC](https://www.ruby-forum.com/t/select-articles-from-group-with-id-1-and-id-2/54039/4 "2006-02-23T11:47:46Z")
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 ---
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [February 21, 2006, 8:14pm UTC](https://www.ruby-forum.com/t/select-articles-from-group-with-id-1-and-id-2/54039/5 "2006-02-21T20:14:09Z")
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 * * * ---
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [February 23, 2006, 1:30pm UTC](https://www.ruby-forum.com/t/select-articles-from-group-with-id-1-and-id-2/54039/6 "2006-02-23T13:30:24Z")
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 ---
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [February 23, 2006, 2:46pm UTC](https://www.ruby-forum.com/t/select-articles-from-group-with-id-1-and-id-2/54039/7 "2006-02-23T14:46:12Z")
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