I have a table named logs. it has sender,receivers fields. my sample data from my table id sender receivers 1 1 2,3 2 2 3 3 1 3,2 when i execute the below query "select * FROM logs where sender = 1 and receiver IN (2)" i get only one record .the id is 1. In my table the 3rd row also has 2 in the receivers field. but my query gives me only one receord. if my self done wrong, kindly give any ideas to solve my issue Thanks for your time. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Newb Newb wrote:> > > I have a table named logs. > > it has sender,receivers fields. > > my sample data from my table > > id sender receivers > 1 1 2,3 > 2 2 3 > 3 1 3,2 > >Very bad. Don''t try to store multiple values in one field, or you''ll run into exactly the kind of problems you''re describing below. You probably want to read up on has_and_belongs_to_many .> when i execute the below query > > "select * FROM logs where sender = 1 and receiver IN (2)" > > i get only one record .the id is 1.I''m surprised that you even get that record.> > In my table the 3rd row also has 2 in the receivers field. > > but my query gives me only one receord. > > if my self done wrong, kindly give any ideas to solve my issue >Fix your data model as I suggested above.> Thanks for your time.Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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.
Marnen Laibow-Koser wrote:> Newb Newb wrote: >> >>Pls let me know Any other way i can try with? -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
You really should redesign this, but anyway
"select * FROM logs where sender = 1 and receivers LIKE
''%2%''" works if
the receivers have one digit ID''s.
If not, you will probably have to do
logs = Log.find_all_by_sender(1)
logs_filtered = []
logs.each do |log|
logs_filtered << log
if(log.receivers.split('','').include?(''2''))
end
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
Sharagoz -- wrote:> You really should redesign this, but anyway > > "select * FROM logs where sender = 1 and receivers LIKE ''%2%''" works if > the receivers have one digit ID''s. > > If not, you will probably have to do > logs = Log.find_all_by_sender(1) > logs_filtered = [] > logs.each do |log| > logs_filtered << log if(log.receivers.split('','').include?(''2'')) > endThanks for ur time. if i use like ''%2%'',it also fetches 25,241 etc.. what can i do in this case -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
I already suggested a solution for that scenario in my previous post. If you are looking for a single SQL query that solves the problem, then I cant help you. As far as I know there no SQL string operation that can solve the problem. If you stored the IDs differently by marking the beginning and end of the digit, then a "LIKE" query could work. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Newb Newb wrote:> Sharagoz -- wrote: >> You really should redesign this, but anyway >> >> "select * FROM logs where sender = 1 and receivers LIKE ''%2%''" works if >> the receivers have one digit ID''s. >> >> If not, you will probably have to do >> logs = Log.find_all_by_sender(1) >> logs_filtered = [] >> logs.each do |log| >> logs_filtered << log if(log.receivers.split('','').include?(''2'')) >> end > > > Thanks for ur time. > if i use like ''%2%'',it also fetches 25,241 etc.. > what can i do in this caseRedesign your data model. Stop trying to avoid it -- it''s the right thing to do. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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.
Marnen Laibow-Koser wrote:> Newb Newb wrote: >> Sharagoz -- wrote: >>> You really should redesign this, but anyway >>> >>> "select * FROM logs where sender = 1 and receivers LIKE ''%2%''" works if >>> the receivers have one digit ID''s. >>> >>> If not, you will probably have to do >>> logs = Log.find_all_by_sender(1) >>> logs_filtered = [] >>> logs.each do |log| >>> logs_filtered << log if(log.receivers.split('','').include?(''2'')) >>> end >> >> >> Thanks for ur time. >> if i use like ''%2%'',it also fetches 25,241 etc.. >> what can i do in this case > > Redesign your data model. Stop trying to avoid it -- it''s the right > thing to do. > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org^^ what Marnen says. You can do what you''re trying to using SQL but it''s not a good idea. If you get any sort of volume in your table and you are running lots of ''like'' type queries you''ll have a performance problem. At the point you''ll refactor to HABTM anyway so you might as well get it right from the outset. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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.
On Wed, Feb 3, 2010 at 1:04 AM, Newb Newb <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> > > I have a table named logs. > > it has sender,receivers fields. > > my sample data from my table > > id sender receivers > 1 1 2,3 > 2 2 3 > 3 1 3,2 > >Yes, I agree with Marnen that you should fix your data model. It simply would make life easier going forward with your application development. Good luck, -Conrad> > when i execute the below query > > "select * FROM logs where sender = 1 and receiver IN (2)" > > i get only one record .the id is 1. > > In my table the 3rd row also has 2 in the receivers field. > > but my query gives me only one receord. > > if my self done wrong, kindly give any ideas to solve my issue > > Thanks for your time. > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
I agree with the rest of the posts. If your DB grows you''re going to be in for a lot of work later on. It would be better to do it right from the start. If you''re set however on leaving it as it is you could run several LIKE statements with OR (field like ... or field like ... ) or if your DB accepts selects with regular expressions that might help you. Here is a link that might help: http://www.java2s.com/Code/SQL/Select-Clause/Whereclauseregularexpressions.htm On Feb 3, 3:27 pm, Conrad Taylor <conra...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Wed, Feb 3, 2010 at 1:04 AM, Newb Newb <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: > > > I have a table named logs. > > > it has sender,receivers fields. > > > my sample data from my table > > > id sender receivers > > 1 1 2,3 > > 2 2 3 > > 3 1 3,2 > > Yes, I agree with Marnen that you should fix your data model. It simply > would make life easier going forward with your application development. > > Good luck, > > -Conrad > > > > > when i execute the below query > > > "select * FROM logs where sender = 1 and receiver IN (2)" > > > i get only one record .the id is 1. > > > In my table the 3rd row also has 2 in the receivers field. > > > but my query gives me only one receord. > > > if my self done wrong, kindly give any ideas to solve my issue > > > Thanks for your time. > > -- > > Posted viahttp://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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > To unsubscribe from this group, send email to > > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > > . > > For more options, visit this group at > >http://groups.google.com/group/rubyonrails-talk?hl=en.-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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.