How can I union 2 different table datasets?

[George D Elig]

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.

   

[George D Elig]

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.

[Anthony]

If all or most of the fields are the same from one table to the other, why not simply put everything in a single table with "category" field to distinguish cats and dogs (create an index on that field to make it faster to query just cats or just dogs)?

Anthony

[Richard Vézina]

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

--
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 web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[A3]

Maybe you can use:

db.define_table(‘animal’,

Field('name', 'string', length=45),

Field(‘age’, ‘integer’),

Field(‘apt_time’,’datetime’)

and then subclass: 

db.define_table(‘cat’, db.animal)

and

db.define_table(‘dog’, db.animal)

and then query on db.animal ? 

[Anthony]

No, you cannot do that. Above defines three separate DAL models, each of which will have a separate database table -- queries on db.animal would be made against an "animal" table in the database, not against the "cat" and "dog" tables separately.

Anthony

[Richard Vézina]

Forgot to mention... If you for some reason you can't refactor your schema... you can unionize query in web2py like so :

select_cat = db(db.cat.id>0).select()

select_dog = db(db.dog.id>0).select()

select_cat_and_dog = select_cat | select_dog 

Note: You can use & or | they are python binary operator, if I recall used like that combine with web2py query they mimic union (no duplicate) and union all (keep duplicate)...

Richard

--

[Anthony]

On Tuesday, April 25, 2017 at 10:39:53 AM UTC-4, Richard wrote:

Forgot to mention... If you for some reason you can't refactor your schema... you can unionize query in web2py like so :

select_cat = db(db.cat.id>0).select()

select_dog = db(db.dog.id>0).select()

select_cat_and_dog = select_cat | select_dog 

Note: You can use & or | they are python binary operator, if I recall used like that combine with web2py query they mimic union (no duplicate) and union all (keep duplicate)...

That won't help in this case. Above, you are running two separate SQL queries, creating two separate Rows objects, and then combining the Rows objects via Python. That approach cannot be used with SQLFORM.grid, which requires a single query as its input.

Anthony

[Richard Vézina]

Yes, you are right, he can use SQLTABLE or build his grid with helper though...

Richard

--

[Richard Vézina]

Is there a major issue that prevent us to use SQLFORM.grid like crud.select() where you can pass a query instead of a talbe??

Richard

[Richard Vézina]

In case .grid() is a requirement and schema is frozen, the best option would be to make the union in the backend (postgres) with a view (index it as describe in the SO explain for better performance) and create a db models for the view in web2py which it can only be selected as it is a view. I am doing this for complexe query in my app.

Richard

[Alfonso Serra]

You can try:

sql = """
SELECT * FROM table1
UNION SELECT * FROM table2"""

rows = db.executesql(sql, fields = [
   Field("name", "string")
   , Field("age", "integer")
   , Field("apt_time", "datetime")
])

Always that both tables are the same size and types.
Then style your view however you like.

It is true the table structures are not good. Theres no need for 2 tables to store animals. just one with an animal type field to distinguish between cats, dogs, birds, etc.

[George D Elig]

Unfortunately, I cannot combine the two tables. The existing product uses one of them extensively for queue processing and I don't want to possibly break any of that functionality. All I want to do is update the SQLFORM with information from the new table. I can use db.executesql but won't that require me to remove SQLFORM? If so, is there another framework element I can leverage for displaying the rows?

[Anthony]

Does your database support views/materialized views?

Anthony

[Richard Vézina]

You surely can create a view, if you can, you can mimic one with db.executesql() where you do your union, and then construct you grid with web2py html HELPERS() or other means... Sadly you can use SQLFORM.grid() as previously said but it not a big deal as you avoid convulated form processing to maintain in sync an unionize entity which would be a pretty bad idea as if you can't improve schema this probably mean your app is not the only system accessing this database which may lead to an off sync unionize entity...

Richard

[George]

Postgres supports views and stored procedures. Assuming I can create a query statement to call a view or stored procedure, can't that query be passed to SQLFORM?

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/lnjK7nibOqw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Anthony]

Create a view or materialized view in the db, and then just create a DAL model based on that view (turn off migrations for that model so the DAL does not attempt to create a table for it). From that point, you can use that model for queries via the DAL.

Anthony

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[George D Elig]

I created a view as you suggested. However, when I ran the view in SQLForm.grid() I received a web2py error that the 'id' did not exist (which is doesn't). As a hack to get around this, I manually created a column named 'id' and hard-coded the value to '1'  because my grid is display only and no actions can be performed. This required an alias in my SQL, so I gave it an alias exactly matching the name of the view. That was more creative than I wanted to get but it works.

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/lnjK7nibOqw/unsubscribe.

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Richard Vézina]

Good news you workaroud!

Richard

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.