Query help

Rails
1

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.

2

Newb N. 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
[email protected]

3

Marnen Laibow-Koser wrote:

Newb N. wrote:

Pls let me know Any other way i can try with?

4

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

5

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

6

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.

7

Newb N. 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
[email protected]

8

Marnen Laibow-Koser wrote:

Newb N. 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
[email protected]

^^ 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.

9

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:

10

On Wed, Feb 3, 2010 at 1:04 AM, Newb N. [email protected] wrote:

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