thr3ads.net - Rails - Doing Subselect query in Rails... Syntax problem [Dec 2008]

If this information is useful, please help other people find it:
Share via:

Jay Mark

2008-Dec-09 17:06 UTC

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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jeff

2008-Dec-09 17:16 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

On Dec 9, 11:06 am, Jay Mark
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
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.
> --
> 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jay Mark

2008-Dec-09 17:52 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

Jeff Cohen wrote:> On Dec 9, 11:06�am, Jay Mark
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jeff

2008-Dec-09 18:55 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

On Dec 9, 11:52 am, Jay Mark
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
> 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.
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jay Mark

2008-Dec-09 19:47 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

Jeff Cohen wrote:> On Dec 9, 11:52�am, Jay Mark
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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





-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jeff

2008-Dec-09 21:03 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

On Dec 9, 1:47 pm, Jay Mark
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jay Mark

2008-Dec-09 22:41 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

Jeff Cohen wrote:> On Dec 9, 1:47�pm, Jay Mark
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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"}}
   [4;36;1mAuthor Columns (0.047000) [0m    [0;1mSHOW FIELDS FROM 
`authors` [0m
   [4;35;1mSQL (0.015000) [0m    [0mSHOW TABLES [0m


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

Cypray
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Priya Dharsini

2008-Dec-10 06:49 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

Hi,
   Actually what''s your requirement? If you tell your requirement 
clearly then the solution can be found.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Priya Dharsini

2008-Dec-10 06:55 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

@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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jay Mark

2008-Dec-10 13:20 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jay Mark

2008-Dec-10 14:35 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

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




-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Frederick Cheung

2008-Dec-10 14:43 UTC

head link

Re: Doing Subselect query in Rails... Syntax problem

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>
>
>
> 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.
>
> -- 
> Posted via http://www.ruby-forum.com/.
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rails - Dec 2008 - Doing Subselect query in Rails... Syntax problem

Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem

Re: Doing Subselect query in Rails... Syntax problem