Model.find statements: SQL condition format -> How?

Hi there,

What's the correct end part of "conditions" if:

- you assume that "Book :has_many Authors"
- I'd like to have the find statement in the below format

@books = Book.find(:all,
          :joins => [:authors => [...]],
    :include => :whatever_stuff,
          :conditions => "books.published = TRUE AND
                          number-of-authors-must-be-greater-than-5")

Thank you for your help!
Tom

The raw SQL will be along the lines of:

  SELECT books.*, count(books.id) AS amount_of_authors FROM books JOIN
authors ON books.id = authors.book_id GROUP BY books.id HAVING count(
books.id ) > 5

Get that working in your SQL query editor against the real DB to be
sure the right syntax gives the right result for you, and then you
should be able to turn that into an AR find with a bit of reference to
the API.

As an aside, I wonder about your model... when I've looked at things
link Authors/Articles before, I've tended to have a join table like
"Authorships", so that I can keep a unique instance of the information
about the author as a person, and multiple records for their
participation in books/articles/whatever. You still have a many:many
relationship between books and authors, but you just go "through"
authorships.

Thanks - and no worries re the model, it's just an example...

Actually, I asked this question since I'd like to stick as much as
possible to the "Rails way" of doing things (and therefor I'd like to
try to avoid things like "original" SQL statements).

So, any idea how the part...

   :conditions => "books.published = TRUE AND
                   number-of-authors-must-be-greater-than-5"

...would have to be? (In the Rails kind of way?)

(I tried using the API, but don't seem to get it.)

I would start by getting the easy bit going (published is true), then
work out the trickier part. I don't think you need the joins, but you
will want to include authors I think.

I have not tried it but for the number of authors have you tried a
condition involving authors.count > 5? If you are not getting the
results you expect look at the sql in the log and see what is wrong
with it.

Colin

Yep, it's really the "number of authors" part that I have not understood
yet.

If I try as you say (using authors.count > 5 in the "conditions") an
error message tells me that there is no column called "count"...

Thanks - and no worries re the model, it's just an example...

If the code you post is an example, then SQL I posted won't work. As I
said, I'd suggest you get the SQL working for your specific situation,
and then try to get the Rails find to generate the right SQL - you can
compare the output in the log file to the SQL you know works.
The SQL I posted does *exactly* what you asked for (at least here on
models of mine it does)... have you run it to see what it does for
you?

Actually, I asked this question since I'd like to stick as much as
possible to the "Rails way" of doing things (and therefor I'd like to
try to avoid things like "original" SQL statements).

Of course, but until you know what resulting SQL statement you want to
get to, how can you form the finder options?

So, any idea how the part...

:conditions => "books.published = TRUE AND
number-of-authors-must-be-greater-than-5"

...would have to be? (In the Rails kind of way?)

(I tried using the API, but don't seem to get it.)

What part of the documentation don't you "get"?
http://api.rubyonrails.org/classes/ActiveRecord/Base.html

If there's part of it that doesn't make sense, then we can try to help out.

Get the SQL working for your specific requirement (so you're not
guessing with "example" models), and then figure how to get AR to
generate it. The documentation explains about JOINS, GROUP BY and
HAVING - all the components of the SQL you need to get your job done.

Worst case scenario; post the working SQL here and ask "how can I
generate this exact SQL in an AR find method?".

Thanks for getting back!

Well, to put it bluntly:

I cannot figure out what parameter(s) (like :limit, :group, etc.) of the
find method I have to use to create the count "condition" in the find
statement.

By "count condition" I mean the part:

  number-of-authors-must-be-greater-than-5

Assume that we're in a simply Model_A :has_many Model_B case like...

  Book :has_many Authors

...and that I'd like to avoid the find_by_sql method (raw SQL), if
possible.

Michael
To some extent I agree with Tom here, ideally it should be possible to
get straight to the answer without going through the sql. The
condition is that the number of authors for the book should be greater
than 5 and
that published should be true, so the requirement is fully defined,
the question is how to tell active record that that is what is
required. To some extent the framework has failed (or one is just
trying to do something too complex for it) if one has to work out the
sql first then work out how to tell ActiveRecord to generate that sql.

Colin

...and that I'd like to avoid the find_by_sql method (raw SQL), if
possible.

Yes - do not use "find_by_sql" for this...

But you *DO* need to know what the SQL is to generate the results you want.

I cannot figure out what parameter(s) (like :limit, :group, etc.) of the
find method I have to use to create the count "condition" in the find
statement.

I have already posted you an example that groups, counts and queries
according to your initial post.
You could try looking at that SQL and then looking up the SQL
components in the Rails documentation; or converting as much as
possible, in as small steps as possible, and asking for help with what
remains.

It's *impossible* to just give you the Ruby code to do the find if you
haven't given the exact model structure, or at least the exact SQL you
want to generate.

Assume that we're in a simply Model_A :has_many Model_B case like...

Book :has_many Authors

Again, I've shown you the SQL that does this, and the SQL that's in
your log file I can guarantee looks not very similar to it. You will
need to use the :group and :having parameters to duplicate those
components of the SQL.
You *can't* do "books.count" as in the DB there isn't a "count" column
in the books table.

Colin, I doubt that you or I would *need* to do the SQL first for our
own models; but I often do so anyway before writing a complex finder
as a safety-net to ensure Rails returns what I want. But if the OP
makes up an example that doesn't match his models, then we can't post
solutions. If he has no idea what the SQL needs to be to return data
from his database, then maybe there's some more non-Rails learning to
cover.

It's similar to the argument that anti-IDE people use; when they say
the IDE stops you understanding what's going on underneath. Well, if
one doesn't understand the SQL generated by a find, then that's not a
great situation.

For instance, in an app I've worked on, there's a Person model that
can have many Participations (not too dissimilar to Authors and
Books). If I want to return everyone with a surname beginning with "B"
who has more than one participation, I can use the following:

   Person.all(:select => "people.*, count(people.id)", :joins =>
:participations, :group => "people.id", :conditions =>
["people.lastname LIKE ?", "b%"], :having => "count(people.id) > 1")

... and frankly, I can leave the :select out if I don't care about the
amount of Participations, to let AR do its thing

But unless the OP can be *sure* the results are correct for his
implementation (by checking it against a SQL DB query) then it strikes
me as a little bit of a worry.

Playing devil's advocate again, I disagree, if one understands
ActiveRecord syntax fully and one codes up a find, then one *can* be
sure that it is correct. It should not be necessary to check that the
SQL is correct as one is then suggesting that ActiveRecord has made a
mistake. There may be other reasons to check the sql of course,
efficiency worries for example.

Colin

I concede that's very true.

(but it's the "understanding" that's the problem :slight_smile:

Yep, I would have liked to be able to use Rails without needing to dig
deeper into SQL - at least as long as I'm not a professional coder...

Anyways, from what I understand, the answer to my question is:

With Rails, I need to use the :select parameter to have the "count part"
(as described above), like so (and in combination with the :having
parameter):

  :select => "people.*, count(people.id)"
  :having => "count(people.id) > 1"

Thanks for your participation and patience!

Now *that* I would find it very difficult to play Devil's Advocate
against. (I think there is a syntax error in your final line, there
should be an extra ')' :slight_smile: )

Colin

Well, just like the answer to my question, this wasn't obvious from the
API.

What wasn't? Without quoting a previous message, no one knows what
you're replying to.

Again, I'll ask you; what bit of the API did you find confusing?
There's several references to how to use :group and :having - was
there something in the documentation in these areas that wasn't clear?
Or was it another part?

What wasn't? Without quoting a previous message, no one knows what
you're replying to.

I was replying to the last message: "there should be an extra ')'"

Again, I'll ask you; what bit of the API did you find confusing?

It's just that based on the API, I couldn't figure out if/where/how to
place the "count" part in the find statement...

The problem is a little circular - as the count is nothing to do with
Rails, it's a function of the DB, and you need to know how the SQL
works to be able to use the finder correctly. The API tells you you
*can* use :having and :group; but also that these are just the clauses
in SQL - it's hardly up to Ruby/Rails APIs to then teach us how our DB
of choice implements these options to select records.

If you were to do it in Ruby, with disregard to the DB, you *could* do
something like:

  books_with_over_five_authors = Book.all.select { |book|
book.authors.size > 5 }

It would probably (almost certainly) be *better* (in many ways) to do
it with parameters on the finder (but you need to know the SQL to
structure them correctly :wink:

PS In re-reading my earlier posts I sound a little stroppy/terse...
maybe I'm just in a "Marnen" mood today... apologies if it wasn't
apparent that I'm actually keen for you to understand and solve your
problem.

PS In re-reading my earlier posts I sound a little stroppy/terse...
maybe I'm just in a "Marnen" mood today... apologies if it wasn't
apparent that I'm actually keen for you to understand and solve your
problem.

Yeah, I want my money back!
:wink:

Michael Pavling wrote:
[...]

PS In re-reading my earlier posts I sound a little stroppy/terse...
maybe I'm just in a "Marnen" mood today... apologies if it wasn't
apparent that I'm actually keen for you to understand and solve your
problem.

Sheesh, I know it's bad when my name comes up like this in a thread I
haven't even been in! :slight_smile: I hope I haven't been that difficult...I guess
I have to get some rough edges off my writing style.

Best,