在我的 WordPress 數(shù)據(jù)庫中，我有三個(gè)表 wp_companies，它存儲(chǔ)一些公司信息和 wp_product_types，它存儲(chǔ)一堆公司可以自行分配的產(chǎn)品類型，然后我有 wp_company_products，它用于使用 ID 將產(chǎn)品類型分配給公司。下面是數(shù)據(jù)庫外觀的示例：wp_公司id | company_name | member_type | logo-----------------------------------------------------------------1 | Google | full | https://via.placeholder.com/150-----------------------------------------------------------------2 | Crunchyroll | full | https://via.placeholder.com/150wp_產(chǎn)品id | product_name |----------------------1 | Car Insurance |----------------------2 | House Insurance |----------------------3 | Life Insurance |wp_company_productsid | company_id | product_id ----------------------------1 | 1 | 2----------------------------2 | 2 | 1----------------------------3 | 1 | 3這是我當(dāng)前的 MySQL 查詢，它只顯示 wp_companies 中的數(shù)據(jù) add_action('rest_api_init', function() { register_rest_route('custom-routes/v1', 'members', array( 'methods' => 'GET', 'callback' => 'get_members' ) ); }); function get_members($data) { global $wpdb; $query = $wpdb->get_results( "SELECT company_name, member_type, logo, site_url FROM {$wpdb->prefix}companies ORDER BY RAND()" ); foreach ( $query as $member ) { $member_data[] = array( "company_name" => $member->company_name, "member_type" => $member->member_type, "logo" => $member->logo, ); } return $member_data; }這會(huì)在我的 api 端點(diǎn)上顯示我的數(shù)據(jù)：[ { "company_name":"Google", "member_type":"full", "logo":"https://via.placeholder.com/150", }, { "company_name":"Crunchyroll", "member_type":"full", "logo":"https://via.placeholder.com/150", }]我如何構(gòu)建我的 MySQL 查詢才能實(shí)現(xiàn)這一目標(biāo)？
1 回答
呼如林
TA貢獻(xiàn)1798條經(jīng)驗(yàn) 獲得超3個(gè)贊
請(qǐng)?jiān)囋囘@個(gè)
function get_members($data) {
global $wpdb;
$query = $wpdb->get_results( "SELECT company_name, member_type, logo, site_url FROM {$wpdb->prefix}companies ORDER BY RAND()" );
foreach ( $query as $member ) {
$productQuery = $wpdb->get_results( "SELECT product_name FROM wp_products WHERE id IN (SELECT product_id from wp_company_products WHERE compony_id = '{$member->id}') " );
$products = array();
foreach ( $productQuery as $prduct ) {
array_push($products ,$prduct->product_name);
}
$member_data[] = array(
"company_name" => $member->company_name,
"member_type" => $member->member_type,
"logo" => $member->logo,
"products" => $products
);
}
return $member_data;
}
- 1 回答
- 0 關(guān)注
- 106 瀏覽
添加回答
舉報(bào)
0/150
提交
取消