Hello!
Thanks for your help! but it means that for every record inside events
table i should do an additional query in the modificators table, so if in a
query i want to get 100 events and his modificators i should do 101 queries
to the database, i think (im not sure, i'll will talk tomorrow with the
DevOps) that database queries are expensives.
Or instead, loop over all the modificators, find the ID, do a query using
pydal belongs and do a matchup between the two responses to relationate
each modificator to his respective events.
After i'll check this option tomorrow with my DevOps i will edit this
message
El lunes, 29 de agosto de 2016, 9:17:20 (UTC-4), Mirek Zvolský escribió:
>
> With pydal (ie. db=DAL()) you can get the records in 2 queries:
> event = db(db.events.id == wish_event_id).select()
> and event_modificators = db(db.modificator.events_id
> == wish_event_id).select()
>
> then convert to the list:
> event_modificators = [row for row in event_modificators] # item is
> whole row
> or event_modificators = [row.modificator for row in event_modificators]
> # item is one field only.
>
> ----
> If modificator has just 1 field (+ 2nd id field),
> then instead of solution with 2 tables you could use just 1 table with
> Field(..., type="list:string").
> This has special support on Google App Engine NoSQL, but on any other SQL
> database (SQLite, Postgres) it is supported as 'text' with '|' separators
> inside. I think you will get a list immediately when asking for such field.
> And it is supported in SQLFORM() too to set 1+ modificators.
>
>
>
>
>
> Dne neděle 28. srpna 2016 23:21:31 UTC+2 luis.va...@metamaxzone.com
> napsal(a):
>>
>> Hello!
>>
>> I've this data structure in my database: i've two tables events and
>> modificators, one event can have many modificators so i've a one to many
>> relation. Im trying to build an HTTP endpoint that returns a json with
>> multiple events and all the asociated modificators, i order to do this i do
>> a join and return the select as a list, but my problem (and i understand
>> this problem is related to the way SQL works) is that the parent is
>> repeating on every record, so i've an event A with four modificators B, C,
>> D and E i get something like this:
>>
>> [{"events": A, "modificator": B}, {"events": A, "modificator": C},
>> {"events": A, "modificator": D}, {"events": A, "modificator": E}]
>>
>> instead of this i want something like
>>
>> [{"events": A, "modificator": [B, C, D, E]}]
>>
>> i now i can do this with a for and creating my own custom dictionary, but
>> i think this problem will be repeated several times along the api im
>> developing because my database has multiple one to many relations so it
>> would be good to get some "generic" solution instead of creating a lot of
>> nested fors for each case, also i think is a little bit inefficient doing
>> it on this way.
>>
>> I was wondering if the DAL or web2py himself has some method for this, or
>> if you can recomend a external library because i thinks this is a fair
>> common case, even if you can give me some guidance to develop a common
>> class, or function for this problem.
>>
>> By the way, i read this example in the web2py manual:
>>
>> >>> for person in db().select(db.person.ALL):
>> print person.name
>> for thing in person.thing.select():
>> print ' ', thing.nameAlex
>> Boat
>> ChairBob
>> ShoesCarl
>>
>> but i think it doesnt fit my needs because of the high database I/O, im
>> deploying my app on EC2 (Amazon) and the databases instances have a really
>> high cost, this is combined with my database having a LOT of records (about
>> 2000 events and 5800 modificators) and growing.
>>
>>
>> Thanks for any help or guidance you can provide!
>>
>
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
