adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-10-30 21:52:00 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

[web API] pwg.categories.getList - queried category is out of order when using limit #2208

New Issue
Open
opened 2026-10-05 01:09:47 +02:00 by adam · 4 comments
adam commented 2026-10-05 01:09:47 +02:00
Owner
Copy link
Copy source

The logic behind limit is a bit mysterious in a couple ways. Take the following query:

Background

ws.php?format=json&method=pwg.categories.getList&cat_id=204

This will return the category with the id that equals 204 as well as any other category that has 204 listed as one of its uppercats. These are unsorted.

With the query adding limit:

ws.php?format=json&method=pwg.categories.getList&cat_id=204&limit=1

Since the cat_id is greater than 0 (not the root), this will actually return two results. The bizarre thing about this is that I would assume that this would be so that it can return the category id=204 and the first sub-category, but it... doesn't.

What should be happening

My assumption is that the intention is for this to sort the categories as:

{"categories": [
  { "id": "204" },
  { "uppercats": "204", "rank": "1"},
  { "uppercats": "204", "rank": "2"},
  { "uppercats": "204", "rank": "3"}
]}

What is actually happening

That would mean it is returning two, but the first is the identity category 204 and the second is its first child. So limit 1 would limit it to 1 child. However if the category 204 has a rank that isn't 1 within its uppercat, say 3, then it will result in the results being sorted like:

{"categories": [
  { "uppercats": "204", "rank": "1", "global_rank": "3.1"},
  { "uppercats": "204", "rank": "2", "global_rank": "3.2"},
  /* end of list for `limit=1` */
  { "id": "204", "rank": "3", "global_rank": "3"},
  { "uppercats": "204", "rank": "3", "global_rank": "3.3"}
]}

This results in getting the first two sub-categories in the category and not getting the identity category at all, which means the limit is utterly confusing, particularly since the results are later sorted by global_rank_compare, which means if the searched for category's rank falls within the limit then it will be the first result.

  $cats = array();
  while ($row = pwg_db_fetch_assoc($result))
  {
    # ...

    $cats[] = $row;
  }
  usort($cats, 'global_rank_compare');

Fix

I imagine that this is just a typo in the query itself:

    $query .= '
  ORDER BY `rank` ASC 
  LIMIT '.($params['limit'] + ($params['cat_id'] > 0 ? 1 : 0));

And tl;dr that order by rank should be global_rank.

So anyway it's a one line change and I'll put up a PR shortly.

The logic behind `limit` is a bit mysterious in a couple ways. Take the following query: ## Background ``` ws.php?format=json&method=pwg.categories.getList&cat_id=204 ``` This will return the category with the `id` that equals `204` as well as any other category that has `204` listed as one of its `uppercats`. These are _unsorted_. With the query adding `limit`: ``` ws.php?format=json&method=pwg.categories.getList&cat_id=204&limit=1 ``` Since the `cat_id` is greater than `0` (not the root), this will actually return _two_ results. The bizarre thing about this is that I would assume that this would be so that it can return the category `id=204` and the first sub-category, but it... doesn't. ## What _should be_ happening My assumption is that the _intention_ is for this to sort the categories as: ```json {"categories": [ { "id": "204" }, { "uppercats": "204", "rank": "1"}, { "uppercats": "204", "rank": "2"}, { "uppercats": "204", "rank": "3"} ]} ``` ## What is _actually_ happening That would mean it is returning two, but the first is the identity category `204` and the second is its first child. So limit 1 would limit it to 1 child. However if the category `204` has a rank that isn't `1` within its uppercat, say `3`, then it will result in the results being sorted like: ```jsonc {"categories": [ { "uppercats": "204", "rank": "1", "global_rank": "3.1"}, { "uppercats": "204", "rank": "2", "global_rank": "3.2"}, /* end of list for `limit=1` */ { "id": "204", "rank": "3", "global_rank": "3"}, { "uppercats": "204", "rank": "3", "global_rank": "3.3"} ]} ``` This results in getting the first two sub-categories in the category and not getting the identity category at all, which means the `limit` is utterly confusing, particularly since the results are later sorted by `global_rank_compare`, which means if the searched for category's rank falls within the `limit` then it _will_ be the first result. ```php $cats = array(); while ($row = pwg_db_fetch_assoc($result)) { # ... $cats[] = $row; } usort($cats, 'global_rank_compare'); ``` ## Fix I imagine that this is just a typo in the query itself: ```php $query .= ' ORDER BY `rank` ASC LIMIT '.($params['limit'] + ($params['cat_id'] > 0 ? 1 : 0)); ``` And **tl;dr** that order by `rank` should be `global_rank`. So anyway it's a one line change and I'll put up a PR shortly. <!-- github-sync-id:4265661956 -->
adam commented 2026-10-05 01:09:48 +02:00
Author
Owner
Copy link
Copy source

Hi @quacainia

I need to make some checks but what I'm quite sure about is that order by global_rank in SQL won't work as expected. global_rank needs to be compared with a "natural order" algorithm and SQL doesn't provide that.

Take 10.2.21 and 9.23.1 and tell me which should come first.

Anyway, I agree there is a problem. I just don't think this fix proposition is correct.

Hi @quacainia I need to make some checks but what I'm quite sure about is that `order by global_rank` in SQL won't work as expected. `global_rank` needs to be compared with a "natural order" algorithm and SQL doesn't provide that. Take `10.2.21` and `9.23.1` and tell me which should come first. Anyway, I agree there is a problem. I just don't think this fix proposition is correct. <!-- github-comment-id:4252353659 -->
adam commented 2026-10-05 01:09:49 +02:00
Author
Owner
Copy link
Copy source

@plegall Would that scenario be in the case that either 10.2.21 or 9.23.1 have multiple uppercat values? I guess if cat_id isn't specified then yeah that would lead to strange results.

My other solution, which would at least solve the cat_id problem was this:

    $uppercat_order = ($params['cat_id'] > 0) ? ' (`id`='.(int)($params['cat_id']).') DESC, ':'';

    $query .= '
  ORDER BY '.$uppercat_order.'`rank` ASC 
  LIMIT '.($params['limit'] + ($params['cat_id'] > 0 ? 1 : 0));

I think to do the global rank sort we'd need to either do a complicated lpad dance or slice the sorted results in php

@plegall Would that scenario be in the case that either `10.2.21` or `9.23.1` have multiple `uppercat` values? I guess if `cat_id` isn't specified then yeah that would lead to strange results. My other solution, which would at least solve the `cat_id` problem was this: ```php $uppercat_order = ($params['cat_id'] > 0) ? ' (`id`='.(int)($params['cat_id']).') DESC, ':''; $query .= ' ORDER BY '.$uppercat_order.'`rank` ASC LIMIT '.($params['limit'] + ($params['cat_id'] > 0 ? 1 : 0)); ``` I think to do the global rank sort we'd need to either do a complicated `lpad` dance or slice the sorted results in php <!-- github-comment-id:4252821148 -->
adam commented 2026-10-05 01:09:50 +02:00
Author
Owner
Copy link
Copy source

NATURAL_SORT_KEY exists in MariaDB 10.7+ but not MySQL as far as I can tell, but it's exactly what we'd want

`NATURAL_SORT_KEY` exists in MariaDB 10.7+ but not MySQL as far as I can tell, but it's exactly what we'd want <!-- github-comment-id:4253094719 -->
adam commented 2026-10-05 01:09:50 +02:00
Author
Owner
Copy link
Copy source

@plegall I updated the PR to use cat_id. I feel this is the only simple solution for now, and it should be as ordered as we can get based on rank and global rank for a category.

@plegall I updated the PR to use `cat_id`. I feel this is the only simple solution for now, and it should be as ordered as we can get based on rank and global rank for a category. <!-- github-comment-id:4262276365 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#2208