Cargando objetos para usuarios, roles y grupos de una consulta con uniones externas izquierda -- php campo con mysql campo con json campo con mysqli campo con join camp codereview Relacionados El problema

Loading objects for users, roles, and groups from a query with LEFT OUTER JOINs


4
vote

problema

Español

Tengo esta función que me devuelve una lista de usuarios con sus roles y grupos. Como puede ver, así es como busco y creo la lista de objetos. Me pregunto si este es un buen enfoque y qué partes deben mejorarse. No estoy tan experimentado con PHP, así que apreciaría las muestras de código.

También me pregunto qué tan bueno es un enfoque para obtener primero a todos los usuarios y luego hacer otra declaración preparada para obtener roles y grupos de usuarios. Eso significaría que tendré un gran número de llamadas de base de datos, así que creo que es una mala idea.

      $stmt = $mysqli->prepare("SELECT u.id, u.firstName, u.lastName, u.email,          u.phoneNumber, u.address, u.birthDate, ur.roleName, cg.id, cg.name FROM users as u          LEFT OUTER JOIN user_role as ur ON u.id = ur.userId          LEFT OUTER JOIN user_group as ug on ug.userId = u.id          LEFT OUTER JOIN control_group as cg on cg.id = ug.groupId WHERE u.id != ?");     $stmt->bind_param("i", $_SESSION["id"]);     $stmt->execute();     $stmt->bind_result($id, $firstName, $lastName, $email, $phoneNumber,          $address, $birthDate, $roleName, $groupId, $groupName);     $users = array();      while ($stmt->fetch()) {         if (empty($users[$id])) {             $users[$id] = array(                 'id' => $id,                 'firstName' => $firstName,                 'lastName' => $lastName,                 'email' => $email,                 'phoneNumber' => $phoneNumber,                 'address' => $address,                 'birthDate' => $birthDate,                 'roles' => array(),                 'groups' => array()             );         }         if ($roleName) {             $found = false;             foreach ($users[$id]['roles'] as $role) {                 if($role['roleName'] == $roleName){                     $found = true;                     break;                 }             }             if($found == false)                 $users[$id]['roles'][] = array(                     'roleName' => $roleName                 );          }          if ($groupId) {             $found = false;             foreach ($users[$id]['groups'] as $group) {                 if($group['groupName'] == $groupName){                     $found = true;                     break;                 }             }             if($found == false)                 $users[$id]['groups'][] = array(                     'groupName' => $groupName                 );          }     }      $stmt->close();     $mysqli->close();     echo json_encode($users);   

Esta es la respuesta que recibo , lo único que quiero mejorar es el índice de elementos, como usted Puede ver en mi ejemplo, ya que el índice tengo ID de artículo, me gustaría obtener un índice correcto a partir de 0.

Original en ingles

I have this function that returns me a list of users with their roles and groups. As you can see, this is how I fetch and create list of objects. I'm wondering whether this is a good approach and what parts should be improved. I'm not that experienced with PHP so I would appreciate code samples.

I'm also wondering how good an approach it is to first get all users and then make another prepared statement to get user roles and groups. That would mean I will have big number of database calls, so I think it's a bad idea.

    $stmt = $mysqli->prepare("SELECT u.id, u.firstName, u.lastName, u.email,          u.phoneNumber, u.address, u.birthDate, ur.roleName, cg.id, cg.name FROM users as u          LEFT OUTER JOIN user_role as ur ON u.id = ur.userId          LEFT OUTER JOIN user_group as ug on ug.userId = u.id          LEFT OUTER JOIN control_group as cg on cg.id = ug.groupId WHERE u.id != ?");     $stmt->bind_param("i", $_SESSION["id"]);     $stmt->execute();     $stmt->bind_result($id, $firstName, $lastName, $email, $phoneNumber,          $address, $birthDate, $roleName, $groupId, $groupName);     $users = array();      while ($stmt->fetch()) {         if (empty($users[$id])) {             $users[$id] = array(                 'id' => $id,                 'firstName' => $firstName,                 'lastName' => $lastName,                 'email' => $email,                 'phoneNumber' => $phoneNumber,                 'address' => $address,                 'birthDate' => $birthDate,                 'roles' => array(),                 'groups' => array()             );         }         if ($roleName) {             $found = false;             foreach ($users[$id]['roles'] as $role) {                 if($role['roleName'] == $roleName){                     $found = true;                     break;                 }             }             if($found == false)                 $users[$id]['roles'][] = array(                     'roleName' => $roleName                 );          }          if ($groupId) {             $found = false;             foreach ($users[$id]['groups'] as $group) {                 if($group['groupName'] == $groupName){                     $found = true;                     break;                 }             }             if($found == false)                 $users[$id]['groups'][] = array(                     'groupName' => $groupName                 );          }     }      $stmt->close();     $mysqli->close();     echo json_encode($users); 

This is the response I get, only thing that I want improve is item index, as you can see in my example as index I have item id, I would like to get correct index starting from 0.

              
   
   

Lista de respuestas

1
 
vote
vote
La mejor respuesta
 

Cuando dice que desea "El índice correcto a partir de 0", ¿significa esto que desea que la primera dimensión en su estructura de datos sea 0 a N-1 (donde n es el número de objetos de usuario devueltos)?

Si ese es el caso, realmente debería estar construyendo una matriz de objetos de usuario indexados numéricamente, no una matriz de objeto / asociación con la ID de usuario como la primera clave dimensional.

algunos pensamientos en su código:

  • Consideraría encarecidamente alejarse de camelcasando los nombres de las entidades de su base de datos (tabla, columnas, etc.). Esto puede ahorrarle problema por el hecho de que, en la mayoría de los casos con MySQL, estas entidades de base de datos se tratan sin consideración para el caso (como en archivos en su sistema y demás). El uso de Snake_case es generalmente el enfoque preferido para la mayoría de los sistemas de bases de datos relacionales para evitar problemas inesperados derivados de dicho nombramiento.
  • Sé consistente en las piezas de consulta de todas las carcasas que no son entidades de base de datos - AS , ON1 , etc. Además de los obvios, como SELECT < / Código>, FROM3 , etc. En este momento está mezclando el uso para on .
  • Tomemos tanto espacio vertical como lo necesites al escribir tus consultas. No obtienes puntos de bonificación por tratar de mantenerlo en la medida de lo posible. Errar en el lado de hacer todo lo que está en su código más legible.
  • Consideraría aliasizar los nombres de campo de retorno en su consulta para que pueda alejarse de los resultados de la consulta de unión con variables que están disponibles en el alcance principal de este script. Para mí, comienza a perder el concepto de trabajar con una fila de datos en su enfoque actual.
  • Puede considerar usar fetch_object() en combinación con el aliasing mencionado anteriormente, para darle una forma agradable y legible de trabajar con cada fila en el conjunto de resultados. Esto también mostrará los valores nulos en el establecimiento de resultados en los valores verdaderos null en el objeto fila resultante.
  • Debe usar ORDER BY CLAUSES Cada vez que tiene casos en los que va a visitar las filas planas de un lugar en una estructura de datos jerárquicos. Esto le permite simplemente buscar cambios en los valores de columna al iterar la estructura para activar la necesidad de crear nuevas estructuras de datos infantiles.
  • Si realmente trabaja con objetos cuando lee los datos en su estructura final y use una cláusula 998877666555443388 , debe simplificar su fila de resultados a la lógica de asignación de estructura de datos.
  • ¿Por qué ser redundantes en sus datos de respuesta con las etiquetas roleName ON0 cuando ya están anidados en ON1 y ON2 < / Código> Arrays?
  • ¿Por qué recuperar la identificación del grupo? Si no lo va a usar en la estructura de respuesta resultante?

Poner esto todos juntos, podría tener código más como este:

  ON3  

Tenga en cuenta que esto es un ejemplo simple y no tiene un manejo adecuado de errores en torno a la preparación y ejecución de la declaración. Debe asegurarse de que simplemente no asuma que estas cosas funcionen. Asegúrese de comprender todos los resultados posibles y / o excepciones que pueden ocurrir a partir de una función / llamada de método y manejar esos resultados en consecuencia.

 

When you say you want "correct index starting from 0", does this mean that you want the first dimension in your data structure to be 0 to n-1 (where n is number of user objects returned)?

If that is the case, you really should be building a numerically-indexed array of user objects, not an object/associative array with the user id as the first dimensional key.

Some thoughts on your code:

  • I would strongly consider moving away from camelCasing the names of your database entities (table, columns, etc.). This can save you from problem around the fact that, in most cases with MySQL, these database entities are treated without consideration for case (like in files on your system and such). Using snake_case is generally the preferred approach for most relational database systems to avoid unexpected problems arising from such naming.
  • Be consistent on upper-casing all query parts that are not database entities - AS, ON, etc. in addition to the obvious ones like SELECT, FROM, etc. Right now you are mixing usage for on.
  • Take as much vertical space as you need when writing your queries. You get no bonus points for trying to keep it on as few lines as possible. Err on the side of making everything in your code more readable.
  • I would consider aliasing the return field names in your query so you can move away from binding query results with variables that are available in the main scope of this script. To me you begin to lose the concept of working with a row of data in your current approach.
  • You might consider using fetch_object() in combination with the aliasing noted above, to give you a nice, readable way to work with each row in the result set. This will also map null values in result set to true null values on the resulting row object.
  • You should use ORDER BY clauses whenever you have cases where you are going to need to map flat rows from a result set into a hierarchical data structure. This allows you to simply look for changes in column values when iterating the structure to trigger the need to create a new child data structures.
  • If you truly work with objects when reading data into your final structure and use an ORDER BY clause, you should be able to simplify your result row to data structure mapping logic.
  • Why be redundant in your response data with having labels roleName and groupName when they are already nested in roles and groups arrays?
  • Why retrieve group id at all if you are not going to use it in the resulting response structure?

Putting this all together you might have code more like this:

$query = " SELECT     u.id AS id,     u.first_name AS first_name,     u.last_name AS last_name ,     u.email As email,     u.phone_number AS phone_number,      u.address AS address,     u.birth_date AS birth_date,     ur.roleName AS role_name,     cg.name AS group_name FROM users AS u  LEFT OUTER JOIN user_role AS ur ON u.id = ur.user_id  LEFT OUTER JOIN user_group AS ug ON ug.user_id = u.id  LEFT OUTER JOIN control_group AS cg ON cg.id = ug.group_id WHERE u.id != ? ORDER BY id ASC, role_name ASC, group_name ASC "; $stmt = $mysli->prepare($query); $stmt->bind_param("i", $_SESSION["id"]); $stmt->execute();  // and then fetch rows $users = array(); $user = new stdClass(); $user->id = 0; while ($row = $stmt->fetch_object()) {     // build new user if needed     if((int)$row->id !== $user->id) {         // Break existing reference between previous $user and $users.         // Data in $users will remain after this dereferencing         unset($user);         $user = new stdClass();         $user->id = (int)$row->id;         $user->firstName = $row->first_name;         $user->lastName = $row->last_name;         $user->email = $row->email;         $user->phoneNumber = $row->phone_number;         $user->address = $row->address;         $user->birthDate = $row->birth_date;         $user->roles = array();         $user->groups = array();         // Assign this new user object by reference to $users.         // This allows you to simply work with $user here in loop         // as opposed to $users[$index] which requires you to manually         // track index values.         $users[] =& $user;          // since this is a new user object, we need to reset         // role and group objects so we can detect changes in these columns.         unset($role);         $role = new stdClass();         $role->name = null;         unset($group);         $group = new stdClass();         $group->name = null;     }     // build new role if needed     if($row->role_name !== $role->name) {         // dereference role         unset($role);         // create new role for this user         $role = new stdClass();         $contact->name = $row->role_name;         $user->roles[] =& $role;     }     // build new group if needed     if($row->group_name !== $group->name) {         // dereference group         unset($group);         // create new group for this user         $group = new stdClass();         $group->name = $row->group_name;         $user->groups[] =& $group;     } } unset($user, $role, $group);  $stmt->close(); $mysqli->close(); echo json_encode($users); 

Note that this is simple example and doesn't have proper error handling around statement preparation and execution. You should make sure that you just don't assume these things work. Make sure you understand all possible results and/or exceptions that can occur from a function/method call and handle those outcomes accordingly.

 
 
       
       
2
 
vote

Para el caso exacto que publicó aquí, sugeriría usar GROUP_CONCAT () en la consulta. Reducirá drásticamente la cantidad de código requerido, así como la cantidad de datos entregados de la base de datos.

Además, veo muy poco uso para la declaración preparada aquí, por lo que la cantidad de código podría reducirse drásticamente en esta cuenta también.

  $sql = "SELECT u.id, u.firstName, u.lastName, u.email, u.phoneNumber,      u.address, u.birthDate,group_concat(ur.roleName), roles group_concat(cg.name) groups      FROM users as u      LEFT OUTER JOIN user_role as ur ON u.id = ur.userId      LEFT OUTER JOIN user_group as ug on ug.userId = u.id      LEFT OUTER JOIN control_group as cg on cg.id = ug.groupId      GROUP BY u.id"; $res = $mysqli->query($sql); $users = array(); while ($row = $res->fetch_assoc()) {     if ($row['id'] == $_SESSION["id"]) {         continue;     }     $row['groups'] = explode(",", $row['groups']);     $row['roles'] = explode(",", $row['roles']);     $users[] = $row; }   

Este es el ejemplo de respuesta que considero más utilizable y claro.
Pero tenga en cuenta que no tomaría problemas para hacerlo exactamente como en su ejemplo anterior, si lo desea.

En un segundo pensamiento, pensando que se avecinó, supongo que es posible que desee hacer grupos y roles de algún tipo de interactivos, permitiendo clics u otras interacciones con ellos. Y para este propósito necesitará identificaciones de grupo y rutadas inevitablemente.

Por lo tanto, para que este código sea robusto y utilizable en la aplicación de la vida real, también tiene que suministrar ID de grupo y rol. Para este propósito, no puedo evitarlo, sino también usar PDO, ya que sus métodos auxiliares hacen mucho su trabajo. Por ejemplo, PDO :: Fetch_Key_PAIR le brinda buenos pares de valor-valor justo al lado de la consulta de SQL:

  $groups = $pdo->query("SELECT id, name FROM control_group")->fetchAll(PDO::FETCH_KEY_PAIR); $roles  = $pdo->query("SELECT id, roleName FROM user_role")->fetchAll(PDO::FETCH_KEY_PAIR);  $sql = "SELECT u.id, u.firstName, u.lastName, u.email, u.phoneNumber,      u.address, u.birthDate,group_concat(ur.roleName), roles group_concat(cg.name) groups      FROM users as u      LEFT OUTER JOIN user_role as ur ON u.id = ur.userId      LEFT OUTER JOIN user_group as ug on ug.userId = u.id      LEFT OUTER JOIN control_group as cg on cg.id = ug.groupId      GROUP BY u.id"; $res = $pdo->query($sql); $users = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) {     if ($row['id'] == $_SESSION["id"]) {         continue;     }      $user_groups = array();     foreach(explode(",", $row['groups'] as $id) {         $user_groups[$id] = $groups[$id]     }     $row['groups'] = $user_groups;      $user_roles = array();     foreach(explode(",", $row['roles']) as $id) {         $user_roles[$id] = $roles[$id]     }      $row['roles'] = $user_roles;     $users[] = $row; }   

Este es un ejemplo de respuesta .

Como puede ver, aquí estamos seleccionando por primera vez todos los nombres de grupo y roles, mientras que en la consulta principal seleccionando solo las ID. Luego, en el bucle corto, finalmente, creando grupos y roles.

 

For the exact case you posted here, I would suggest to use group_concat() in the query. It will dramatically reduce the amount of code required, as well as the amount of data delivered from database.

Besides, I see a very little use for the prepared statement here, so the amount of code could be dramatically reduced on this account as well.

$sql = "SELECT u.id, u.firstName, u.lastName, u.email, u.phoneNumber,      u.address, u.birthDate,group_concat(ur.roleName), roles group_concat(cg.name) groups      FROM users as u      LEFT OUTER JOIN user_role as ur ON u.id = ur.userId      LEFT OUTER JOIN user_group as ug on ug.userId = u.id      LEFT OUTER JOIN control_group as cg on cg.id = ug.groupId      GROUP BY u.id"; $res = $mysqli->query($sql); $users = array(); while ($row = $res->fetch_assoc()) {     if ($row['id'] == $_SESSION["id"]) {         continue;     }     $row['groups'] = explode(",", $row['groups']);     $row['roles'] = explode(",", $row['roles']);     $users[] = $row; } 

This is the response example which I consider most usable and clear.
But note it would take no trouble to make it exactly as in your example above, if you wish.

On a second thought, thinking one move ahead, I suppose you may wish to make groups and roles some sort of interactive - allowing clicks or other interactions with them. And for this purpose you will need group and roled IDs inevitably.

Therefore, to make this code robust and usable in the real life app, you have to supply group and role id as well. For this purpose I can't help it but use PDO, as its helper methods do A LOT of your job. For example, PDO::FETCH_KEY_PAIR gives you nice key-value pairs right out of SQL query:

$groups = $pdo->query("SELECT id, name FROM control_group")->fetchAll(PDO::FETCH_KEY_PAIR); $roles  = $pdo->query("SELECT id, roleName FROM user_role")->fetchAll(PDO::FETCH_KEY_PAIR);  $sql = "SELECT u.id, u.firstName, u.lastName, u.email, u.phoneNumber,      u.address, u.birthDate,group_concat(ur.roleName), roles group_concat(cg.name) groups      FROM users as u      LEFT OUTER JOIN user_role as ur ON u.id = ur.userId      LEFT OUTER JOIN user_group as ug on ug.userId = u.id      LEFT OUTER JOIN control_group as cg on cg.id = ug.groupId      GROUP BY u.id"; $res = $pdo->query($sql); $users = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) {     if ($row['id'] == $_SESSION["id"]) {         continue;     }      $user_groups = array();     foreach(explode(",", $row['groups'] as $id) {         $user_groups[$id] = $groups[$id]     }     $row['groups'] = $user_groups;      $user_roles = array();     foreach(explode(",", $row['roles']) as $id) {         $user_roles[$id] = $roles[$id]     }      $row['roles'] = $user_roles;     $users[] = $row; } 

This is a response example.

As you can see, here we're first selecting all the group and role names, while in the main query selecting only ids. Then in the short loop finally creating groups and roles arrays.

 
 
       
       

Relacionados problema

2  Clasificación de calificaciones del cliente  ( Collating client ratings ) 
El código está recuperando registros de una tabla llamada CSAT_SUMMARY_REPORTS[driver table] , pero debido a las condiciones de los filtros aplicadas que deb...

0  La consulta T-SQL se aplican con las subconsultas anidadas  ( T sql query outer apply with nested subqueries ) 
Tengo las siguientes 3 tablas, y no tengo la opción de modificarlas. [Logs] Tabla con [log_type] , /* GO */ 0 , 99887766555443311 , y otras columnas i...

6  Uso de LINQ para realizar una únete al exterior de la izquierda en 2 datos de datos (criterios múltiples)  ( Using linq to perform a left outer join in 2 datatables multiples criteria ) 
Sé que existe muchas soluciones sobre cómo crear un OUTER JOIN entre dos DataTables . He creado el siguiente código en C #: DataTable vDT1 = new DataT...

3  Únase interno con el primer resultado  ( Inner join with first result ) 
En SQL Server, hay dos tablas: casas y sus imágenes. Necesito una lista con 20 casas con la primera de sus imágenes (solo una). Lo intenté: rhyme_finder4...

11  Búsqueda geográfica  ( Geographic search ) 
Tengo esta tabla ( inradar_ad ) con casi 300k entradas. Quiero saber por qué mi consulta lleva 160 segundos para correr. Intenté limitar con LIMIT 10 para...

4  Contando penalizaciones por cada jugador al unir tablas, donde algunos de los datos son nulos  ( Counting penalties for each player by joining tables where some of the data is ) 
Tengo una tabla llena de nombres de jugadores: También tengo una mesa llena de sanciones de jugadores: Tengo que enumerar todos los nombres de los j...

3  Recopile información sobre computadoras de múltiples archivos CSV  ( Gather information about computers from multiple csv files ) 
He creado un script para importar varios archivos CSV de varias fuentes y un archivo CSV con una lista de sistemas en ella. El script busca cada archivo CSV p...

1  Seleccione la suma de los últimos valores en la tabla  ( Select sum from latest values in table ) 
SELECT SUM(CD.[Value]) AS [Value] FROM [{schema}].[CompanyData] CD INNER JOIN #Locations T ON T.LocationId = CD.CompanyId INN...

2  Unirse interno solo si satisface si la condición  ( Inner join only if satisfies if condition ) 
Estoy buscando revisión de código, y optimizaciones. Tengo dos tablas ItemCategory y ShopItemCategory itemcategory ------------------------------...

32  Contando filas en un archivo CSV que corresponde a una fila de base de datos, cada una con un millón de registros  ( Counting rows in a csv file that correspond to a database row each with a milli ) 
Tengo dos datos: dt : se rellena de un archivo CSV con más de 1.7 millones de filas dataStructure.Tables["AccountData"] : se rellena de una consulta de...




© 2022 respuesta.top Reservados todos los derechos. Centro de preguntas y respuestas reservados todos los derechos