Project

General

Profile

Bug #16835

API search on resources with organization_id fails with SQL error

Added by Bryan Kearney about 4 years ago. Updated over 2 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Organizations and Locations
Target version:
Difficulty:
Triaged:
Bugzilla link:
Fixed in Releases:
Found in Releases:

Description

Cloned from https://bugzilla.redhat.com/show_bug.cgi?id=1375788

Description of problem:

API get on ptables with organization ID fails with SQL error

Version-Release number of selected component (if applicable): 6.2

How reproducible: Always

Steps to Reproduce:
1. Search for ptables works correctly, but throws an error when used with organization id.
2.https://satellite.example.com/api/v2/ptables?search=FreeBSD&organization_id=1

Actual results: api output displayed with error

Expected results: Should display the output of the searched string in ptables

Additional info:

Actual error:

{
"error": {"message":"PG::Error: ERROR: missing FROM-clause entry for table \"organizations_templates\"\nLINE 1: ... t1_r9, \"taxonomies\".\"katello_default\" AS t1_r10, \"organizat...\n ^\n: SELECT \"templates\".\"id\" AS t0_r0, \"templates\".\"name\" AS t0_r1, \"templates\".\"template\" AS t0_r2, \"templates\".\"snippet\" AS t0_r3, \"templates\".\"template_kind_id\" AS t0_r4, \"templates\".\"created_at\" AS t0_r5, \"templates\".\"updated_at\" AS t0_r6, \"templates\".\"locked\" AS t0_r7, \"templates\".\"default\" AS t0_r8, \"templates\".\"vendor\" AS t0_r9, \"templates\".\"type\" AS t0_r10, \"templates\".\"os_family\" AS t0_r11, \"templates\".\"job_category\" AS t0_r12, \"templates\".\"provider_type\" AS t0_r13, \"templates\".\"description_format\" AS t0_r14, \"taxonomies\".\"id\" AS t1_r0, \"taxonomies\".\"name\" AS t1_r1, \"taxonomies\".\"type\" AS t1_r2, \"taxonomies\".\"created_at\" AS t1_r3, \"taxonomies\".\"updated_at\" AS t1_r4, \"taxonomies\".\"ignore_types\" AS t1_r5, \"taxonomies\".\"description\" AS t1_r6, \"taxonomies\".\"label\" AS t1_r7, \"taxonomies\".\"ancestry\" AS t1_r8, \"taxonomies\".\"title\" AS t1_r9, \"taxonomies\".\"katello_default\" AS t1_r10, \"organizations_templates\".\"id\" AS t2_r0, \"organizations_templates\".\"name\" AS t2_r1, \"organizations_templates\".\"type\" AS t2_r2, \"organizations_templates\".\"created_at\" AS t2_r3, \"organizations_templates\".\"updated_at\" AS t2_r4, \"organizations_templates\".\"ignore_types\" AS t2_r5, \"organizations_templates\".\"description\" AS t2_r6, \"organizations_templates\".\"label\" AS t2_r7, \"organizations_templates\".\"ancestry\" AS t2_r8, \"organizations_templates\".\"title\" AS t2_r9, \"organizations_templates\".\"katello_default\" AS t2_r10 FROM \"templates\" INNER JOIN \"taxable_taxonomies\" ON \"taxable_taxonomies\".\"taxable_id\" = \"templates\".\"id\" AND \"taxable_taxonomies\".\"taxable_type\" = 'Ptable' INNER JOIN \"taxonomies\" ON \"taxonomies\".\"id\" = \"taxable_taxonomies\".\"taxonomy_id\" AND \"taxonomies\".\"type\" = 'Organization' LEFT OUTER JOIN \"taxable_taxonomies\" \"taxable_taxonomies_templates_join\" ON \"taxable_taxonomies_templates_join\".\"taxable_id\" = \"templates\".\"id\" AND \"taxable_taxonomies_templates_join\".\"taxable_type\" = 'Ptable' LEFT OUTER JOIN \"taxonomies\" \"locations_templates\" ON \"locations_templates\".\"id\" = \"taxable_taxonomies_templates_join\".\"taxonomy_id\" AND \"locations_templates\".\"type\" = 'Location' WHERE \"templates\".\"type\" IN ('Ptable') AND \"taxonomies\".\"type\" IN ('Organization') AND ((\"taxonomies\".\"id\" = 1 OR \"taxonomies\".\"title\" = '1')) AND ((\"taxonomies\".\"name\" ILIKE '%FreeBSD%' OR \"taxonomies\".\"name\" ILIKE '%FreeBSD%' OR \"templates\".\"name\" ILIKE '%FreeBSD%' OR \"templates\".\"template\" ILIKE '%FreeBSD%' OR \"templates\".\"template\" ILIKE '%FreeBSD%' OR \"templates\".\"os_family\" ILIKE '%FreeBSD%')) AND \"templates\".\"id\" IN (58) ORDER BY \"templates\".\"name\" ASC NULLS FIRST"}
}


Related issues

Has duplicate Foreman - Bug #16901: [Foreman] API : GET on ptables with organization ID fails with SQL errorDuplicate2016-10-13

Associated revisions

Revision 8fe2ad71 (diff)
Added by Tomer Brisker almost 3 years ago

Fixes #16835 - Allow implicit search with org_id

Previously, any resource with many taxonomies would fail when using
implicit search when passing organization_id parameter to the api. This
is due to scoped_search incorrectly adding the taxonomy table to search
on, which was conflicting with the current taxonomy scope. Removing the
implicit search on taxonomies fixes the issue while still allowing users
to explicitly search on location or organization if they want to
(although it makes no sense to use both options).

History

#1 Updated by Dominic Cleal about 4 years ago

  • Category changed from Unattended installations to API

#2 Updated by Dominic Cleal about 4 years ago

  • Has duplicate Bug #16901: [Foreman] API : GET on ptables with organization ID fails with SQL error added

#3 Updated by The Foreman Bot about 4 years ago

  • Status changed from New to Ready For Testing
  • Pull request https://github.com/theforeman/foreman/pull/3941 added

#4 Updated by Ivan Necas almost 4 years ago

  • Category changed from API to Organizations and Locations

#5 Updated by Tomer Brisker almost 3 years ago

  • Assignee set to Tomer Brisker
  • Status changed from Ready For Testing to Assigned
  • Subject changed from API get on ptables with organization ID fails with SQL error to API search on resources with organization_id fails with SQL error
  • Pull request deleted (https://github.com/theforeman/foreman/pull/3941)

this occurs on all has_many organizations resources, not just ptables.

#6 Updated by The Foreman Bot almost 3 years ago

  • Status changed from Assigned to Ready For Testing
  • Pull request https://github.com/theforeman/foreman/pull/4964 added

#7 Updated by Anonymous almost 3 years ago

  • % Done changed from 0 to 100
  • Status changed from Ready For Testing to Closed

#8 Updated by Marek Hulán almost 3 years ago

  • Legacy Backlogs Release (now unused) set to 296

Also available in: Atom PDF