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.
@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?
@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.
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:
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.
@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.
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:
@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]]
@books = Book.find(:all, :joins=>“books inner join authors as a on
books.title=a.name”, :conditions => [‘name LIKE ? ‘,
‘%’+params[:author][:name]+’%’])
@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
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.