Hello George, You don't need to do that (which is called materialized view and it's not suppose to involve the creation a new table that you need to maintain in sync : http://stackoverflow.com/questions/3986366/how-to-create-materialized-views-in-sql-server)... In MSSQL you will have storeprocedure that will populate the materialized view, in postgres it calls a function, you would use a trigger to make sure you catch all the even that may occur over both of your entity to populate the third one...
BUT, don't do that... If you step back and you take a look at you db schema, there is place to improvement... You may consider to merge cat an dog into a single animal entity and create an animal_type entity that you reference to define for a given record if the animal is a cat or a dog... Like so, you have a normalized schema and you don't repeat yourself having 2 tables with the exact same field but with diffrents names. You solve your initial issue without all the hassle I discribe above... Good luck Richard On Tue, Apr 25, 2017 at 7:35 AM, George D Elig <[email protected]> wrote: > I have a working solution for this. > > I created a third table, which reads the rows from each cat and dog table > and then inserts the values as new rows into the new table. That table is > then passed into SQLFORM.grid(). > > It's not pretty, but it works while I continue to look for a more elegant > solution. > > > On Saturday, April 22, 2017 at 4:51:21 PM UTC-4, George D Elig wrote: >> >> I have the following tables defined and would like to display rows of >> cats, followed by rows of dogs >> >> db.define_table(‘cat’, >> >> Field('name', 'string', length=45), >> >> Field(‘age’, ‘integer’), >> >> Field(‘apt_time’,’datetime’) >> >> ) >> >> db.define_table(‘dog’, >> >> Field('name', 'string', length=45), >> >> Field(‘age’, ‘integer’), >> >> Field(‘apt_time’,’datetime’) >> >> ) >> >> >> #Controller >> >> query = ((db.cat.age == 3) | (db.dog.age == 3)) >> >> union_rows = db(query).select() >> >> print union_rows >> >> >> The returned value is a single row object, containing an entry for each >> table. >> >> <Row {‘cat’: {'name': ‘FLUFFY’, 'age': 3L, 'apt_time': >> datetime.datetime(2017, 4, 22, 16, 22, 10), }, ‘dog’: {'name': ‘SPOT’, >> 'age': 3L, 'apt_time': datetime.datetime(2017, 4, 22, 16, 22, 10), }}> >> >> The output from SQLFORM.grid is below >> >> *FLUFFY 3 2017-04-22 16:22:10 SPOT 3 2017-04-22 >> 16:22:10* >> >> >> >> What I need are two row objects, displayed in the SQLFORM as two rows. >> *FLUFFY 3 2017-04-22 16:22:10* >> *SPOT 3 2017-04-22 16:22:10* >> >> I attempted to append row objects but that raises incompatible types, >> which I believe is because of the different table names. >> >> >> >> > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > You received this message because you are subscribed to the Google Groups > "web2py-users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
