Compare two tables in mysql by grouping the main table and listing the result of the second table

Question

Hello! I will explain from the beginning.. I am comparing the following tables accounts with clientes select uses the user_id to find all customers with same users_id2 on the table clientes as described below, so far so certain!

"SELECT * FROM accounts as a INNER JOIN clientes as c ON (a.user_id=c.users_id2)"

but this gives me this result (repeating user_id)...

[
    {
        "user_id": "100001",
        "email": "[email protected]",
        "clientes": {
            "id_cliente" => "1",
            "users_id": "100001",
            "nome_pessoa": "cliente pereira"
        }
    },
    {
        "user_id": "100001",
        "email": "[email protected]",
        "clientes": {
            "id_cliente" => "2",
            "users_id": "100001",
            "nome_pessoa": "cliente Gustavo"
        }
    }
]

And I need you to return a more precise result by grouping the user_id and below displaying all the customers listed.. that in json would look exactly like this..

[
    {
        "user_id": "100001",
        "email": "[email protected]",
        "clientes": {
            "0": {
                 "id_cliente" => "1",
                 "users_id": "100001",
                 "nome_pessoa": "cliente pereira"
            },
            "1": {
                 "id_cliente" => "2",
                 "users_id": "100001",
                 "nome_pessoa": "cliente Gustavo"
            }
        }
    }
]

So how can I be writing this code in mysql? I tried GROUP BY user_id but it was unsuccessful..

Answer 1

This answer is not a solution in Mysql but I believe it solves your problem.

In Laravel’s documentation there is a feature called Eager Loading, with it you can fetch values from a table taking also the values of the relation without suffering the problem N+1.

You would first need to define the relations in the User Model.

/**
 * Define que usuários pertence a vários clientes.
 */
public function clientes()
{
    return $this->belongsToMany('App\Clientes');
}

And then you would search for users' customers using:

$users = App\User::with('clientes')->get();

And in case you want to display it the way you mentioned, maybe you could take a look at API Resources from Laravel, it helps you return a custom Json format the way you set it.