Rails - Aug 2005 - Problem with :joins and aliases

I have the following tables:

      users (id ...)
      feeds (id ...)
      user_feeds (id, user_id, feed_id ...)
      entries (id, feed_id ...)

I want to select all entries from an user''s feeds. Namely:

Entry.find_by_sql ["select f.title feed_title, e.* from entries e, feeds 
f, user_feeds uf where e.feed_id = f.id and uf.user_id = ? order by 
date_published DESC", user_id]

However, I want to take advantage of paginate(). So I tried converting 
my find_by_sql to find() (which paginate() uses):

Entry.find :all,
            :limit => 1, # for testing in script/console...
            :joins => ''inner join feeds f, user_feeds uf on 
entries.feed_id = f.id'',
            :conditions => [''uf.user_id = ?'', user_id],
            :order => ''date_published DESC''

Seems to work, with a caveat: both entries and feeds have a
''title''
feed. But the resulting row object only includes one title attribute, in 
this case, feed''s. Note that in find_by_sql, f.title is aliased to 
feed_title. Is there an option for aliases in find()? Any workaround?

Thanks in advance,

--Jonas Galvez

Jonas Galvez wrote:
> Seems to work, with a caveat: both entries
> and feeds have a ''title'' feed.
A ''title'' *field*, that is :)

--Jonas Galvez

Jonas Galvez wrote:
> I have the following tables:
>
>      users (id ...)
>      feeds (id ...)
>      user_feeds (id, user_id, feed_id ...)
>      entries (id, feed_id ...)
>
> I want to select all entries from an user''s feeds. Namely:
>
> Entry.find_by_sql ["select f.title feed_title, e.* from entries e, 
> feeds f, user_feeds uf where e.feed_id = f.id and uf.user_id = ? order 
> by date_published DESC", user_id]
>
> However, I want to take advantage of paginate(). So I tried converting 
> my find_by_sql to find() (which paginate() uses):
>
> Entry.find :all,
>            :limit => 1, # for testing in script/console...
>            :joins => ''inner join feeds f, user_feeds uf on 
> entries.feed_id = f.id'',
>            :conditions => [''uf.user_id = ?'', user_id],
>            :order => ''date_published DESC''
>
> Seems to work, with a caveat: both entries and feeds have a
''title''
> feed. But the resulting row object only includes one title attribute, 
> in this case, feed''s. Note that in find_by_sql, f.title is aliased
to
> feed_title. Is there an option for aliases in find()? Any workaround?
>
>
With current trunk, you can use a select option as argument to find.

Entry.find :all,
           :limit => 1, # for testing in script/console...
           :select => ''e.*, f.title AS feed_title'',
           :joins => ''e inner join feeds f, user_feeds uf on 
entries.feed_id = f.id'',
           :conditions => [''uf.user_id = ?'', user_id],
           :order => ''date_published DESC''


And paginate supports it too.

-- stefan kaes

On Wednesday 03 August 2005 17:38, Jonas Galvez wrote:
> However, I want to take advantage of paginate(). So I tried
> converting my find_by_sql to find() (which paginate() uses):
>
> Entry.find :all,
>             :limit => 1, # for testing in script/console...
>             :joins => ''inner join feeds f, user_feeds uf on
> entries.feed_id = f.id'',
>             :conditions => [''uf.user_id = ?'',
user_id],
>             :order => ''date_published DESC''
>
> Seems to work, with a caveat: both entries and feeds have a
''title''
> feed. But the resulting row object only includes one title attribute,
> in this case, feed''s. Note that in find_by_sql, f.title is aliased
to
> feed_title. Is there an option for aliases in find()? Any workaround?
I may not be understanding what you''re doing, but why are you using 
a :join option instead of :include?

Michael

-- 
Michael Schuerig                 Nothing is as brilliantly adaptive
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org       as selective
stupidity.
http://www.schuerig.de/michael/    --A.O. Rorty, The Deceptive Self

Michael Schuerig wrote:
> I may not be understanding what you''re doing, but why are you
using a
> :join option instead of :include?
For table users (id ...):

      class User ...
          has_many :user_feeds
      end

For table feeds (id ...):

      class Feed ...
          has_many :user_feeds
          has_many :entries
      end

For table user_feeds (id, user_id, feed_id ...):

      class UserFeed ...
          belongs_to :feed
          belongs_to :user
      end

For table entries (id, feed_id ...)

      class Entry ...
          belongs_to :feed
      end

As you can see, there''s no direct relationship between UserFeeds and 
Entry, so :include => [:feeds, :user_feeds] fails. I presume I could 
create the relationship using finder_sql(?) in has_many, not sure, but 
that seems too much of a pain to me. Any suggestion?

--Jonas Galvez

Stefan Kaes wrote:
> With current trunk, you can use a select option as argument to find.
So I would have to use ''Rails unstable'', eh?

Hmm, fortune favors the bold heh...

--Jonas Galvez

On Thursday 04 August 2005 01:28, Jonas Galvez wrote:
> Michael Schuerig wrote:
> > I may not be understanding what you''re doing, but why are you
using
> > a :join option instead of :include?
>
> For table users (id ...):
>
>       class User ...
>           has_many :user_feeds
>       end
>
> For table feeds (id ...):
>
>       class Feed ...
>           has_many :user_feeds
>           has_many :entries
>       end
>
> For table user_feeds (id, user_id, feed_id ...):
>
>       class UserFeed ...
>           belongs_to :feed
>           belongs_to :user
>       end
Does this class have any more properties than the ones needed to carry 
the foreign keys? To me it looks as if you''re manually simulating what 
has_and_belongs_to_many already handles automatically.
> For table entries (id, feed_id ...)
>
>       class Entry ...
>           belongs_to :feed
>       end
>
> As you can see, there''s no direct relationship between UserFeeds
and
> Entry, so :include => [:feeds, :user_feeds] fails. I presume I could
> create the relationship using finder_sql(?) in has_many, not sure,
> but that seems too much of a pain to me. Any suggestion?
I''m too sleepy to investigate this in detail. I think, you can 
use :include to eagerly load all Feeds for a User. To go one level 
further to Entries, you have to explicitly write the :join clause.

Michael

-- 
Michael Schuerig                       Face reality and stare it down
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org        --Jethro Tull,
Silver River Turning
http://www.schuerig.de/michael/

Jonas Galvez wrote:
> Stefan Kaes wrote:
>
>> With current trunk, you can use a select option as argument to find.
>
>
> So I would have to use ''Rails unstable'', eh?
Not really, the changes are small and you can apply them to your local 
installation easily. Just copy pagination.rb from the controller dir and 
change function construct_finder_sql ar activerecord base.rb, like so:

       def construct_finder_sql(options)
          sql  = "SELECT #{options[:select] || ''*''} FROM
#{table_name} "
          sql << " #{options[:joins]} " if options[:joins]
          add_conditions!(sql, options[:conditions])
          sql << "ORDER BY #{options[:order]} " if
options[:order]
          add_limit!(sql, options)
          sql
       end
>
> Hmm, fortune favors the bold heh...
>
>
Sometimes ;-)