Rails - Dec 2005 - Inconsitencies between find() and count() in ActiveRecord

Hi guys,

I''m trying to do something that I imagine is quite common: implementing
a search for a particular record in a table (or ActiveRecord of Model). 
In order to display the search results properly, I call count() to count 
the number of search results before I do the actual search using find(). 
And this is where the problem is.

Sometimes I want to search using criteria that are in linked tables, e.g 
I might have 2 tables, ''book'' and ''author'',
and I want to find all the
books by a certain author. This is easy with find(); I can just put the 
author table in the :include parameter. On the other hand, for count() I 
must provide a fragment of SQL for the ''joins'' parameter.
Obviously
find() does this internally, generating the required SQL code from 
:include. It seems to me that this in an inconsitency between the two 
functions.

My question is, what is the function that find() calls to do this and 
can I call it myself?

Maybe there is an easier way to implement this search functionality 
using functions that I don''t know about. But in my opinion,
shouldn''t
count() be exactly like find(:all) except it contains ''SELECT count(*) 
FROM'' instead of ''SELECT * FROM''?

David.

-- 
Posted via http://www.ruby-forum.com/.

David,

These docs were pretty helpful:

http://rails.rubyonrails.com/classes/ActiveRecord/Base.html#M000700

Carl

On 12/5/05, sirdavidoff
<davewroberts-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>
<davewroberts-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>
wrote:
> Hi guys,
>
> I''m trying to do something that I imagine is quite common:
implementing
> a search for a particular record in a table (or ActiveRecord of Model).
> In order to display the search results properly, I call count() to count
> the number of search results before I do the actual search using find().
> And this is where the problem is.
>
> Sometimes I want to search using criteria that are in linked tables, e.g
> I might have 2 tables, ''book'' and
''author'', and I want to find all the
> books by a certain author. This is easy with find(); I can just put the
> author table in the :include parameter. On the other hand, for count() I
> must provide a fragment of SQL for the ''joins'' parameter.
Obviously
> find() does this internally, generating the required SQL code from
> :include. It seems to me that this in an inconsitency between the two
> functions.
>
> My question is, what is the function that find() calls to do this and
> can I call it myself?
>
> Maybe there is an easier way to implement this search functionality
> using functions that I don''t know about. But in my opinion,
shouldn''t
> count() be exactly like find(:all) except it contains ''SELECT
count(*)
> FROM'' instead of ''SELECT * FROM''?
>
> David.
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Hi Carl, thanks for replying.

I have indeed looked at the Rails API documentation, but the source code 
included only shows one level of functions. What I mean is that 
according to the API, for a find(:all) with an :include parameter, a 
function called find_with_associations() is called. However I don''t
know
what that function does because it is not part of the API.

I assume that it calls some function like generate_join_sql(:include). 
How can I find this out?


-- 
Posted via http://www.ruby-forum.com/.

sirdavidoff <davewroberts-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>
wrote:
> I''m trying to do something that I imagine is quite common:
implementing
> a search for a particular record in a table (or ActiveRecord of Model). 
> In order to display the search results properly, I call count() to count 
> the number of search results before I do the actual search using find(). 
> And this is where the problem is.
> 
> Sometimes I want to search using criteria that are in linked tables, e.g 
> I might have 2 tables, ''book'' and
''author'', and I want to find all the
> books by a certain author. This is easy with find(); I can just put the 
> author table in the :include parameter. On the other hand, for count() I 
> must provide a fragment of SQL for the ''joins'' parameter.
Obviously
> find() does this internally, generating the required SQL code from 
> :include. It seems to me that this in an inconsitency between the two 
> functions.
I submitted a bug relating to this issue a month ago:

http://dev.rubyonrails.org/ticket/2760

Phil

Hi Phil,

Good to know. In the meantime (before they get around fixing it), do you 
know of a function to generate join sql fragments from table names, or 
did you have to write one yourself? I thought it might get a bit 
complicated when there are has_and_belongs_to_many associations 
involved...

David.


-- 
Posted via http://www.ruby-forum.com/.

If you are bringing the records down the pipe anyway (I think you  
are, from what you wrote) then why not just use array.size as in:

@x = Model.find(:all, :conditions=>''blah blah blah'')
count = @x.size

bruce


On 5-Dec-05, at 4:07 AM, sirdavidoff wrote:
> Hi guys,
>
> I''m trying to do something that I imagine is quite common:  
> implementing
> a search for a particular record in a table (or ActiveRecord of  
> Model).
> In order to display the search results properly, I call count() to  
> count
> the number of search results before I do the actual search using  
> find().
> And this is where the problem is.
>
> Sometimes I want to search using criteria that are in linked  
> tables, e.g
> I might have 2 tables, ''book'' and
''author'', and I want to find all the
> books by a certain author. This is easy with find(); I can just put  
> the
> author table in the :include parameter. On the other hand, for count 
> () I
> must provide a fragment of SQL for the ''joins'' parameter.
Obviously
> find() does this internally, generating the required SQL code from
> :include. It seems to me that this in an inconsitency between the two
> functions.
>
> My question is, what is the function that find() calls to do this and
> can I call it myself?
>
> Maybe there is an easier way to implement this search functionality
> using functions that I don''t know about. But in my opinion,
shouldn''t
> count() be exactly like find(:all) except it contains ''SELECT
count(*)
> FROM'' instead of ''SELECT * FROM''?
>
> David.
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

No, the database I am using contains thousands of records so it
wouldn''t
be a good idea to actually retrieve all search results. Therefore what I 
do is count how many results I have and then retrieve them in groups of 
10 according to pagination...

-- 
Posted via http://www.ruby-forum.com/.

@pages, @records = paginate :records, :per_page => 10, ...


@pages.item_count #  => 237
@pages.page_count  # => 24
@records.size   # => 10


RTFM: http://api.rubyonrails.com/classes/ActionController/Pagination/ 
Paginator.html





On Dec 5, 2005, at 9:22 AM, sirdavidoff wrote:
> No, the database I am using contains thousands of records so it  
> wouldn''t
> be a good idea to actually retrieve all search results. Therefore  
> what I
> do is count how many results I have and then retrieve them in  
> groups of
> 10 according to pagination...
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Yeah, but in using pagination with a search I''d still be retrieving ALL
the search results from the database, wouldn''t I?


-- 
Posted via http://www.ruby-forum.com/.

sirdavidoff <davewroberts-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>
<davewroberts wrote:
> Yeah, but in using pagination with a search I''d still be
retrieving ALL
> the search results from the database, wouldn''t I?
no.

sdelmont wrote:
> @pages, @records = paginate :records, :per_page => 10, ...
> @pages.item_count #  => 237
> @pages.page_count  # => 24
> @records.size   # => 10
> 
> RTFM: http://api.rubyonrails.com/classes/ActionController/Pagination/
> Paginator.html
Hi,

I read the f****** manual, and had a look at the source a while ago as I 
ran into this problem. Paginate calls paginator_and_collection_for with 
your collection and options. Unfortunately it then discards any :include 
when passing to the count method as that won''t accept them anyway, it 
just takes conditions and joins. It doesn''t seem very helpful to list 
includes as accepted in this case.

The documentation for paginate claims the include option is passed to 
model.count, however model.count doesn''t accept includes, and I
can''t
see how it is passed in the source that I have.
>:include:	optional eager loading parameter passed to Model.find(:all,
*params) and Model.count
So far as I can see this means the include parameter doesn''t work with 
paginate just now (Rails 0.14.3) if you need to refer to other tables in 
your conditions statement, as the count won''t accept the conditions
with
foreign references. You have to use joins. If I''m wrong, please do let 
us know how to use this properly.
>From pagination.rb in ActionPack, line 185 :
 def paginator_and_collection_for(collection_id, options) #:nodoc:
      klass = options[:class_name].constantize
      page  = @params[options[:parameter]]
      count = count_collection_for_pagination(klass, 
options[:conditions],
                                              options[:join] || 
options[:joins])

      paginator = Paginator.new(self, count, options[:per_page], page)
      collection = find_collection_for_pagination(klass, options, 
paginator)

      return paginator, collection
    end



-- 
Posted via http://www.ruby-forum.com/.

There is a new plugin that enhances Model.count so that it is
consitent Model.find...

Counting and limiting with named associations --
http://wiki.rubyonrails.com/rails/pages/Plugins#countlimit

On 12/5/05, Philip Ross
<phil.ross-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> sirdavidoff <davewroberts-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>
wrote:
> > I''m trying to do something that I imagine is quite common:
implementing
> > a search for a particular record in a table (or ActiveRecord of
Model).
> > In order to display the search results properly, I call count() to
count
> > the number of search results before I do the actual search using
find().
> > And this is where the problem is.
> >
> > Sometimes I want to search using criteria that are in linked tables,
e.g
> > I might have 2 tables, ''book'' and
''author'', and I want to find all the
> > books by a certain author. This is easy with find(); I can just put
the
> > author table in the :include parameter. On the other hand, for count()
I
> > must provide a fragment of SQL for the ''joins''
parameter. Obviously
> > find() does this internally, generating the required SQL code from
> > :include. It seems to me that this in an inconsitency between the two
> > functions.
>
> I submitted a bug relating to this issue a month ago:
>
> http://dev.rubyonrails.org/ticket/2760
>
> Phil
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>