feat: Use openfoodfacts-query service for facet queries instead of product_tags collection #8947

john-gom · 2023-09-04T14:57:54Z

Include unit tests for new functionality
If you have multiple commits please combine them into one commit by squashing them.

What

Use the Postgres-based query service for counts and aggregations. Updated the product_tags refresh scripts to refresh this database instead

Related issue(s) and discussion

Fixes Move products_tags to Postgres to improve performance #8676

scripts/refresh_postgres.pl

alexgarel

LGTM.

Kudos @john-gom (for the off-query part firstly !).

lib/ProductOpener/Display.pm

alexgarel · 2023-09-11T13:50:07Z

lib/ProductOpener/Display.pm


-				$count_results = [$count_results->all]->[0];
+			if ((not $@) and (defined $count_results)) {


there again, I think it's wiser to save $@ just after the eval block, to avoid a future mistake.

alexgarel · 2023-09-11T14:09:05Z

scripts/refresh_postgres.pl

+my $path = "$query_url/importfrommongo?from=$from";
+print STDERR "Importing to $path\n";
+my $ua = LWP::UserAgent->new();
+$ua->get($path);


Is this query returning quickly ? (maybe it should)

This does wait until the refresh has been completed before returning at the moment. It will be quick if it is an incremental update, but takes about 1.5 hours to do a full load.

lib/ProductOpener/Display.pm

stephanegigandet · 2023-09-14T08:49:06Z

http://world.openfoodfacts.localhost/country/united-kingdom/misc does work though

john-gom · 2023-09-15T09:43:00Z

I had to do a small change to run it in a dev environment in Linux:

host.docker.internal is not available in Linux by default (only Windows and Mac), but it could be added like this: (it may have unwanted side effects)
+++ b/docker/dev.yml
@@ -20,6 +20,10 @@ x-backend-conf: &backend-conf
     - ./docker/docker-entrypoint.sh:/docker-entrypoint.sh
     # we use this for debugging, from times to times
     - ./debug:/mnt/podata/debug/
+  # Allow the container to connect to the host
+  # this is needed to access the dev openfoodfacts-query running on the host
+  extra_hosts:
+    - "host.docker.internal:host-gateway"

Hi @stephanegigandet , just to clarify, you should only need this kind of configuration if something running inside docker needs to access a service running on the host. So this would either be the query service running in docker trying to access MongoDB running on the host (not in Docker) or Product Opener running in Docker trying to access the Query service running on the host (not in Docker). If the Query service is running in Docker and is accessing MongoDB running in docker then it should be able to access the MongoDB database using the "mongodb" host name as the Query service joins the po_default network. What scenario were you trying?

john-gom · 2023-09-15T09:44:28Z

For queries that include a country filter, the country filter is not taken into account.

e.g. http://uk.openfoodfacts.localhost/label/fair-trade/misc

[Nest] 1  - 09/14/2023, 8:46:11 AM   DEBUG [QueryService] Object:
[
  {
    "$match": {
      "labels_tags": "en:fair-trade",
      "countries_tags": "en:united-kingdom"
    }
  },
  {
    "$unwind": "$misc_tags"
  },
  {
    "$group": {
      "_id": "$misc_tags",
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$sort": {
      "count": -1
    }
  },
  {
    "$skip": 0
  },
  {
    "$limit": 10000
  }
]

[Nest] 1  - 09/14/2023, 8:46:11 AM   DEBUG [QueryService] select value _id, count(*) count from "query"."product_misc_tag" as "pt" where (not pt.obsolete) and (EXISTS (select "pt2".* from "query"."product_labels_tag" as "pt2" where (pt2.product_id = pt.product_id and pt2.value = 'en:fair-trade'))) group by "pt"."value" order by 2 desc limit 10000
[Nest] 1  - 09/14/2023, 8:46:11 AM   DEBUG [QueryService] Processed misc_tags where labels_tags == en:fair-trade in 21 ms. Returning 54 records

Hi @stephanegigandet . I hadn't realised that we supported multiple filters like this. I can enhance the query handling to support this relatively easily

john-gom · 2023-09-15T11:11:40Z

For queries that include a country filter, the country filter is not taken into account.
e.g. http://uk.openfoodfacts.localhost/label/fair-trade/misc

[Nest] 1  - 09/14/2023, 8:46:11 AM   DEBUG [QueryService] Object:
[
  {
    "$match": {
      "labels_tags": "en:fair-trade",
      "countries_tags": "en:united-kingdom"
    }
  },
  {
    "$unwind": "$misc_tags"
  },
  {
    "$group": {
      "_id": "$misc_tags",
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$sort": {
      "count": -1
    }
  },
  {
    "$skip": 0
  },
  {
    "$limit": 10000
  }
]

[Nest] 1  - 09/14/2023, 8:46:11 AM   DEBUG [QueryService] select value _id, count(*) count from "query"."product_misc_tag" as "pt" where (not pt.obsolete) and (EXISTS (select "pt2".* from "query"."product_labels_tag" as "pt2" where (pt2.product_id = pt.product_id and pt2.value = 'en:fair-trade'))) group by "pt"."value" order by 2 desc limit 10000
[Nest] 1  - 09/14/2023, 8:46:11 AM   DEBUG [QueryService] Processed misc_tags where labels_tags == en:fair-trade in 21 ms. Returning 54 records

Hi @stephanegigandet . I hadn't realised that we supported multiple filters like this. I can enhance the query handling to support this relatively easily

Hi @stephanegigandet , this should be fixed now

codecov-commenter · 2023-09-15T15:17:46Z

Codecov Report

Merging #8947 (f311104) into main (7e07fcb) will decrease coverage by 0.03%.
The diff coverage is 17.14%.

@@            Coverage Diff             @@
##             main    #8947      +/-   ##
==========================================
- Coverage   47.95%   47.93%   -0.03%     
==========================================
  Files          64       64              
  Lines       20013    20052      +39     
  Branches     4850     4868      +18     
==========================================
+ Hits         9597     9611      +14     
- Misses       9168     9186      +18     
- Partials     1248     1255       +7

Files	Coverage Δ
lib/ProductOpener/Data.pm	`76.00% <50.00%> (-8.22%)`	⬇️
lib/ProductOpener/Display.pm	`11.27% <10.34%> (+0.06%)`	⬆️

📣 We’re building smart automated test selection to slash your CI/CD build times. Learn more

stephanegigandet · 2023-09-15T15:48:51Z

What scenario were you trying?

I was following the instructions on https://github.com/openfoodfacts/openfoodfacts-query#prepare-for-development
I didn't realize that I already had a running query server inside docker, so I ran the npm commands that started another one on the host.

john-gom · 2023-10-03T09:38:13Z

Hi @stephanegigandet , @raphael0202 , @alexgarel , I have reverted the code so that the products_tags collection is used if the QUERY_URL is not available (or the call to the query service fails). Would you be happy to take a look at the latest changes to check you have no concerns.

Also, the tests are not passing but I can't figure out why my changes should have affected this. If anyone can help I would be vary grateful.

sonarcloud · 2023-10-09T12:01:03Z

Kudos, SonarCloud Quality Gate passed!

0 Bugs
0 Vulnerabilities
0 Security Hotspots
0 Code Smells

No Coverage information
0.0% Duplication

stephanegigandet · 2023-10-09T12:04:44Z

Also, the tests are not passing but I can't figure out why my changes should have affected this. If anyone can help I would be vary grateful.

You are getting a 404 because the returned product count is 0 I think.

john-gom · 2023-10-09T12:46:04Z

Also, the tests are not passing but I can't figure out why my changes should have affected this. If anyone can help I would be vary grateful.

You are getting a 404 because the returned product count is 0 I think.

Thanks. Figured it out, the code was still going to product_tags when no_cache=1

john-gom · 2023-10-09T12:51:50Z

Some comparisons. Local version connecting to quer.openfoodfacts.org, production still using product_tags collection:

Ecoscore by nutrition grade D:

Query counts seem lower - investigating...

john-gom · 2023-10-09T15:29:21Z

I added a "select" service to compare results but realised that my own local service was still using my local query service as I hadn't rebuilt and re-started my docker containers. Results are now as expected query service is slightly higher as it was refreshed more recently than product_tags:

john-gom added 14 commits July 24, 2023 18:49

Call local postgres service instead of mongo

335e165

Initial count support

d541056

Removing unused code

de06ea3

Separate queries so easier to mock results

f68e5cf

Make QUERY_URL configurable

8377b6c

Removing references to products_tags

9e29c3b

Note and tweaks

b545ee8

Perl tidy fixes

233a7ab

Get from query first then fallback

1cf12fc

Merge remote-tracking branch 'origin/main' into issue/8676

77cba44

Fix merge issue

85a8e97

Update port

42275c7

Tidy up comment

b02b765

Merge remote-tracking branch 'origin/main' into issue/8676

11b84e6

john-gom requested a review from a team as a code owner September 4, 2023 14:57

github-actions bot assigned john-gom Sep 4, 2023

Merge branch 'main' into issue/8676

5e3055f

john-gom changed the title ~~feat: Use openfoodfacts-query servie for facet queries instead of product_tags collection~~ feat: Use openfoodfacts-query service for facet queries instead of product_tags collection Sep 4, 2023

raphael0202 reviewed Sep 11, 2023

View reviewed changes

scripts/refresh_postgres.pl Outdated Show resolved Hide resolved

alexgarel approved these changes Sep 11, 2023

View reviewed changes

Fix comment block

c8c8704

john-gom added 2 commits September 15, 2023 15:31

extra_hosts for Linux

eb6bbe2

Merge remote-tracking branch 'origin/main' into issue/8676

ad2c868

github-actions bot added the 🐋 Docker https://docker-curriculum.com/ label Sep 15, 2023

Allow Produuct Opener to continue to use product_tags during transition

8e98741

github-actions bot removed MongoDB We have 2 mongodb collections: one for current products, and one for obsolete products JavaScript GitHub Actions Pull requests that update Github_actions code labels Sep 19, 2023

john-gom added 4 commits September 19, 2023 09:58

Perltidy fixes

600ddd3

Merge remote-tracking branch 'origin/main' into issue/8676

fa5b2a5

Prevent errors when query_urll not set on refresh_product_tags

894b9c5

Set query_url for staging

612a563

github-actions bot added the GitHub Actions Pull requests that update Github_actions code label Oct 2, 2023

Merge remote-tracking branch 'origin/main' into issue/8676

d327c37

john-gom added 2 commits October 9, 2023 11:52

Merge branch 'main' into issue/8676

6a303fc

Make sure product_tags is not used when no_cache=1

f311104

john-gom merged commit 1fbbe06 into main Oct 9, 2023
14 checks passed

john-gom deleted the issue/8676 branch October 9, 2023 15:39

openfoodfacts-bot mentioned this pull request Oct 9, 2023

chore(main): 🚀 Open Food Facts Backend - Product Opener - Release 2.20.0. #9054

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat: Use openfoodfacts-query service for facet queries instead of product_tags collection #8947

feat: Use openfoodfacts-query service for facet queries instead of product_tags collection #8947

john-gom commented Sep 4, 2023 •

edited

Loading

alexgarel left a comment

alexgarel Sep 11, 2023

alexgarel Sep 11, 2023

john-gom Sep 13, 2023

stephanegigandet commented Sep 14, 2023

john-gom commented Sep 15, 2023

john-gom commented Sep 15, 2023

john-gom commented Sep 15, 2023

codecov-commenter commented Sep 15, 2023 •

edited

Loading

stephanegigandet commented Sep 15, 2023

john-gom commented Oct 3, 2023

sonarcloud bot commented Oct 9, 2023

stephanegigandet commented Oct 9, 2023

john-gom commented Oct 9, 2023

john-gom commented Oct 9, 2023

john-gom commented Oct 9, 2023


		$count_results = [$count_results->all]->[0];
		if ((not $@) and (defined $count_results)) {

feat: Use openfoodfacts-query service for facet queries instead of product_tags collection #8947

feat: Use openfoodfacts-query service for facet queries instead of product_tags collection #8947

Conversation

john-gom commented Sep 4, 2023 • edited Loading

What

Related issue(s) and discussion

alexgarel left a comment

Choose a reason for hiding this comment

alexgarel Sep 11, 2023

Choose a reason for hiding this comment

alexgarel Sep 11, 2023

Choose a reason for hiding this comment

john-gom Sep 13, 2023

Choose a reason for hiding this comment

stephanegigandet commented Sep 14, 2023

john-gom commented Sep 15, 2023

john-gom commented Sep 15, 2023

john-gom commented Sep 15, 2023

codecov-commenter commented Sep 15, 2023 • edited Loading

Codecov Report

stephanegigandet commented Sep 15, 2023

john-gom commented Oct 3, 2023

sonarcloud bot commented Oct 9, 2023

stephanegigandet commented Oct 9, 2023

john-gom commented Oct 9, 2023

john-gom commented Oct 9, 2023

john-gom commented Oct 9, 2023

john-gom commented Sep 4, 2023 •

edited

Loading

codecov-commenter commented Sep 15, 2023 •

edited

Loading