Line 237:
Line 237:
</pre>
</pre>
−
=== CSV export via SQL (alpha) ===
+
=== CSV export via SQL (beta) ===
We are testing a new kind of tool to provide the data: every day an SQL database is fed by the regular daily CSV export, and published online thanks to Datasette tool.
We are testing a new kind of tool to provide the data: every day an SQL database is fed by the regular daily CSV export, and published online thanks to Datasette tool.
Line 255:
Line 255:
* It is possible to restrict the number of fields displayed and exported.
* It is possible to restrict the number of fields displayed and exported.
* It is possible to order results by any field.
* It is possible to order results by any field.
+
+
==== Example ====
+
'''1 -- Build your query (or ask someone to build it for you)'''
+
+
Eg. all German products that have been scanned at least one time.
+
-- Products from Germany that have been scanned at least one time
+
select code, product_name from [all]
+
where countries_en like "%germany%" and unique_scans_n is not null
+
order by unique_scans_n desc
+
-- the limit here displays 20 results; remove it or comment it with "--" when you build your CSV export
+
limit 20
+
<nowiki>https://mirabelle.openfoodfacts.org/products?sql=--+Products+from+Germany+that+have+been+scanned+at+least+one+time%0D%0Aselect+code%2C+product_name+from+%5Ball%5D%0D%0Awhere+countries_en+like+%22%25germany%25%22+and+unique_scans_n+is+not+null%0D%0Aorder+by+unique_scans_n+desc%0D%0A--+the+limit+here+displays+20+results%3B+remove+it+or+comment+it+with+%22--%22+when+you+build+your+CSV+export%0D%0Alimit+20</nowiki>
+
+
'''2 -- Copy "CSV" link on the result page.'''
+
+
'''3 -- If necessary, edit the link to remove the "limit+20" limit to get all the products.'''
+
+
Eg. (don't click this link if you don't want to get 90,000+ products) <nowiki>https://mirabelle.openfoodfacts.org/products.csv?sql=--+Products+from+Germany+that+have+been+scanned+at+least+one+time%0D%0Aselect+code%2C+product_name+from+%5Ball%5D%0D%0Awhere+countries_en+like+%22%25germany%25%22+and+unique_scans_n+is+not+null%0D%0Aorder+by+unique_scans_n+desc%0D%0A--+the+limit+here+displays+20+results%3B+remove+it+or+comment+it+with+%22--%22+when+you+build+your+CSV+export%0D%0A&_size=max</nowiki>
+
+
Now you can use this link to download the CSV with your favourite tool (wget, curl, web browser, etc.).