All,

I have some data that could be a combination of around 30 values. Typical
values could be 'TB', 'TF', 'D', 'U', '-D', 'OP', 'RM', or nil.

Ideally, I'd use the SET data type in PostgreSQL, but ActiveRecord doesn't
appear to support[1] this. More specifically, I can run a migration on my
development database, but db/schema.rb contains a comment about an
unsupported data-type.

I've considered creating 30 columns to model this, however I'm not convinced
that's efficient. I've also thought about separating each value with a :
and storing this - for example, ":TF:-D:" - whilst I can query for "WHERE
foo LIKE '%:D:%'", that probably isn't indexable as it's a text search.

Can anyone help me to come up with an efficient way to do this in Rails
3.0.10?

Kind regards,


Peter


[1] http://ionrails.com/2010/06/16/set-data-type-mysql-activerecord/

--
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@googlegroups.com.
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.

Search Discussions

  • Colin Law at Oct 20, 2011 at 12:25 pm

    On 20 October 2011 11:46, Peter Hicks wrote:
    All,

    I have some data that could be a combination of around 30 values.  Typical
    values could be 'TB', 'TF', 'D', 'U', '-D', 'OP', 'RM', or nil.

    Ideally, I'd use the SET data type in PostgreSQL, but ActiveRecord doesn't
    appear to support[1] this.  More specifically, I can run a migration on my
    development database, but db/schema.rb contains a comment about an
    unsupported data-type.

    I've considered creating 30 columns to model this, however I'm not convinced
    that's efficient.  I've also thought about separating each value with a :
    and storing this - for example, ":TF:-D:" - whilst I can query for "WHERE
    foo LIKE '%:D:%'", that probably isn't indexable as it's a text search.

    Can anyone help me to come up with an efficient way to do this in Rails
    3.0.10?
    If the attributes are effectively independent, so that a record may be
    a TB or it may not, and it may be a TF or it may not, and so on, then
    use 30 (or whatever) boolean columns as that is the simplest way to do
    it and it most closely maps to your requirement. I don't see in what
    way this would be inefficient. It would surely be the most efficient
    solution when it comes to finding records of particular types.

    In any case you should not worry about efficiency at this stage.
    Start with the most straight forward design and *if* performance
    becomes an issue then optimise it later. I can virtually guarantee
    that the bottleneck in a app will not be where you expect to be at the
    outset, so starting with a more complex (and potentially buggy)
    solution in order to work around perceived bottlenecks is rarely a
    good idea.

    Colin

    --
    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@googlegroups.com.
    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.
  • Colin Law at Oct 20, 2011 at 3:16 pm

    On 20 October 2011 15:53, Peter Hicks wrote:
    On Thu, Oct 20, 2011 at 01:24:24PM +0100, Colin Law wrote:

    In any case you should not worry about efficiency at this stage.
    Start with the most straight forward design and *if* performance
    becomes an issue then optimise it later.  I can virtually guarantee
    that the bottleneck in a app will not be where you expect to be at the
    outset, so starting with a more complex (and potentially buggy)
    solution in order to work around perceived bottlenecks is rarely a
    good idea.
    Very wise words which only make sense when somebody else utters them.

    Thanks, Colin - I'll give this a go!
    Another solution might be to have a table of options (or whatever word
    is appropriate), where option.name is 'TB', 'TF' and so on. Then you
    can have a HABTM relationship between the tables and you could get all
    the options for your main object using
    @my_object.options
    which would give you (effectively) an array of the options.

    This would have the advantage of flexibility in that you can add
    further options if you need to. Also it gives you somewhere to store
    further information about the option. It all depends on the details
    of the problem you are trying to solve.

    Colin

    --
    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@googlegroups.com.
    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.
  • Robert Walker at Oct 20, 2011 at 5:14 pm

    Peter Hicks wrote in post #1027558:
    I have some data that could be a combination of around 30 values.
    Typical
    values could be 'TB', 'TF', 'D', 'U', '-D', 'OP', 'RM', or nil.

    Ideally, I'd use the SET data type in PostgreSQL, but ActiveRecord
    doesn't
    appear to support[1] this. More specifically, I can run a migration on
    my
    development database, but db/schema.rb contains a comment about an
    unsupported data-type.

    I've considered creating 30 columns to model this, however I'm not
    convinced
    that's efficient. I've also thought about separating each value with a
    :
    and storing this - for example, ":TF:-D:" - whilst I can query for
    "WHERE
    foo LIKE '%:D:%'", that probably isn't indexable as it's a text search.

    Can anyone help me to come up with an efficient way to do this in Rails
    3.0.10?
    Yes. store this as a bit-mask. Then your query is as efficient using
    integer comparison. Map your labels to the bit-mask.

    Example:

    Constants
    -----
    TB => 1
    TF => (1 << 1)
    D => (1 << 2)
    U => (1 << 3)
    MINUS_D => (1 << 4)

    select * from bar where foo = (TB | D);
    or
    select * from bar where foo = 5;

    --
    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@googlegroups.com.
    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.
  • Colin Law at Oct 20, 2011 at 7:24 pm

    On 20 October 2011 18:14, Robert Walker wrote:
    Peter Hicks wrote in post #1027558:
    I have some data that could be a combination of around 30 values.
    Typical
    values could be 'TB', 'TF', 'D', 'U', '-D', 'OP', 'RM', or nil.

    Ideally, I'd use the SET data type in PostgreSQL, but ActiveRecord
    doesn't
    appear to support[1] this.  More specifically, I can run a migration on
    my
    development database, but db/schema.rb contains a comment about an
    unsupported data-type.

    I've considered creating 30 columns to model this, however I'm not
    convinced
    that's efficient.  I've also thought about separating each value with a
    :
    and storing this - for example, ":TF:-D:" - whilst I can query for
    "WHERE
    foo LIKE '%:D:%'", that probably isn't indexable as it's a text search.

    Can anyone help me to come up with an efficient way to do this in Rails
    3.0.10?
    Yes. store this as a bit-mask. Then your query is as efficient using
    integer comparison. Map your labels to the bit-mask.

    Example:

    Constants
    -----
    TB => 1
    TF => (1 << 1)
    D => (1 << 2)
    U => (1 << 3)
    MINUS_D => (1 << 4)

    select * from bar where foo = (TB | D);
    or
    select * from bar where foo = 5;
    That works of course, but then you may have the complication of adding
    all the setters and accessors so you can say things like
    widget.tb = true
    do_something if widget.tb
    and so on.
    Once again it depends upon the details of the OPs requirement.

    Colin

    --
    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@googlegroups.com.
    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.
  • Philip Hallstrom at Oct 20, 2011 at 7:47 pm

    On Oct 20, 2011, at 12:23 PM, Colin Law wrote:
    On 20 October 2011 18:14, Robert Walker wrote:
    Peter Hicks wrote in post #1027558:
    I have some data that could be a combination of around 30 values.
    Typical
    values could be 'TB', 'TF', 'D', 'U', '-D', 'OP', 'RM', or nil.

    Ideally, I'd use the SET data type in PostgreSQL, but ActiveRecord
    doesn't
    appear to support[1] this. More specifically, I can run a migration on
    my
    development database, but db/schema.rb contains a comment about an
    unsupported data-type.

    I've considered creating 30 columns to model this, however I'm not
    convinced
    that's efficient. I've also thought about separating each value with a
    :
    and storing this - for example, ":TF:-D:" - whilst I can query for
    "WHERE
    foo LIKE '%:D:%'", that probably isn't indexable as it's a text search.

    Can anyone help me to come up with an efficient way to do this in Rails
    3.0.10?
    Yes. store this as a bit-mask. Then your query is as efficient using
    integer comparison. Map your labels to the bit-mask.

    Example:

    Constants
    -----
    TB => 1
    TF => (1 << 1)
    D => (1 << 2)
    U => (1 << 3)
    MINUS_D => (1 << 4)

    select * from bar where foo = (TB | D);
    or
    select * from bar where foo = 5;
    That works of course, but then you may have the complication of adding
    all the setters and accessors so you can say things like
    widget.tb = true
    do_something if widget.tb
    and so on.
    There are plugins that will do all of this for you... search for something like "rails bit field plugin"

    -philip

    --
    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@googlegroups.com.
    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.
  • Owain at Oct 21, 2011 at 11:37 am
    There are plugins that will do all of this for you... search for something like "rails bit field plugin"
    I use has bit field which is here: https://github.com/pjb3/has-bit-field

    --
    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@googlegroups.com.
    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.
  • Robert Walker at Oct 21, 2011 at 1:05 pm

    Colin Law wrote in post #1027667:
    That works of course, but then you may have the complication of adding
    all the setters and accessors so you can say things like
    widget.tb = true
    do_something if widget.tb
    and so on.
    Once again it depends upon the details of the OPs requirement.
    Yep. I didn't say it might be a simpler solution, but the question was
    "Can anyone help me to come up with an efficient way to do this in Rails
    3.0.10?" That the question I was answering anyway. I think we can all
    agree that a bit-field would be the most efficient. Integer comparison,
    especially if the field is indexed should provide excellent query
    efficiency.

    But, as others have mentioned, there are gems to make this more complex
    solution easier to manage.

    --
    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@googlegroups.com.
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouprubyonrails-talk @
categoriesrubyonrails
postedOct 20, '11 at 10:46a
activeOct 21, '11 at 1:05p
posts8
users5
websiterubyonrails.org
irc#RubyOnRails

People

Translate

site design / logo © 2021 Grokbase