Forum: Ruby on Rails Doing Subselect query in Rails... Syntax problem

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Cad24cf25cdf0bade2e508d30dd3cadd?d=identicon&s=25 Jay Mark (cypray)
on 2008-12-09 18:06
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.
8217faf2bfdfa7daf10135d41ddd421e?d=identicon&s=25 Jeff Cohen (jeff)
on 2008-12-09 18:17
(Received via mailing list)
On Dec 9, 11:06 am, Jay Mark <rails-mailing-l...@andreas-s.net> 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
Cad24cf25cdf0bade2e508d30dd3cadd?d=identicon&s=25 Jay Mark (cypray)
on 2008-12-09 18:52
Jeff Cohen wrote:
> On Dec 9, 11:06�am, Jay Mark <rails-mailing-l...@andreas-s.net> 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
8217faf2bfdfa7daf10135d41ddd421e?d=identicon&s=25 Jeff Cohen (jeff)
on 2008-12-09 19:56
(Received via mailing list)
On Dec 9, 11:52 am, Jay Mark <rails-mailing-l...@andreas-s.net> 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
Cad24cf25cdf0bade2e508d30dd3cadd?d=identicon&s=25 Jay Mark (cypray)
on 2008-12-09 20:47
Jeff Cohen wrote:
> On Dec 9, 11:52�am, Jay Mark <rails-mailing-l...@andreas-s.net> 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
8217faf2bfdfa7daf10135d41ddd421e?d=identicon&s=25 Jeff Cohen (jeff)
on 2008-12-09 22:04
(Received via mailing list)
On Dec 9, 1:47 pm, Jay Mark <rails-mailing-l...@andreas-s.net> 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
Cad24cf25cdf0bade2e508d30dd3cadd?d=identicon&s=25 Jay Mark (cypray)
on 2008-12-09 23:41
Jeff Cohen wrote:
> On Dec 9, 1:47�pm, Jay Mark <rails-mailing-l...@andreas-s.net> 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"}}
  Author Columns (0.047000)   SHOW FIELDS FROM
`authors`
  SQL (0.015000)   SHOW TABLES


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

Cypray
0348be81e22f24afdaa6e4f137aa9dfc?d=identicon&s=25 Priya Buvan (priyadharsini)
on 2008-12-10 07:49
Hi,
   Actually what's your requirement? If you tell your requirement
clearly then the solution can be found.
0348be81e22f24afdaa6e4f137aa9dfc?d=identicon&s=25 Priya Buvan (priyadharsini)
on 2008-12-10 07:55
@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:
> 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.
Cad24cf25cdf0bade2e508d30dd3cadd?d=identicon&s=25 Jay Mark (cypray)
on 2008-12-10 14:20
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
Cad24cf25cdf0bade2e508d30dd3cadd?d=identicon&s=25 Jay Mark (cypray)
on 2008-12-10 15:35
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
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-12-10 15:43
(Received via mailing list)
On 10 Dec 2008, at 06:55, Priya Dharsini 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
This topic is locked and can not be replied to.