Forum: Ruby on Rails Active record Query

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.
Nick H. (Guest)
on 2009-03-30 18:35
In my controller i am doing these querys.

Query 1

astock = MyStock.find(:first,:select => "Symbol", :conditions
=>["user_id = ?", session[:idnum]])

im wanting to find all the rows from my stock with that particular id.
this works fine.

i then do a second query


Query 2

@stocks_load = Stocks.find(:all, :conditions => ["Symbol =
?",astock.Symbol] )

this also works fine unless the first query is find(:all then i get
symbol as a undefined method error.

Do i need some sort of loop that does the query comparsion in row found
from the first query?

Any help or suggestions would be great!

Regards

Nick
Colin L. (Guest)
on 2009-03-30 18:45
(Received via mailing list)
2009/3/30 Nick H. <removed_email_address@domain.invalid>

>
> In my controller i am doing these querys.
>
> Query 1
>
> astock = MyStock.find(:first,:select => "Symbol", :conditions
> =>["user_id = ?", session[:idnum]])
>
> im wanting to find all the rows from my stock with that particular id.
> this works fine.


I think this will only find the first one, not all


> this also works fine unless the first query is find(:all then i get
> symbol as a undefined method error.
>

If you mean that it does not work if you do query 2 first, it may be
because
astock has not been found yet.  If you mean it does not work if the
query 1
is find(:all,..) then astock will be a collection so astock.Symbol is
not
valid.  astock[0].Symbol might be, for example.
Nick H. (Guest)
on 2009-03-30 18:59
>
> If you mean that it does not work if you do query 2 first, it may be
> because
> astock has not been found yet.  If you mean it does not work if the
> query 1
> is find(:all,..) then astock will be a collection so astock.Symbol is
> not
> valid.  astock[0].Symbol might be, for example.


thanks for quick reply

Yeah astock[0].symbol does work. I need it to compare the collection. so
each row in the stored array

is this possible?
Colin L. (Guest)
on 2009-03-30 19:07
(Received via mailing list)
You can use astock.each or other methods to iterate the collection, but
I
think maybe I do not understand what you are trying to achieve.

2009/3/30 Nick H. <removed_email_address@domain.invalid>
Nick H. (Guest)
on 2009-03-30 19:20
Colin L. wrote:
> You can use astock.each or other methods to iterate the collection, but
> I
> think maybe I do not understand what you are trying to achieve.
>
> 2009/3/30 Nick H. <removed_email_address@domain.invalid>

In the first query i am getting all the symbols for stocks where the
user id is for example 1 in table mystocks

now i have all the symbols that user id 1 has

then i am doing a second query on another table called stocks to compare
the symbols found in the first query to the symbols in stocks then
outpuuting the result
Colin L. (Guest)
on 2009-03-30 19:33
(Received via mailing list)
Forget about how you have done it so far for the moment.  Is it possible
to
write out in one sentence what you want you want @stocks_load to
contain?
Find all the stocks where .....

2009/3/30 Nick H. <removed_email_address@domain.invalid>
Nick H. (Guest)
on 2009-03-30 19:41
Colin L. wrote:
> Forget about how you have done it so far for the moment.  Is it possible
> to
> write out in one sentence what you want you want @stocks_load to
> contain?
> Find all the stocks where .....
>
> 2009/3/30 Nick H. <removed_email_address@domain.invalid>

find all stocks from stocks table where user id 1 has them stocks in
there mystocks table
Colin L. (Guest)
on 2009-03-30 19:46
(Received via mailing list)
Is there any relationship between the Stocks table and the MyStocks
table?

2009/3/30 Nick H. <removed_email_address@domain.invalid>
Nick H. (Guest)
on 2009-03-30 19:51
Colin L. wrote:
> Is there any relationship between the Stocks table and the MyStocks
> table?
>
> 2009/3/30 Nick H. <removed_email_address@domain.invalid>

yeah user_id in mystocks relates to the id in a table called users
Colin L. (Guest)
on 2009-03-30 20:00
(Received via mailing list)
Sorry, I have lost track of the tables.  Is there a Stocks table and a
Mystocks table and a Users table, or just a Stocks table and a Users
table?

2009/3/30 Nick H. <removed_email_address@domain.invalid>
Nick H. (Guest)
on 2009-03-30 20:03
Colin L. wrote:
> Sorry, I have lost track of the tables.  Is there a Stocks table and a
> Mystocks table and a Users table, or just a Stocks table and a Users
> table?
>
> 2009/3/30 Nick H. <removed_email_address@domain.invalid>


My stocks table which stores the stocks a particular user has, stocks
table which contains the latest stock data and a users table which
contains the user id etc. how ever in my querys i dont involve the users
table as i just pull the user id from the session
Colin L. (Guest)
on 2009-03-30 20:24
(Received via mailing list)
I know it is not the question you want the answer to but I feel there is
something not right about the database organisation. Warning bells ring
when
you say that you have stocks in two tables.  If changing the database
organisation is an option then I would suggest considering whether you
would
be better to have a 'has and belongs to many' relationship between Users
and
Stocks.  If the only thing in Mystocks is the symbol., or other
information
that is also in the Stocks table then that table becomes a UserStocks
table
containing only the ids in the conventional HABTM manner. If there is
more
than just the symbol then the UserStocks table can contain the extra
fields.

The advantage will hopefully be that rails, knowing about the
relationships
between the tables, will be able to do more for you.  For example to
access
the stocks belonging to a user you just have to use user.stocks rather
than
the find in your original query 1.

2009/3/30 Nick H. <removed_email_address@domain.invalid>
Nick H. (Guest)
on 2009-03-30 22:08
I feared you might say that! lol i think i might to go back to the
drawing!

Thanks again for your time and help!

Much Appreciated

Nick
Ar C. (Guest)
on 2009-03-30 22:32
Colin L. wrote:
> I know it is not the question you want the answer to but I feel there is
> something not right about the database organisation. Warning bells ring
> when
> you say that you have stocks in two tables.

Bells of all sorts...
Users may have multiple "lots" of the same stock purchased at different
times and prices, which affects the decision to hold or sell specific
"lots"...

User
  has_many lots

Lot
  belongs_to user
  belongs_to stock
  # simple user_id, stock_id is insufficient? may also want to include
purchase date, quantity, purchase price, minimum hold period, blah,
blah, blah

Stock
  has_many lots

Fun, fun, fun...
This topic is locked and can not be replied to.