0

I have 2 tables: 'Actor' & 'Role'. Each table has a column named 'ethnicity'. An Actor can only have 1 ethnicity, but a Role can have 1 or more ethnicities.

So for example, let's say I want to find all the roles that match Actor1's ethnicity below:
Actor1 - ethnicity: 'Asian'
Role1 - ethnicity: 'Asian'
Role2 - ethnicity: ['Asian','Caucasian']

This query:

Role.where{(ethnicity.eq my{actor.ethnicity})}

Will pull up Role1, but not Role2. My question is how do I construct a query that will pull up Role1 as well as Role2 in this example?

I've tried doing:

Role.where{(ethnicity.in my{actor.ethnicity})}

But that doesn't seem to work.

How to do this?

user1341808
  • 249
  • 4
  • 7
  • 18

1 Answers1

0

The first question to ask is how are you storing the ethnicity array on the Role model? Is it a serialized column? If it is you may be better of going for a has_many relationship if you are going to be frequently asking about the ethnicity.

It seems like your problem is that you are querying a string against a value that could potentially be an array or a string. Depending on your answer to the question above the solution may be different.

One option would be to invert your

Role.where{(ethnicity.in my{actor.ethnicity})}

to become

Role.where{(actor.ethnicity.in my{ethnicity})}

since the array here is the Role's ethnicity, not the actor's ethnicity. Now I am not a 100% familiar with squeel but this is my answer based on the basic logic involved.

Dimitar Koparov
  • 51
  • 1
  • 3
  • ethnicity is a serialized column on the Role model. I'm not going to be using this query a lot so I don't think I'll be needing a has many relationship. I tried inverting the query but it didn't work. Any other thoughts? – user1341808 Mar 24 '14 at 15:31
  • So, the problem with it being a serialized column, and especially one that's occasionally string and occasionally array is that you essentially can't use SQL logic on it, which is what squeel is based on. ((de)serialization happens in Rails, as far as SQL is concerned a serialized column is just a blob). The best you can do is try to achieve this with just text matching against the YAML, although that may be risky. Something like `Role.where{ethnicity =~ '%- Asian\n%'}` but that is honestly a bit of a hack – Dimitar Koparov Mar 25 '14 at 14:46