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-/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.
On 20 October 2011 11:46, Peter Hicks <peter.hicks-AEjVCzJlOor10XsdtD+oqA@public.gmane.org> 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-/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 20 October 2011 15:53, Peter Hicks <peter.hicks-AEjVCzJlOor10XsdtD+oqA@public.gmane.org> 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-/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.
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-/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.
On 20 October 2011 18:14, Robert Walker <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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-/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 Oct 20, 2011, at 12:23 PM, Colin Law wrote:> On 20 October 2011 18:14, Robert Walker <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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-/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.
> > 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-/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.
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-/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.