Using parse_as_rest: problem with cross joins?

[David Orme]

Hi,

I'm looking at parse_as_rest() to provide an API and can't get it to work as expected. I have two table: datasets and fields, with a 1 to N relationship and I want to create an API that returns datasets that have a field name matching a pattern so (using the tuple version of patterns to provide a pattern, base query and exposed fields).

        patterns = [
            ("/field_name/{fields.field_name.contains}/data[datasets.id]", None, None),
            ]

I thought this was fine, but now I want to restrict results to the latest version of datasets either through:

    patterns = [
        ("/field_name/{fields.field_name.contains}/data[datasets.id]",  (db.datasets.latest == True), None),
        ]

or:

 

  parser = db.parse_as_rest(patterns, args, vars, queries=(db.datasets.latest == True))

That was returning datasets that are not the latest version. I stuck a print(dbset._select()) into pydal/helpers/rest.py to try and figure it out. I think that the example in the manual goes from 1 to N (people to pets), whereas here I am going from N to 1 (fields to datasets) and the underlying SQL from that select is performing a cross join:

SELECT *
    FROM "datasets"
    WHERE ("datasets"."id" IN (
        SELECT "fields"."dataset_id"
            FROM "fields", "datasets"
            WHERE (("fields"."field_name" ILIKE '%search_text%')
                AND ("datasets"."latest" = 'T') ESCAPE '\')));

That cross join is breaking the link between the two tables. If I edit that by hand to check:

SELECT *
    FROM "datasets"
    WHERE ("datasets"."id" IN (
        SELECT "datasets"."latest", "datasets"."id", "fields"."dataset_id"
            FROM "fields", "datasets"
            WHERE (("fields"."field_name" ILIKE '%search_text%')
                AND ("datasets"."latest" = 'T') ESCAPE '\')));

then I get rows like this:

 latest      | id  | dataset_id
-------------+-----+------------
 T           | 134 |        177
 T           | 134 |        177
 T           | 134 |        177
 T           | 134 |        180
 T           | 134 |        180
 T           | 134 |        180
 T           | 158 |        177
 T           | 158 |        177
 T           | 158 |        177

What am I doing wrong?

Cheers,

David

 From looking at the code, it seems like the current options are:

1. If parse_at_rest() gets a queries object that isn't a dict, the is applied 

[Massimo Di Pierro]

Hello David,

I recommend moving the the new DBAPI (other thread). Do not know the parse_as_rest will be supported very much in the near future. Got very little traction.

[David Orme]

Hi Massimo,

I saw that announcement shortly after submitting the question. I've downloaded the developer source (/static/nightly, with VERSION contents "Version 2.18.5-stable+timestamp.2019.04.08.04.22.03") and neither dbapi.py (nor it's recent rebranding as restapi.py) is in that zip. Is it reasonable to drop a clone of pydal/pydal into packages or is there a release coming out shortly with the new api in it?

Thanks,

David

[David Orme]

Sorry, being dim. The web2py repo links out to pydal, so I can just clone web2py/web2py.

Cheers,

David

[Massimo Di Pierro]

Will release at the end of the month