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

Question

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

Navigation

#1 by (1 votes)

0

Hello! I will explain from the beginning .. I am comparing the following tables accounts with clientes select uses user_id to find all clients with same users_id2 in table clientes as described below, right there!

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

But this a returns this result (repeating the 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"
        }
    }
]

I need to return a more accurate result by grouping the user_id and below showing all the clients listed .. which 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've tried GROUP BY user_id but it was unsuccessful ..

php mysql group-by laravel slimframework

asked by anonymous 16.08.2018 / 09:06

1 answer

Dynamic friendly URL .htaccess How do I search for data in MYSQL using Regular Expressions?

score 1 · Accepted Answer

My answer is not a solution in MySql, but I think it will solve your problem.

In the Laravel documentation there is a feature called Eager Loading , with which you can search for values of a table also taking the values of the relation without suffering the problem N + 1.

You would first need to define relationships 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 if you'd like to display as mentioned, you might want to take a look at Laravel's API Resources , it helps you to return a custom Json format the way you define it.