one to many joins repeated parent

[luis.va...@metamaxzone.com]

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.name
Alex
     Boat
     Chair
Bob
     Shoes
Carl

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!

[Mirek Zvolský]

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):

[Mirek Zvolský]

btw:

your tables aren't large, but small.

This will run well with SQLite on every hosting.

Example:

- Google App Engine NoSQL for free (with list:string fields for modificator)

- Forpsi virtual server 20G SSD with Postgres (or just SQLite)

http://www.forpsi.com/virtual/?lang=en-US

for 1.2 EUR/month,

install Debian (or something different) via VPS administration, run ssh, install nginx+postgres via apt - and go.

Or this should install nginx+Web2py on Debian: https://github.com/zvolsky/web2py/blob/master/scripts/setup-web2py-nginx-uwsgi-ubuntu.sh

I write that just because you say Amazon EC2 is too expensive(?)

Dne neděle 28. srpna 2016 23:21:31 UTC+2 luis.va...@metamaxzone.com napsal(a):

Hello!

[luis.va...@metamaxzone.com]

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