Forum: Rails France D'une requête SQL sale à un truc propre utilisant :includ

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.
2763d87b1843f2d116e006b6039133a3?d=identicon&s=25 Frederic de Villamil (Guest)
on 2007-03-28 22:03
(Received via mailing list)
Bonsoir,

J'ai la requête SQL suivante :

   self.find_by_sql([%{
      SELECT categories.id, categories.name, categories.permalink,
categories.position, COUNT(articles.id) AS article_counter
      FROM #{Category.table_name} categories
        LEFT OUTER JOIN #{Category.table_name_prefix}categorizations#
{Category.table_name_suffix} articles_categories
          ON articles_categories.category_id = categories.id
        LEFT OUTER JOIN #{Article.table_name} articles
          ON (articles_categories.article_id = articles.id AND
articles.published = ?)
      GROUP BY categories.id, categories.name, categories.position,
categories.permalink
      ORDER BY position
      }, true]).each {|item| item.article_counter =
item.article_counter.to_i }

Que je cherche à transformer en ça :

       self.find(:all, :include => :articles, :order => 'position
ASC', :conditions => "contents.published = 1",  :group =>
'categories.id, categories.name, categories.position,
categories.permalink', :order => 'position')

Malheureusement je n'arrive pas à voir comment récupérer
l'article_counter de la première requête. Quelqu'un aurait une idée ?

Merci d'avance
Frédéric
--
Frédéric de Villamil
frederic@de-villamil.com                        tel: +33 (0)6 62 19 1337
http://fredericdevillamil.com         Typo : http://typosphere.org
2763d87b1843f2d116e006b6039133a3?d=identicon&s=25 Frederic de Villamil (Guest)
on 2007-03-29 07:22
(Received via mailing list)
Le 28 mars 07 à 22:01, Frederic de Villamil a écrit :

>          ON articles_categories.category_id = categories.id
>
>       self.find(:all, :include => :articles, :order => 'position
> ASC', :conditions => "contents.published = 1",  :group =>
> 'categories.id, categories.name, categories.position,
> categories.permalink', :order => 'position')
>
> Malheureusement je n'arrive pas à voir comment récupérer
> l'article_counter de la première requête. Quelqu'un aurait une idée ?
>
> Merci d'avance
> Frédéric

Après pas mal de recherches et de tatonnements :
     self.find(:all, :include => :articles, :conditions =>
['contents.published = ?', true], :order => 'position').each { |cat|
cat.update_attributes(:article_counter => cat.articles.length) }

Où article_counter est un attr_accessor


--
Frédéric de Villamil
frederic@de-villamil.com                        tel: +33 (0)6 62 19 1337
http://fredericdevillamil.com         Typo : http://typosphere.org
This topic is locked and can not be replied to.