Doing Subselect query in Rails... Syntax problem

I have to use subselect for this query. find_all_by_name and joins will not work. Please I need help with getting the correct syntax for doing subselect the rails way. The query works fine in mysql. I can't find the syntax in the docs or may be I was looking in the wrong place. My project is due tomorrow. Please help!!! Here is the query statement:

@books = Books.find_by_sql ["SELECT * FROM books WHERE title IN SELECT title FROM authors WHERE name = ?", params[:author][:name]]

One problem is where to put the () that will enclose the inner select. Or may be it is not needed.

Thanks Cypray.

I don't understand what tables and columns you've got here. Assuming you have a books table that has a title column, and an authors table with a name column, how do you relate books to authors? Is there a foreign key that relates the tables?

Jeff

Jeff Cohen wrote:

@books = Books.find_by_sql ["SELECT * FROM books WHERE title IN SELECT title FROM authors WHERE name = ?", params[:author][:name]]

One problem is where to put the () that will enclose the inner select. Or may be it is not needed.

Thanks Cypray. -- Posted viahttp://www.ruby-forum.com/.

I don't understand what tables and columns you've got here. Assuming you have a books table that has a title column, and an authors table with a name column, how do you relate books to authors? Is there a foreign key that relates the tables?

Jeff

Here is the table structure:

Author has id, name, title, book_id and other columns Book has id, title, isbn, and other columns.

They both have the title column. And, yes, authors has book_id as a foreign key for books. The inner query is to select title where name = the name that was passed in as the query condition. Then the outer query will select all rows where title is equal to the title returned by the inner query. The strange thing here is, title has a unique entry in authors table, but title has duplicate entries in the book table.

So, the main point here is to get all rows from the book table that has the same value as the title that was returned from the authors table, which was selected based on the name that was passed in.

I don't have a control over the table structure. So, changing it is not an option.

Cypray

Ok, so Author.find_by_name should definitely work:

Author.find_by_name(params[:author][:name])

If that's not working, then maybe there's a problem with case sensitivity? For example, in Postgres, I have to use the ILIKE operator like this:

Author.find :first, :conditions => ["name ILIKE ?", params[:author] [:name]]

Once you have an author object, author.book should give you the book.

Let me know if this helps...?

Jeff purpleworkshops.com

Jeff Cohen wrote:

where title is equal to the title returned by the inner query. The strange thing here is, title has a unique entry in authors table, but title has duplicate entries in the book table.

So, the main point here is to get all rows from the book table that has the same value as the title that was returned from the authors table, which was selected based on the name that was passed in.

I don't have a control over the table structure. So, changing it is not an option.

Ok, so Author.find_by_name should definitely work:

Author.find_by_name(params[:author][:name])

If that's not working, then maybe there's a problem with case sensitivity? For example, in Postgres, I have to use the ILIKE operator like this:

Author.find :first, :conditions => ["name ILIKE ?", params[:author] [:name]]

Once you have an author object, author.book should give you the book.

Let me know if this helps...?

Jeff purpleworkshops.com

Thanks for all your help Jeff. It is still not working. The find_by_name, gives me this error: "undefined method `find_by_name' for ...." I get that error any time I used find_by_name or find_all_by_name That was why I am going with find_by_sql in the first place.

Using, Author.find :first, :conditions => ["name ILIKE ?", params[:author] [:name]] gives me this error:

Mysql::Error: You have an error in your SQL syntax; check the manual.....

That is the same error I get when I use find_by_sql with subselect. And that was why I thought my subselect syntax is wrong, hence my search for the correct syntax for Rails subselect.

Cypray

Can you post the full details of the error message from your log file?

Also, if you open script/console, does Author.count and Author.find :first work as expected?

Jeff purpleworkshops.com

Jeff Cohen wrote:

Jeff Cohen wrote: Thanks for all your help Jeff. It is still not working. The find_by_name, gives me this error: "undefined method `find_by_name' for ...."

Can you post the full details of the error message from your log file?

Also, if you open script/console, does Author.count and Author.find :first work as expected?

Jeff purpleworkshops.com

Author.count in script/console works fine Author.find :first gives error: "undefined local variable or method"

Here is the log file.

Processing AuthorsController#show (for 127.0.0.1 at 2008-12-09 15:31:25) [GET]   Session ID: BAh7BiIKZmxhc2hJQzonQWN0aW9uQ29udHJvbGxlcjo6Rmxhc2g6OkZsYXNo%0ASGFzaHsABjoKQHVzZWR7AA%3D%3D--11d9bb75a8effe12dc7f6fda1b51c9d2ea9943db   Parameters: {"commit"=>"Submit", "action"=>"show", "id"=>"show", "controller"=>"authors", "author"=>{"name"=>"John"}}   e[4;36;1mAuthor Columns (0.047000)e[0m e[0;1mSHOW FIELDS FROM `authors`e[0m   e[4;35;1mSQL (0.015000)e[0m e[0mSHOW TABLESe[0m

NoMethodError (undefined method `find_by_name' for #<Class:0x3da796c>):

Cypray

@books = Book.find(:all, :joins=>"books inner join authors as a on books.title=a.name", :conditions => ['name LIKE ? ', '%'+params[:author][:name]+'%'])

try with the above code

Jay Mark wrote:

Priya Buvan wrote:

@books = Book.find(:all, :joins=>"books inner join authors as a on books.title=a.name", :conditions => ['name LIKE ? ', '%'+params[:author][:name]+'%'])

try with the above code

I put the code in Authors Controller as it is, and I get nil object error. Since I am using @authors in my view, I changed the @books to @authors. That gives no error but it returned empty row.

The selection of 'name' takes place in view\author\index.html and the result of the query will be displayed in view\author\show.html I think I have to return @authors for <% @authors.each do |geogr| %> to work in the view, unless their is a different way to do it with @books

Cypray

Jay Mark wrote:

Priya Buvan wrote:

@books = Book.find(:all, :joins=>"books inner join authors as a on books.title=a.name", :conditions => ['name LIKE ? ', '%'+params[:author][:name]+'%'])

try with the above code

I put the code in Authors Controller as it is, and I get nil object error. Since I am using @authors in my view, I changed the @books to @authors. That gives no error but it returned empty row.

The selection of 'name' takes place in view\author\index.html and the result of the query will be displayed in view\author\show.html I think I have to return @authors for <% @authors.each do |geogr| %> to work in the view, unless their is a different way to do it with @books

Cypray

I changed the loop to <% @books.each do |book| %> This code is still giving empty row:

@books = Book.find(:all, :joins=>"books inner join authors as a on books.title=a.name", :conditions => ['name LIKE ? ', '%'+params[:author][:name]+'%'])

But the problem is resolved now with this code:

@books = Books.find_by_sql ["SELECT * FROM books WHERE title IN   (SELECT   title FROM authors WHERE name = ?)", params[:author][:name]]

Thanks for your help guys.

Cypray

@books = Book.find(:all, :joins=>"books inner join authors as a on books.title=a.name", :conditions => ['name LIKE ? ', '%'+params[:author][:name]+'%'])

What out for sql injection there.

Fred