Doing Subselect query in Rails... Syntax problem


#1

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.


#2

On Dec 9, 11:06 am, Jay M. removed_email_address@domain.invalid 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


#3

Jeff C. wrote:

On Dec 9, 11:06�am, Jay M. removed_email_address@domain.invalid 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


#4

Jeff C. wrote:

On Dec 9, 11:52�am, Jay M. removed_email_address@domain.invalid 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


#5

On Dec 9, 1:47 pm, Jay M. removed_email_address@domain.invalid wrote:

Jeff C. 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


#6

Jeff C. wrote:

On Dec 9, 1:47�pm, Jay M. removed_email_address@domain.invalid wrote:

Jeff C. 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
authorse[0m
e[4;35;1mSQL (0.015000)e[0m e[0mSHOW TABLESe[0m

NoMethodError (undefined method `find_by_name’ for #Class:0x3da796c):

Cypray


#7

Hi,
Actually what’s your requirement? If you tell your requirement
clearly then the solution can be found.


#8

@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 M. wrote:

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.


#9

On Dec 9, 11:52 am, Jay M. removed_email_address@domain.invalid 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


#10

Jay M. wrote:

Priya B. 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


#11

On 10 Dec 2008, at 06:55, Priya D. wrote:

@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


#12

Priya B. 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