Note you can do such query :
db(db.table.id > 0).select("sql_field_name AS new_sql_field_name") but I
don't recall how you access value of a field query like that (plain sql
passed into select...) You can experiment... There is a presentation from
Anthony Bastardi about these undocumented feature from deplaul web2py con
from 2015 I think... Or maybe Anthony can jump in here to clarify my
explanations.
There is also .with_alias('field alias'), but I am not sure exactly what
need you try to fill with them...
About db.executesql(), I think you can use it for DDL purpose, but I prefer
drop into pdAdmin for such type of work... And to be frank there is not
much value added to do it in db.executesql(), I think that having "view
definition" in DAL could be greate it would have to create the view if it
is not already create in the backend... I would avoid me to use the hacky
road that I had used and detailed above by defining them as web2py table
after havnig created them in the backend...
For many-to-many, it would be greate, but I guess it get complicated
rapidly when you start to allow such thing as every you don't have anymore
a one to one relation between DAL methodes and a table... You start to have
code that need to be specific for many-to-many relation which involve 3
table at least, other part of web2py need to support that, like sqlform...
How such table get defined?? Do you have to define these tables all
together or one at a time as now... If there still exist as a single piece,
do you have to make further check at each request to know if the reference
is a many-to-many "real" type... I never try to evaluate the impact over
DAL actual code of such things, I guess Massimo's did such assessment. His
input would enlighten us...
Richard
On Fri, Dec 9, 2016 at 3:37 PM, Val K <[email protected]> wrote:
> Thank you for your reply, Richard!
> " a normalized way to represent web2py list:reference field with a proper
> many-to-many relation table " - yes! this is what I meant, 'alias_name' -
> just very bad example of field name - it isn't about aliasing - sorry for
> the mess
>
> About views
> I think I found the acceptable way (for me at least) that is:
> 1. Get query string by _select()
> 2. Search string and add aliases to fields (if required)
> 3. Wrap string in "CREATE VIEW ... AS ..." and create view at db level
> by db.executesql( )
> 4. Define corresponding table for created view using
> db.some_table.any_field.clone(name = alias_for_some_table_any_field)
>
> all could be wrapped in the function( view_query, alias_map)
> Parsing sql string (item 2) is not a better way, but DAL doesn't support
> aliases for fields :(
>
> P.S. db.table.field.clone() - very useful function, but still undocumented
>
>
>
>
>
>
> On Friday, December 9, 2016 at 4:31:48 AM UTC+3, Richard wrote:
>>
>> Hello Val K,
>>
>> I am not sure I understand fully what your are talking about. On one
>> hand, I do understand that you would like a proper way to define SQL VIEW
>> in web2py and you propose to add a switch/flag/argument to define_table()
>> to do so, which would result in a select only table or something like
>> that... It could make sens, but I am not sure it would be accept base on my
>> experience it wouldn't pass as it seems a bit hacky and Massimo would not
>> implement it that way in DAL... Actually, I think there is no proper way to
>> implement view in DAL, and it a shame (we could have care more and answer
>> this need) that we don't have it... But you can define a view as a table in
>> web2py and use all the available feature regarding selection with this
>> "false" table (except create/read/update/delete obviously). You can also,
>> write a plain SQL SELECT and embeded it into a db.executesql() function.
>> Disavantage of the later method is that you can't access field with dot
>> notation db.table_name.field_name as web2py doesn't know about field and
>> even table name in this case, as db.executesql() return rows...
>>
>> On the other hand, I heard you talk about "storedin" field and aliasing
>> but I don't really understand to which common pratice you refer there...
>> Are you talking of a normalized way to represent web2py list:reference
>> field with a proper many-to-many relation table or are you talking about
>> something else? Which level of normalization are you trying to achieve with
>> aliasing and storedin... I don't understand what you refering to here.
>>
>>
>> Thanks
>>
>> Richard
>>
>> On Thu, Dec 8, 2016 at 5:49 PM, Val K <[email protected]> wrote:
>>
>>> It doesn't differ from reference fields. it is about automation
>>> distributed transaction.
>>> Table 'person' hasn't Field('alias_name' ) at db level, it's fake Field
>>> with reverse reference declaration ( 'storedin alias_opt.alias_name' ) that
>>> defines table.field that would be really involved in CRUD-process of table
>>> 'person'
>>>
>>> On Wednesday, December 7, 2016 at 5:29:28 AM UTC+3, Dave S wrote:
>>>>
>>>>
>>>> On Tuesday, December 6, 2016 at 3:41:38 PM UTC-8, Richard wrote:
>>>>>
>>>>> UP, never get any answer...
>>>>>
>>>>
>>>> How would this differ from reference fields?
>>>>
>>>> /dps
>>>>
>>>>
>>>>>
>>>>> On Sat, Jan 23, 2016 at 4:08 PM, Val K <[email protected]> wrote:
>>>>>
>>>>>> Hi guys!
>>>>>> I have an idea to improve DAL in scope of work with normalized DB.
>>>>>> As known It's a common practice to avoid NULL value by creating
>>>>>> separate (option) table(s) to store non required fields.
>>>>>> So, it would be great to have a field type like "storedin
>>>>>> table_name.field_name"
>>>>>> For example:
>>>>>>
>>>>>> db.define_table('alias_opt', Field('name'), Field('alias_name',
>>>>>> 'reference
>>>>>> person'))
>>>>>> db.define_table('person', Field('name'), Field('alias_name', 'storedin
>>>>>> alias_opt.alias_name'))
>>>>>>
>>>>>> #INSERT:
>>>>>> db.person.insert(name='Alex', alias_name='Macedonian')
>>>>>> # means:
>>>>>> id=db.person.insert(name='Alex')
>>>>>> db.alias_opt.insert(id=id, alias_name='Macedonian')
>>>>>>
>>>>>> #UPDATE:
>>>>>> db(db.person.id==id).update(... , alias_name=None)
>>>>>> # means:
>>>>>> # update person
>>>>>> ...
>>>>>> # update option table
>>>>>> update_opt_args = filter_storedin_fields(update_args)
>>>>>> opt_rec = db.alias_opt(id)
>>>>>> opt_rec.update(update_opt_args)
>>>>>> if not any(opt_rec.values()): # - all fields of option table
>>>>>> record is None
>>>>>> del db.alias_opt(id)
>>>>>> else:
>>>>>> db.alias_opt.update_or_insert(id==id, **update_opt_args)
>>>>>>
>>>>>>
>>>>>> #DELETE:
>>>>>> del db.person(id) also means del db.alias_opt(id), like ondelete=
>>>>>> 'CASCADE'
>>>>>>
>>>>>> #SELECT:
>>>>>> rows = db(db.person).select()
>>>>>> # means:
>>>>>> rows = db(db.person).select( left=[ db.alias_opt.on( db.alias_opt.id
>>>>>> == db.person.id ) ] )
>>>>>> but only "storedin" fields should be selected from db.alis_opt
>>>>>> and they should be accessed by row.alias_name (not only by row.
>>>>>> joined_table.field_name )
>>>>>>
>>>>>> Considering, that table person could be a VIEW (i.e. JOIN is already
>>>>>> performed at DB level), there is no need to make join at web2py level,
>>>>>> it could be fixed by passing an option arg like is_view=True to
>>>>>> define_table()
>>>>>> I know, that behavior of insert/update/delete could be easy realized
>>>>>> by custom class based on Table
>>>>>> with a little hacking Field-class to intercept field type to fix it
>>>>>> to web2py type ( considering person.alias_name.type ==
>>>>>> alias_opt.alias_name.type ).
>>>>>> But it's hard for me to change select() behavior, because there is
>>>>>> only common_filter, but there isn't common_join/common_left with
>>>>>> providing of maping (aliasing) joined table fields to 'storedin'
>>>>>> fields.
>>>>>>
>>>>>> In fact, I dream of common_join depends on discriminator field, that
>>>>>> will switch tables to be joined depend on discriminator value specified
>>>>>> in
>>>>>> the query (something like db.object.type_id==type_id),
>>>>>> if descriminator is not specified or couldn't be resolved at web2py
>>>>>> level, it performs left join all tables (from a list containing possible
>>>>>> common_join tables)
>>>>>>
>>>>>> P.S. May be I try to reinvent the wheel, so feel free to shoot me!
>>>>>>
>>>>>> --
>>>>>> 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.
>>>
>>
>> --
> 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.
