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

On 1 June 2010 12:15, Tom Ha [email protected] wrote:

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

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.)

On 1 June 2010 12:15, Tom Ha [email protected] wrote:

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

Thank you for your help!

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”…

On 1 June 2010 13:03, Tom Ha [email protected] wrote:

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?”.

On 1 June 2010 13:45, Michael P. [email protected] wrote:

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?

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

On 1 June 2010 13:57, Tom Ha [email protected] wrote:

…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.

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.

On 1 June 2010 14:03, Colin L. [email protected] wrote:

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, 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.

On 1 June 2010 14:37, Colin L. [email protected] wrote:

if one understands
ActiveRecord syntax fully and one codes up a find, then one can be
sure that it is correct.

I concede that’s very true.

(but it’s the “understanding” that’s the problem :slight_smile:

On 1 June 2010 14:21, Michael P. [email protected] wrote:

sql first then work out how to tell ActiveRecord to generate that sql.
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

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!

On 1 June 2010 14:42, Michael P. [email protected] wrote:

On 1 June 2010 14:37, Colin L. [email protected] wrote:

if one understands
ActiveRecord syntax fully and one codes up a find, then one can be
sure that it is correct.

I concede that’s very true.

(but it’s the “understanding” that’s the problem :slight_smile:

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.

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…

On 1 June 2010 15:51, Tom Ha [email protected] wrote:

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.

On 1 June 2010 15:18, Tom Ha [email protected] wrote:

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?

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 P. 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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]