多站点环境下按用户角色跨站点获取文章的查询需求

Got it, let's tackle this query problem based on your specific site and role scenario. From what you described, you need to pull articles across multiple sites (A, B, C) filtered by specific author roles—right now you can only get them per site, but want a single query to cover cross-site roles.

First, I'll make some reasonable assumptions about your data structure (since you didn't share table schemas), then provide two common scenario solutions.

Assumed Data Structure #

Let's say either:

• You have a single unified table (e.g., article_index) that tracks all articles across sites, with fields like:
  • site_name (values: 'A', 'B', 'C')
  • author_role (values: 'site_admin', 'site_editor')
  • article_id, article_title, publish_date (other relevant article fields)
• Or you have separate tables per site (e.g., site_a_articles, site_b_articles, site_c_articles), each with the same core fields plus role info.

Solution 1: Unified Article Index Table #

If all site articles live in one table, this is straightforward. Use a WHERE clause to target your desired site-role combinations:

Basic Filtered Query #

SELECT article_id, article_title, publish_date, site_name, author_role
FROM article_index
WHERE 
    -- Grab all articles by Site A's admin
    (site_name = 'A' AND author_role = 'site_admin')
    -- Add Site B's editor articles
    OR (site_name = 'B' AND author_role = 'site_editor')
    -- Add Site C's editor articles
    OR (site_name = 'C' AND author_role = 'site_editor')
ORDER BY publish_date DESC;

Simplified with IN Clause #

For cleaner code (especially if you have more site-role pairs), use an IN tuple:

SELECT article_id, article_title, publish_date, site_name, author_role
FROM article_index
WHERE (site_name, author_role) IN (
    ('A', 'site_admin'),
    ('B', 'site_editor'),
    ('C', 'site_editor')
)
ORDER BY publish_date DESC;

Solution 2: Separate Per-Site Tables #

If each site has its own article index table, use UNION ALL to combine results from each table. This ensures you pull exactly the roles you want from each site:

-- Get Site A admin articles
SELECT article_id, article_title, publish_date, 'A' AS site_name, author_role
FROM site_a_articles
WHERE author_role = 'site_admin'

UNION ALL

-- Get Site B editor articles
SELECT article_id, article_title, publish_date, 'B' AS site_name, author_role
FROM site_b_articles
WHERE author_role = 'site_editor'

UNION ALL

-- Get Site C editor articles
SELECT article_id, article_title, publish_date, 'C' AS site_name, author_role
FROM site_c_articles
WHERE author_role = 'site_editor'

ORDER BY publish_date DESC;

Quick Notes: #

• Use UNION instead of UNION ALL if you want to remove duplicate articles (but this adds overhead, so only use if duplicates actually exist).
• Make sure all SELECT statements return the same number of fields with matching data types to avoid errors.

内容的提问来源于stack exchange，提问作者jack