WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 8 months ago

#11823 closed enhancement (fixed)

Improve SQL query used by get_terms()

Reported by: sirzooro Owned by: filosofo
Milestone: 3.7 Priority: normal
Severity: normal Version: 3.0
Component: Taxonomy Keywords: has-patch
Focuses: Cc:

Description

#11076 / [12658] changes get_terms() so it uses SQL query with multiple conditions in WHERE clause - one for each included / excluded term. It will be better to change generated SQL from:

AND (t.term_id = 1 OR t.term_id = 2 OR ...)
AND (t.term_id <> 1 AND t.term_id <> 2 AND ...)

to:

AND t.term_id IN (1, 2, ...)
AND t.term_id NOT IN (1, 2, ...)

Attachments (6)

taxonomy.php.diff (1.6 KB) - added by sirzooro 4 years ago.
taxonomy.php.2.diff (1.9 KB) - added by sirzooro 4 years ago.
no loops this time
11823.diff (2.6 KB) - added by wonderboymusic 8 months ago.
11823.2.diff (2.8 KB) - added by duck_ 8 months ago.
11823.3.diff (3.9 KB) - added by wonderboymusic 8 months ago.
11823.4.diff (2.0 KB) - added by kovshenin 8 months ago.

Download all attachments as: .zip

Change History (17)

comment:1 Denis-de-Bernardy4 years ago

  • Milestone changed from 3.0 to Future Release

comment:2 kevinB4 years ago

  • Cc kevinB added

comment:3 sirzooro4 years ago

  • Keywords has-patch needs-testing added; needs-patch removed

OK, looks that I have to provide a patch for this :)

sirzooro4 years ago

comment:4 dd324 years ago

instead of those ugly loops, something similar to this might be possible?

$interms = array_map('intval', $interms);
$inclusions = ' AND t.term_id IN ( ' . implode(',', $interms) . ' )';

(note, Untested, and havnt looked at the code closer than the patch)

sirzooro4 years ago

no loops this time

wonderboymusic8 months ago

comment:5 wonderboymusic8 months ago

  • Milestone changed from Future Release to 3.7

Refreshed, we should be using IN and NOT IN in most of these old functions that accept arrays of IDs

duck_8 months ago

comment:6 duck_8 months ago

11823.2.diff fixes a bug in the exclude_tree handling (the current patch will only exclude the last tree in the array).

wonderboymusic8 months ago

comment:7 wonderboymusic8 months ago

  • Keywords commit added; needs-testing removed

.3.diff adds unit tests

comment:8 wonderboymusic8 months ago

  • Resolution set to fixed
  • Status changed from new to closed

In 25162:

Improve the include / exclude SQL generation in get_terms() by using IN and NOT IN where applicable. Adds unit tests for include / exclude.

Props sirzooro, duck_.

Fixes #11823.

comment:9 wonderboymusic8 months ago

In 25169:

No need to re-map the return values of wp_parse_id_list() to int.

See #22074, #11823.

kovshenin8 months ago

comment:10 kovshenin8 months ago

  • Keywords commit removed
  • Resolution fixed deleted
  • Status changed from closed to reopened

[25162] causes a database error if an include or exclude argument is not empty, but is also not really a term id. 11823.4.diff addresses that.

For reference, here's a sample SQL query it generates (note that 0):

SELECT t.*, tt.* FROM wptests_terms AS t INNER JOIN wptests_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag')0 ORDER BY t.name ASC
Last edited 8 months ago by kovshenin (previous) (diff)

comment:11 wonderboymusic8 months ago

  • Resolution set to fixed
  • Status changed from reopened to closed

In 25257:

Avoid database error when include or exclude is not really a term_id. Adds more unit tests.

Props kovshenin.
Fixes #11823.

Note: See TracTickets for help on using tickets.