I want to build an application that lets me (the end user accessing a web page) design a simple list with columns and add records. E.g., if the end user says I want a table that has a list of songs, he can create a list (title, date, artist, label) and then make another list of, e.g., books on his bookshelf with the necessary columns (title, author, pub_date, shelf). The end user shouldn''t have to do any database programming, schema designs, etc. Just select the columns and types (from a very limited list -- date, text, longtext). Of course in PHP I could just have the program create tables and keep another table of the lists that has each column in a particular list in a row (list_num, name_of_list, column_name, column_type). Another way to do it that doesn''t result in the end user having to create new tables is to have a table that has core columns for ALL lists (id, title, description) and then join that table to columns by type of data on the fly. So, select * from core_list_items where list_num=X and then join it with columns in the Date_items table and the text_items table and then the date_items table. I have to do multiple join statements, sometimes from the same table. ID TITLE DESCRIPTION SONG_NAME (from text table) PUB_DATE (from date table) RECORD_LABEL (also from text table). I have made this work in PHP, but is there a way to do this in rails? I posted this under a different topic, and I''ve seen suggestions that require migrations and other back-end work to make this happen. But an end-user won''t be able to do that, so i want to not have to do any back-end work. -- Posted via http://www.ruby-forum.com/.
Stephen Bartholomew
2006-Jul-08 16:41 UTC
[Rails] How to handle dynamically columned tables in rails
Hey Todd, I''ve been working on something which i''ve briefly discussed here: http://stephenbartholomew.wordpress.com/. This is based on having field definition/values tables with forms to add new fields. I''ve written a mixin that automatically maps these fields to a model. Have a look and let me know if this is something that you''d be interested in. As i say in the article, it''s very much a work in progress and is kinda an investigation of the idea, but hopefully it''ll grow into something quite useful. Cheers, Steve
Chuck Vose
2006-Jul-08 18:45 UTC
[Rails] How to handle dynamically columned tables in rails
On 7/8/06, Todd <toddflaming@mac.com> wrote:> I want to build an application that lets me (the end user accessing a > web page) design a simple list with columns and add records. E.g., if > the end user says I want a table that has a list of songs, he can create > a list (title, date, artist, label) and then make another list of, e.g., > books on his bookshelf with the necessary columns (title, author, > pub_date, shelf). > > The end user shouldn''t have to do any database programming, schema > designs, etc. Just select the columns and types (from a very limited > list -- date, text, longtext). > > Of course in PHP I could just have the program create tables and keep > another table of the lists that has each column in a particular list in > a row (list_num, name_of_list, column_name, column_type). > > Another way to do it that doesn''t result in the end user having to > create new tables is to have a table that has core columns for ALL lists > (id, title, description) and then join that table to columns by type of > data on the fly. > > So, select * from core_list_items where list_num=X and then join it with > columns in the Date_items table and the text_items table and then the > date_items table. I have to do multiple join statements, sometimes from > the same table.Depending on how many possible columns there might be it seems like you might be able to use Single Table Inheritance or a serialized column object. STI would work if the columns will largely be the same. To be truly dynamic you could have a table with columns: id, user_id, column_name, serialized_column_data which would be a hash/array of all the information in their column. Wouldn''t be pretty or particularly space effective but it could work if you really need dynamism. Either way though, I''d much rather trade space concerns for speed since joins can be some of the slowest operations in complex queries. Maybe an even better way would be to have: id, user_id, column_name, column_data (which only contains one entry instead of a hash) and just query for all rows where column_name = ''monkey'' when loading the monkey column for user_id 16. This would allow a multi-column index on user_id, column_name, column_data which would speed things up so much it would be incredible.>From my experience, joins are pretty evil, creating tables is prettyawful too. I would try to have as flat a structure as possible as long as you aren''t expecting millions of users in which case I have no idea what to do. I''ve dealt with 6mil entries, but over that poses some problems that I just cannot fathom yet. Regardless, to answer your actual question, you can certainly do what you used to do in PHP in Rails. You can use an optimistic include when you use find() or just use find_by_sql() and manually create your array with whatever joins you need. Hope that helps, if nothing it was entertaining to think about. Cheers, Chuck Vose
Stephen Bartholomew wrote:> Hey Todd, > > I''ve been working on something which i''ve briefly discussed here: > http://stephenbartholomew.wordpress.com/. > > This is based on having field definition/values tables with forms to add > new fields. I''ve written a mixin that automatically maps these fields to > a model. > > Have a look and let me know if this is something that you''d be > interested in. As i say in the article, it''s very much a work in > progress and is kinda an investigation of the idea, but hopefully it''ll > grow into something quite useful. > > Cheers, > > SteveThis looks interesting, though I can''t tell if it can let the end user add fields or if they can be added so that they apply only to some and not other items in a table. I''d be interested in hearing more. -- Posted via http://www.ruby-forum.com/.
Chuck Vose wrote:> [Single Table Inheritance] would work if the columns will largely be the same. > To be truly dynamic you could have a table with columns: id, user_id, > column_name, serialized_column_data which would be a hash/array of all > the information in their column. Wouldn''t be pretty or particularly > space effective but it could work if you really need dynamism. Either > way though, I''d much rather trade space concerns for speed since joins > can be some of the slowest operations in complex queries. > > Maybe an even better way would be to have: id, user_id, column_name, > column_data (which only contains one entry instead of a hash) and just > query for all rows where column_name = ''monkey'' when loading the > monkey column for user_id 16. This would allow a multi-column index on > user_id, column_name, column_data which would speed things up so much > it would be incredible. > >>From my experience, joins are pretty evil, creating tables is pretty > awful too. I would try to have as flat a structure as possible as long > as you aren''t expecting millions of users in which case I have no idea > what to do. I''ve dealt with 6mil entries, but over that poses some > problems that I just cannot fathom yet.To save real time, I could just have one table with a few placeholder columns... ID NAME DESCRIPTION DATE1 DATE2 TEXT1 TEXT2 TEXT3 Then the STI would have to be tweaked so that any subset of that table corresponding to a defined group would relabel the diplay on say TEXT1 to be "Record label" as defined in a database record describing the different subsets. For those that don''t use some of the info, it would simply be supressed. Problem is it requires me to rewrite STI somewhat and the user has a fixed number of potentially addable fields. Two dates, three texts. There must be a way to do this efficiently. -- Posted via http://www.ruby-forum.com/.
Chuck Vose
2006-Jul-08 19:46 UTC
[Rails] Re: How to handle dynamically columned tables in rails
On 7/8/06, Todd <toddflaming@mac.com> wrote:> Chuck Vose wrote: > > > [Single Table Inheritance] would work if the columns will largely be the same. > > To be truly dynamic you could have a table with columns: id, user_id, > > column_name, serialized_column_data which would be a hash/array of all > > the information in their column. Wouldn''t be pretty or particularly > > space effective but it could work if you really need dynamism. Either > > way though, I''d much rather trade space concerns for speed since joins > > can be some of the slowest operations in complex queries. > > > > Maybe an even better way would be to have: id, user_id, column_name, > > column_data (which only contains one entry instead of a hash) and just > > query for all rows where column_name = ''monkey'' when loading the > > monkey column for user_id 16. This would allow a multi-column index on > > user_id, column_name, column_data which would speed things up so much > > it would be incredible. > > > >>From my experience, joins are pretty evil, creating tables is pretty > > awful too. I would try to have as flat a structure as possible as long > > as you aren''t expecting millions of users in which case I have no idea > > what to do. I''ve dealt with 6mil entries, but over that poses some > > problems that I just cannot fathom yet. > > To save real time, I could just have one table with a few placeholder > columns... > > ID NAME DESCRIPTION DATE1 DATE2 TEXT1 TEXT2 TEXT3 > > Then the STI would have to be tweaked so that any subset of that table > corresponding to a defined group would relabel the diplay on say TEXT1 > to be "Record label" as defined in a database record describing the > different subsets. For those that don''t use some of the info, it would > simply be supressed. > > Problem is it requires me to rewrite STI somewhat and the user has a > fixed number of potentially addable fields. Two dates, three texts. > > There must be a way to do this efficiently.True, STI most likely isn''t the answer, however the other two suggestions seem like they would work nicely. Ta, Chuck Vose
carmen
2006-Jul-08 19:47 UTC
[Rails] Re: How to handle dynamically columned tables in rails
> Problem is it requires me to rewrite STI somewhat and the user has a > fixed number of potentially addable fields. Two dates, three texts. > > There must be a way to do this efficiently.you might want to check out josh susser''s blog, and the wiki pages on polymorphic ''linking'' associations.. im using a table called links, where the type is either another node, or a primitive (textfield, numfield, date, etc)... the schema is like table node ---------- id name table node_links --------------- id node_id link_id link_type table textfields ---------------- id data just one of many solutions (still thinking about switching to activeRDF)
Stephen Bartholomew
2006-Jul-08 20:11 UTC
[Rails] Re: How to handle dynamically columned tables in rails
> This looks interesting, though I can''t tell if it can let the end user > add fields or if they can be added so that they apply only to some and > not other items in a table. I''d be interested in hearing more.The idea is that there will be one field definition and one definition values table. The definition table will state which model the definition applies to. The fields will be added using a syntax something like: Book.add_dynamic_field(''Date Published'',''text'') You would just include somewhere in the system for clients to enter that info. I have a separate section in the client admin to add new fields. The form just has a ''field'' field to enter the name and a select box with types: Line of text, list of values (select one), list of values (select multiple), date - that kind of thing. The entry in the definitions table would then look like this: ------------------------------------------------------------------- id | name | field_type | field_text |applies_to | ------------------------------------------------------------------- 1 date_published text Date Published Book ------------------------------------------------------------------- This is extracted from a client project that only has one dynamic record - so some of this functionality is not yet present. However, a lot of the core features are working so i''ve been scouting to see if there is interest enough to develop it further. If only i could work on this stuff without the pressure of client deadlines :0) Steve
Todd
2006-Jul-08 20:55 UTC
[Rails] Re: Re: How to handle dynamically columned tables in rails
Stephen Bartholomew wrote:>> This looks interesting, though I can''t tell if it can let the end user >> add fields or if they can be added so that they apply only to some and >> not other items in a table. I''d be interested in hearing more. > The idea is that there will be one field definition and one definition > values table. The definition table will state which model the > definition applies to. > > The fields will be added using a syntax something like: > > Book.add_dynamic_field(''Date Published'',''text'') > > You would just include somewhere in the system for clients to enter that > info. I have a separate section in the client admin to add new fields. > The form just has a ''field'' field to enter the name and a select box > with types: Line of text, list of values (select one), list of values > (select multiple), date - that kind of thing. > > The entry in the definitions table would then look like this: > > ------------------------------------------------------------------- > id | name | field_type | field_text |applies_to | > ------------------------------------------------------------------- > 1 date_published text Date Published Book > ------------------------------------------------------------------- > > > This is extracted from a client project that only has one dynamic record > - so some of this functionality is not yet present. However, a lot of > the core features are working so i''ve been scouting to see if there is > interest enough to develop it further. > > If only i could work on this stuff without the pressure of client > deadlines :0) > > SteveYou should keep working on it, and we should swap notes. Sounds like we''re working on the same thing. The difficult thing for me is getting an efficient database architecture and select statements. My select statements for these monsters are ugly. I use a series of left joins (one for each dynamically added field) and then a filter/where clause to knock out the extra records. I know there''s a better way to write it. Here''s an example -- this is probably as clear as mud: SELECT id, title, date, it1.it AS who, it2.it AS what FROM list LEFT JOIN dates ON ( id = dates.list_id ) AND ( dates.cat_id =1 ) LEFT JOIN who AS it1 ON ( id = it1.list_id ) AND ( it1.cat_id =1 ) LEFT JOIN who AS it2 ON ( id = it2.list_id ) AND ( it2.cat_id =2 ) WHERE ( dates.list_id = id AND dates.cat_id =1 ) OR ( it1.list_id = id AND it1.cat_id =1 ) OR ( it2.list_id = id AND it2.cat_id =2 ) LIMIT 0 , 30 -- Posted via http://www.ruby-forum.com/.
Stephen Bartholomew
2006-Jul-08 22:26 UTC
[Rails] Re: Re: How to handle dynamically columned tables in rails
> My select statements for these monsters are ugly.I haven''t actually had to write complex searches for this particular project, but i know the client is going to need reporting soon so i''ll be tackling it then. I made a similar system in PHP a while ago and faced the same issue. What i did in the end was create something that required 2 selects but avoided all the joins. Say we have a Book model with isbn and author as dynamic fields and we want to search all values. First, i search the records dynamic field values: select book_id from book_field_values where (value = ''1234'') group by book_id This gives me a list of the the books that contain ''1234''. I can then select all the books'' main data using this list: select * from books where id in(1,2,3,4,5,6) I''ll let you know how i get on with these reports. This is probably the approach i''m going to try first. Steve