Database table from SQL query

[morningovermidnight]

Ok, here goes. I would like to add another table to my database and
link it to an already exsisting table in my database. Lets say the
existing table is table-E, and the table to add is table-A. Usually,
adding a table with a foreign key reference to another table is no
problem. But for this situation. I do not want every record from table-
E to provide a foreign key to table-A. I only want a subset of the
records from table-E to show up in table-A. How can I do this? I know
I can get a subset of records from table-E with a query, but then how
to add all the results of the query to table-A? And if a record is
removed or altered in table-E, will those changes be reflected on
related entries in table-A? Will the query be automatically updating?
Can a database column be an SQL query?

[mdipierro]

db.define_table('tableE',SQLField('name'))

db.define_table('tableA',SQLField('tableE',db.tableE),SQLField('name'))

condition=db.tableE.name.like('%o') ## records where name ends in 'o'
db.tableA.tableE.requires=IS_IN_DB(db(condition),'tableE.id','%
(name)s')


On Oct 17, 3:47 am, morningovermidnight <thotzdatbreat...@gmail.com>
wrote:

[morningovermidnight]

Thanks!

[yarko]

I'm sorry.... this reads a little bit like magic to me - can someone
walk me through this line, and how it works:

db.tableA.tableE.requires=.....

I don't know how to read / make sense of this. Help please!

I think I get the right side of this ( condition is the database
selector; tableE.id is to be represended by the name filed out of
tableE);

Thanks,
Yarko

On Oct 18, 11:19 am, morningovermidnight <thotzdatbreat...@gmail.com>
wrote:

[yarko]

.... that should have read "...the name field out of tableE...."

[mdipierro]

I think the names are just confusing. tableA is the table field.
tableE in this case is a field of tableA. This is the normal way to
set a validator. The only point of the example is that in
IS_IN_DB(db, ...) db can be replaced by a SQLSet db(....).

On Oct 18, 4:09 pm, yarko <yark...@gmail.com> wrote:

[yarko]

AH! Yes, now it makes sense..... to modify like this, all magic
(for me at least) goes away:

db.define_table('tableE',SQLField('name'))

db.define_table('tableA',SQLField('tableE_id',db.tableE),SQLField('name'))

condition=db.tableE.name.like('%o') ## records where name ends in 'o'

db.tableA.tableE_id.requires=IS_IN_DB(db(condition),'tableE.id','%
(name)s')

Thanks!