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.
