Как правильно создать такой SQL запрос?

Question

[Dvoeglazov]

Есть 3 связанные таблицы:

spoiler

Таблица users_signin

id Первичный int(11)
oauth_provider enum('', 'linkedin')
oauth_uid varchar(100)
first_name varchar(50)
last_name varchar(50)
email varchar(100)
location varchar(255)
picture varchar(255)
profile_url varchar(255)
created datetime
industry varchar(255)

Таблица users_positions

users_id int(11)
unique_id int(11)
location varchar(255)
title varchar(255)
startDate varchar(255)
company_id int(11)

users_id - связь с users_signin, как id
company_id - связь с users_company, как id

Таблица users_company

id Первичный int(11)
unique_id int(11)
industry varchar(255)
name varchar(255)
type varchar(255)

Мой SQL запрос:

$rows = $wpdb->get_results( 
            "SELECT 
            nam.id, 
            nam.first_name, 
            nam.last_name, 
            nam.email, 
            nam.location, 
            nam.picture, 
            nam.profile_url, 
            nam.industry, 
            nam.created, 
            pos.location as loc_work, 
            pos.title as profession, 
            com.name as com_name, 
            com.industry as com_inds 
            from 
            $table_name nam, 
            $table_positions pos, 
            $table_company com 
            WHERE nam.oauth_provider = 'linkedin' 
            AND nam.id = pos.users_id 
            AND pos.company_id = com.id 
            limit  $offset, $limit" 
        );

Вывод информации:

spoiler

Array
(
[0] => stdClass Object
(
[id] => 1
[first_name] => Хабиб
[last_name] => Макгрэгович
[email] => [e-mail]
[location] => United States
[picture] => https://media.licdn.com/dms/image/C4D03AQF7xeYVfWV...
[profile_url] => www.linkedin.com/in/%D1%85%D0%B0%D0%B1%D0%B8%D0%B1...
[industry] => Information Technology and Services
[created] => 2018-10-04 23:13:00
[loc_work] => Saint Petersburg, Russian Federation
[profession] => Frontend Engineer
[com_name] => Paladin Engineering
[com_inds] => Информационные технологии и услуги
)

[1] => stdClass Object
(
[id] => 1
[first_name] => Хабиб
[last_name] => Макгрэгович
[email] => [e-mail]
[location] => United States
[picture] => https://media.licdn.com/dms/image/C4D03AQF7xeYVfWV...
[profile_url] => www.linkedin.com/in/%D1%85%D0%B0%D0%B1%D0%B8%D0%B1...
[industry] => Information Technology and Services
[created] => 2018-10-04 23:13:00
[loc_work] =>
[profession] => Капитан команды
[com_name] => Яндекс Терра
[com_inds] => Нефтяная и энергетическая промышленность
)

)

Вопрос:

Как избежать дублирования информации?
Я хочу, чтобы SQL ответ был таким:

[0] => stdClass Object
(
[id] => 1
[first_name] => Хабиб
[last_name] => Макгрэгович
[email] => [e-mail]
[location] => United States
[picture] => https://media.licdn.com/dms/image/C4D03AQF7xeYVfWV...
[profile_url] => www.linkedin.com/in/%D1%85%D0%B0%D0%B1%D0%B8%D0%B1...
[industry] => Information Technology and Services
[created] => 2018-10-04 23:13:00
[loc_work] => Array( тут 1 и более )
[profession] => Array(тут 1 и более )
[com_name] => Array(тут 1 и более )
[com_inds] => Array(тут 1 и более )
)

Или мне нужно средствами php делать такой массив?

Answer 1

[Jorik86]

я так понял в результирующий массив вы сразу хотите группировать значения по аттрибутам.

[loc_work] => Array( тут 1 и более )
[profession] => Array(тут 1 и более )
[com_name] => Array(тут 1 и более )
[com_inds] => Array(тут 1 и более )

Для этого вам может подойти group_concat
https://dev.mysql.com/doc/refman/8.0/en/group-by-f...

Comments

[Dvoeglazov]

Спасибо, а можно ли SQL запросом получить такую структуру:

spoiler

Array
(
    [id] => apple
    [name] => banana
    [properties] => Array
        (
            [prop_name] => x
            [count] => y
            [prop_val] => z
        )
)

[Dvoeglazov]

Т.е. не строку, как в вашем ответе, а массив или объект

[Jorik86]

нет. такое возможно в документированных базах. MongoDb например

[ThunderCat]

Dvoeglazov, нет, бд всегда отдает наборы строк.

[Dvoeglazov]

Понял, спасибо.

Answer 2

[Максим Тимофеев]

Если есть связи они должны быть описаны, я так понимаю Вам нужен join

Comments

[Dvoeglazov]

Мне не важно массив или json. Полученную информацию я буду выводить через цикл, вот весь код скрипта:

spoiler

<?php

    function wp_schools_list() {
        global $wpdb;
        $table_name = "users_signin";
        $table_positions = 'users_positions';
        $table_company = 'users_company';



        $pagenum = isset( $_GET['pagenum'] ) ? absint( $_GET['pagenum'] ) : 1;      

        $limit = 20; // number of rows in page
        $offset = ( $pagenum - 1 ) * $limit;
        $total = $wpdb->get_var( "select count(*) as total from $table_name WHERE oauth_provider = 'linkedin'" );
        $num_of_pages = ceil( $total / $limit );

        $rows = $wpdb->get_results( 
            "SELECT 
            nam.id, 
            nam.first_name, 
            nam.last_name, 
            nam.email, 
            nam.location, 
            nam.picture, 
            nam.profile_url, 
            nam.industry, 
            nam.created, 
            pos.location as loc_work, 
            pos.title as profession, 
            com.name as com_name, 
            com.industry as com_inds 
            from 
            $table_name nam, 
            $table_positions pos, 
            $table_company com 
            WHERE nam.oauth_provider = 'linkedin' 
            AND nam.id = pos.users_id 
            AND pos.company_id = com.id 
            limit  $offset, $limit" 
        );
        $rowcount = $wpdb->num_rows;
        
        echo "<pre>";
        print_r($rows);


    ?>
        <!-- <link type="text/css" href="<?php #echo WP_PLUGIN_URL; ?>mywp/sinetiks-schools/style-admin.css" rel="stylesheet" /> -->
        <div class="wrap abs">
           
            
            <table class='wp-list-table widefat fixed striped posts'>
                <tr>
                    <th class="manage-column ss-list-width">#</th>
                    <th class="manage-column ss-list-width">Full Name</th>
                    <th class="manage-column ss-list-width">Email</th>
                    <th class="manage-column ss-list-width">Location</th>
                    <th class="manage-column ss-list-width">Picture</th>
                    <th class="manage-column ss-list-width">Industry</th>
                    <th class="manage-column ss-list-width">location_work</th>
                    <th class="manage-column ss-list-width">Profession</th>
                    <th class="manage-column ss-list-width">Company name</th>
                    <th class="manage-column ss-list-width">Company industry</th>
                    <th class="manage-column ss-list-width">Created</th>
                    
                </tr>
                <?php
                if($rowcount>0){
                $i = $offset;
                foreach ($rows as $row) { $i++; ?>
                    <tr>
                        <td class="manage-column ss-list-width"><?php echo $i; ?></td>
                        <td class="manage-column ss-list-width">
                            <a target="_blank" href="<?php echo $row->profile_url; ?>">
                                <?php echo $row->first_name . ' '. $row->last_name; ?>
                            </a>
                        </td>

                        <td class="manage-column ss-list-width"><?php echo $row->email; ?></td>
                        <td class="manage-column ss-list-width"><?php echo $row->location; ?></td>

                        <td class="manage-column ss-list-width">
                            <a target="_blank" href="<?php echo $row->profile_url; ?>">
                                <img src="<?php echo $row->picture; ?>" alt="">
                            </a>
                        </td>

                        <td class="manage-column ss-list-width"><?php echo $row->industry; ?></td>
                        <td class="manage-column ss-list-width"><?php echo $row->loc_work; ?></td>
                        <td class="manage-column ss-list-width"><?php echo $row->profession; ?></td>
                        <td class="manage-column ss-list-width"><?php echo $row->com_name; ?></td>
                        <td class="manage-column ss-list-width"><?php echo $row->com_inds; ?></td>
                        <td class="manage-column ss-list-width"><?php echo $row->created; ?></td>
                       
                    </tr>
                <?php } }else{
                    echo "<tr><td cols=an='5'>No records found</td></tr>";
                } ?>
            </table>
        </div>
        <?php

        $page_links = paginate_links( array(
            'base' => add_query_arg( 'pagenum', '%#%' ),
            'format' => '',
            'prev_text' => __( '&laquo;', 'text-domain' ),
            'next_text' => __( '&raquo;', 'text-domain' ),
            'total' => $num_of_pages,
            'current' => $pagenum
        ) );

        if ( $page_links ) {
            echo '<div class="tablenav" style="width: 99%;"><div class="tablenav-pages" style="margin: 1em 0">' . $page_links . '</div></div>';
        }
}

wp_schools_list();

Там следует обратить внимание на SQL запрос и Цикл, остальное не важно (использую wordpress)

[Дмитрий]

Dvoeglazov, не "json" а "join")))

[Максим Тимофеев]

Dvoeglazov, ))) JSON не стоит пихать в SQL, это точно. Как и COIN или JJABRAMS и другие слова составленные путем замены и перестановки букв. Но я написал про JOIN, который является оператором языка sql

[alexalexes]

Посмотрите мой ответ на вопрос.
У вас в точности по аналогии будет решение.
Как произвести правельную выборку статей со всеми тегами?

[Dvoeglazov]

alexalexes, Спасибо сейчас буду разбирать ваш ответ

[Dvoeglazov]

Join не позволяет реализовать вложенность. С join будет тоже самое

[Dvoeglazov]

Вот например вывел массив $wpdb:

spoiler

wpdb Object
(
    [show_errors] => 
    [suppress_errors] => 
    [last_error] => 
    [num_queries] => 20
    [num_rows] => 2
    [rows_affected] => 0
    [insert_id] => 0
    [last_query] => SELECT 
            nam.id, 
            nam.first_name, 
            nam.last_name, 
            nam.email, 
            nam.location, 
            nam.picture, 
            nam.profile_url, 
            nam.industry, 
            nam.created, 
            pos.location as loc_work, 
            pos.title as profession, 
            com.name as com_name, 
            com.industry as com_inds 
            from 
            users_signin nam, 
            users_positions pos, 
            users_company com 
            WHERE nam.oauth_provider = 'linkedin' 
            AND nam.id = pos.users_id 
            AND pos.company_id = com.id 
            limit  0, 20
    <b>[last_result] => Array
        (</b>
            [0] => stdClass Object
                (
                    [id] => 1
                    [first_name] => Хабиб
                    [last_name] => Макгрэгович
                    [email] => [e-mail]
                    [location] => United States
                    [picture] => https://media.licdn.com/dms/image/C4D03AQF7xeYVfWVd0Q/profile-displayphoto-shrink_100_100/0?e=1544054400&v=beta&t=bCAj7ixxdIj7Mmk2pH1iIdTGUK7VZLPKcBXxEK5Grts
                    [profile_url] => http://www.linkedin.com/in/%D1%85%D0%B0%D0%B1%D0%B8%D0%B1-%D0%BC%D0%B0%D0%BA%D0%B3%D1%80%D1%8D%D0%B3%D0%BE%D0%B2%D0%B8%D1%87-9b57a412b
                    [industry] => Information Technology and Services
                    [created] => 2018-10-04 23:13:00
                    [loc_work] => Saint Petersburg, Russian Federation
                    [profession] => Frontend Engineer
                    [com_name] => Paladin Engineering
                    [com_inds] => Информационные технологии и услуги
                )

            [1] => stdClass Object
                (
                    [id] => 1
                    [first_name] => Хабиб
                    [last_name] => Макгрэгович
                    [email] => [e-mail]
                    [location] => United States
                    [picture] => https://media.licdn.com/dms/image/C4D03AQF7xeYVfWVd0Q/profile-displayphoto-shrink_100_100/0?e=1544054400&v=beta&t=bCAj7ixxdIj7Mmk2pH1iIdTGUK7VZLPKcBXxEK5Grts
                    [profile_url] => http://www.linkedin.com/in/%D1%85%D0%B0%D0%B1%D0%B8%D0%B1-%D0%BC%D0%B0%D0%BA%D0%B3%D1%80%D1%8D%D0%B3%D0%BE%D0%B2%D0%B8%D1%87-9b57a412b
                    [industry] => Information Technology and Services
                    [created] => 2018-10-04 23:13:00
                    [loc_work] => 
                    [profession] => Капитан команды
                    [com_name] => Яндекс Терра
                    [com_inds] => Нефтяная и энергетическая промышленность
                )

        )

Жирным выделил массив, он вложенный. Я хочу сделать тоже самое только с полями [loc_work]=> Array(элементы) и т.д.

[vman]

Такие вещи делаются ORM автоматически, PDO возвращает плоские объекты представляющие строки. Пример того как можно реализовать то, что вы хотите https://gist.github.com/vmanyushin/39d6ac7d86e6b9e...

Answer 3

[Павел Новиков]

делаете JOIN и выбираете все данные которые вам нужны из всех таблиц