Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Option to expose expanded foreign keys in JSON/CSV #233

Closed
simonw opened this issue Apr 21, 2018 · 11 comments
Closed

Option to expose expanded foreign keys in JSON/CSV #233

simonw opened this issue Apr 21, 2018 · 11 comments
Labels
feature

Comments

@simonw
Copy link
Owner

simonw commented Apr 21, 2018

https://datasette-cluster-map-demo.datasettes.com/sf-trees-02c8ef1/Street_Tree_List?qCareAssistant=1

f36b87c0-478e-4d55-9a5f-ad37df0b47cb

It would be nice if the info bubbles there could expose more than just the IDs, and if the title showed the expanded name of the selected qCareAssistant.
@simonw
Copy link
Owner Author

simonw commented Jun 15, 2018 •
edited

I'm going with the terminology "labels" here. You'll be able to add ?_labels=1 and the JSON will look something like this:

{
    "rowid": 233,
    "TreeID": 121240,
    "qLegalStatus": {
        "value" 2,
        "label": "Private"
    }
    "qSpecies": {
        "value": 16,
        "label": "Sycamore"
    }
    "qAddress": "91 Commonwealth Ave",
    ...
}

I need this to help build foreign key expansions for CSV files, see #266

@simonw
Copy link
Owner Author

simonw commented Jun 15, 2018

I considered including a "table" key like this:

    "qLegalStatus": {
        "value" 2,
        "label": "Private",
        "table": "qLegalStatus"
    }

This would help generate the HTML links using just the JSON data. But... I realized that in a list of 50 rows that value would be duplicated 50 times which is a bit nasty.

@simonw
Copy link
Owner Author

simonw commented Jun 15, 2018

Nearly done, but I need the HTML view to ignore the ?_labels=1 param (it throws an error at the moment).

simonw added a commit that referenced this issue Jun 15, 2018
@simonw
.json?_labels=1 to expand foreign keys, refs #233
825b8a2 
Output looks something like this:

    {
        "rowid": 233,
        "TreeID": 121240,
        "qLegalStatus": {
            "value" 2,
            "label": "Private"
        }
        "qSpecies": {
            "value": 16,
            "label": "Sycamore"
        }
        "qAddress": "91 Commonwealth Ave",
        ...
    }
@simonw
Copy link
Owner Author

simonw commented Jun 15, 2018

Demo: https://datasette-json-labels-demo.now.sh/fixtures-fda0fea/facetable.json?_labels=1&_shape=array

@simonw
Copy link
Owner Author

simonw commented Jun 15, 2018

I'm also going to add the ability to specify individual columns that you want to expand using ?_label=city_id&_label=state_id

@simonw
Copy link
Owner Author

simonw commented Jun 15, 2018

The .json and .csv links displayed on the table page should default to using ?_labels=1 if Datasette detects that there are foreign key expansions available for the page.

@simonw simonw added the feature label Jun 16, 2018
@simonw
Copy link
Owner Author

simonw commented Jun 16, 2018 •
edited

I'm going to support ?_labels= on HTML views, but I'll allow it to be used to turn them off (they are on by default) using ?_labels=off.

Related: 7e0caa1 where I added a new value_as_boolean helper extracted from how --config works in cli.py.

@simonw
Copy link
Owner Author

simonw commented Jun 16, 2018

Should facets always have their labels expanded or should they also obey the _labels and _label querystring arguments?

@simonw
Copy link
Owner Author

simonw commented Jun 16, 2018 •
edited

I'm a bit torn on naming - choices are:

  • ?_labels=on and ?_label=col1&_label=col2
  • ?_expands=on (or ?_expand_all=on) and ?_expand=col1&_expand=col2

@simonw
Copy link
Owner Author

simonw commented Jun 16, 2018

For the moment I'm going with _labels=.

@simonw simonw changed the title Mechanism for exposing expanded foreign keys in JSON Option to expose expanded foreign keys in JSON/CSV Jun 16, 2018
simonw added a commit that referenced this issue Jun 16, 2018
@simonw
?_labels= and ?_label=COL to expand foreign keys in JSON/CSV
40287b1 
These new querystring arguments can be used to request expanded foreign keys
in both JSON and CSV formats.

?_labels=on turns on expansions for ALL foreign key columns

?_label=COLUMN1&_label=COLUMN2 can be used to pick specific columns to expand

e.g. `Street_Tree_List.json?_label=qSpecies&_label=PlantType&_shape=array`

    [
      {
        "rowid": 1,
        "TreeID": 141565,
        "qLegalStatus": 1,
        "qSpecies": {
          "value": 1,
          "label": "Myoporum laetum :: Myoporum"
        },
        "qAddress": "501X Baker St",
        "SiteOrder": 1,
        "qSiteInfo": 1,
        "PlantType": {
          "value": 1,
          "label": "Tree"
        },
        "qCaretaker": 1,
        "qCareAssistant": null,
        "PlantDate": "07/21/1988 12:00:00 AM",
        "DBH": 21,
        "PlotSize": "Width 0ft",
        "PermitNotes": "Permit Number 25401",
        "XCoord": 6000609,
        "YCoord": 2110829,
        "Latitude": 37.7759676911831,
        "Longitude": -122.441396661871,
        "Location": "(37.7759676911831, -122.441396661871)"
      },
      {
        "rowid": 2,
        "TreeID": 232565,
        "qLegalStatus": 2,
        "qSpecies": {
          "value": 2,
          "label": "Metrosideros excelsa :: New Zealand Xmas Tree"
        },
        "qAddress": "940 Elizabeth St",
        "SiteOrder": 1,
        "qSiteInfo": 2,
        "PlantType": {
          "value": 1,
          "label": "Tree"
        },
        "qCaretaker": 1,
        "qCareAssistant": null,
        "PlantDate": "03/20/2017 12:00:00 AM",
        "DBH": 3,
        "PlotSize": "Width 4ft",
        "PermitNotes": "Permit Number 779625",
        "XCoord": 6000396.48544,
        "YCoord": 2101998.8644,
        "Latitude": 37.7517102172731,
        "Longitude": -122.441498017841,
        "Location": "(37.7517102172731, -122.441498017841)"
      }
    ]

The labels option also works for the HTML and CSV views.

HTML defaults to `?_labels=on`, so if you pass `?_labels=off` you can disable
foreign key expansion entirely - or you can use `?_label=COLUMN` to request
just specific columns.

If you expand labels on CSV you get additional columns in the output:

`/Street_Tree_List.csv?_label=qLegalStatus`

    rowid,TreeID,qLegalStatus,qLegalStatus_label...
    1,141565,1,Permitted Site...
    2,232565,2,Undocumented...

I also refactored the existing foreign key expansion code.

Closes #233. Refs #266.
@simonw simonw closed this as completed in ed631e6 Jun 16, 2018
@simonw
Copy link
Owner Author

simonw commented Jun 16, 2018

Some demos:

@simonw simonw mentioned this issue Jun 16, 2018
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
feature
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@simonw