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"?
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 
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 ')' )
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 
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!
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! I hope I haven't been that difficult...I guess
I have to get some rough edges off my writing style.
Best,