Bug #16835
API search on resources with organization_id fails with SQL error
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
Associated revisions
History
#1
Updated by Dominic Cleal over 4 years ago
- Category changed from Unattended installations to API
#2
Updated by Dominic Cleal over 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 over 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 about 4 years ago
- Category changed from API to Organizations and Locations
#5
Updated by Tomer Brisker about 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 about 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 about 3 years ago
- % Done changed from 0 to 100
- Status changed from Ready For Testing to Closed
Applied in changeset 8fe2ad71956270ec71705bdbd11934387144ca7d.
#8
Updated by Marek Hulán about 3 years ago
- Legacy Backlogs Release (now unused) set to 296
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).