i'm preparing build large multi-table backed site first time , hoping ruby community point me in right direction on how go building this.
about app: have several large tables (>200k+ rows , 30 columns each table). columns between each tables not match, , if try , expand long wide, , merge 1 table, total columns balloon out of control. creating 1 master table not possibility in mind. of tables's rows (observations) connected through unique_id. looking do, , hoping advise community, how can create multiple search criteria can pull various tables.
here scaled down example of i'm looking accomplish. in example, have table_1 , table_2 (both tables samples below). , let's users searches name:tim , subject: english, in order see tim's english grade: 78.
i've had no problems working 1 table, have never worked multiple tables connected through 1 common identifier. again, remember scaled down version. actual tables large , cannot create 1 tables without columns getting large.
how go insuring filters able a: hold selected variable, while b: users searches other tables? hope i'm making myself clear here. thank in advance pointers or advice point me in right direction.
table_1 unique_id name age sex 1 tim 16 male 2 chris 15 male 3 brad 18 male 4 mary 20 female table_2 unique_id subject grade 1 math 88 1 english 78 1 history 98 2 math 65 2 english 72 2 history 84
you may define view create large table.
example code (i use sequel , sqlite in example. ar offers similar features):
#prepare example require 'sequel' db = sequel.sqlite() sequel.extension :pretty_table db.create_table(:tab1){ fixnum :unique_id string :name fixnum :age string :sex } db[:tab1].insert(1, 'tim', 16, 'male') db[:tab1].insert(2, 'chris', 15, 'male') db[:tab1].insert(3, 'brad', 18, 'male') db[:tab1].insert(4, 'mary', 20, 'female') db.create_table(:tab2){ fixnum :unique_id string :subject fixnum :grade } db[:tab2].insert(1, 'math', 88) db[:tab2].insert(1, 'english', 78) db[:tab2].insert(1, 'history', 98) db[:tab2].insert(2, 'math', 65) db[:tab2].insert(2, 'english', 72) db[:tab2].insert(2, 'history', 84) #show how use view select on 2 tables sequel::prettytable.print( db[:tab1].inner_join(:tab2, :unique_id => :unique_id).filter(:name => 'tim', :subject => 'math') ) the result:
+---+-----+----+----+-------+---------+ |age|grade|name|sex |subject|unique_id| +---+-----+----+----+-------+---------+ | 16| 88|tim |male|math | 1| +---+-----+----+----+-------+---------+ one problem big number of tables. not recommend create 1 big view data, nor create many views. can define join data need selection.
Comments
Post a Comment