CODEWITHSUNDEEP
mysqllaravel
A.K.S
A.K.S

Reputation: 117

Retrive all the value that satisfy the condition of first table

enter image description here

I have two tables users and location. I need to join both tables

enter image description here

what i need is get all the area number of all the users which are present in the user table. ie user 1 has 3 entries in the second table so i need to join the table in such a way that is, id1 = 1 area = 2,3

area 2 is repeating so do not include it twice

i tried the join but now getting the correct way to doing it.

What i tried?

    $location = User::
  join('addresses','users.id1','=','addresses.id1')                                    ->select('users.id1','addresses.area')
                    ->get();

Expected Output

User 1 -> area ->2,3

Upvotes: 0

Views: 49

Answers (1)

Lakhwinder Singh
Lakhwinder Singh

Reputation: 5582

Here are the two ways to do this.

Firstly you can use Laravel relationship:-

In your model User create relationship:-

function addresses()
{
    return $this->hasMany(Address::class, 'id1', 'id1');
}

Now in your User controller you can get User addresses (areas) like this

$users = User::with('addresses')->get();
dd($users->toArray());

This will print something like this

[
    {
        id1: 1,
        name: abaa
        pwd: 12345
        addresses: [
            {
                id2: 1,
                id1: 1,
                area: 2
            },
            {
                id2: 2,
                id1: 1,
                area: 3
            },
            {
                id2: 3,
                id1: 1,
                area: 3
            }
        ]
    },
    {
        ...
    }
]

Second you can use Laravel relationship:-

$builder = new User;
$builder->join('addresses','users.id1','=','addresses.id1')
    ->selectRaw("users.*, GROUP_CONCAT(DISTINCT addresses.area SEPARATOR ',') as distinct_areas")
    ->groupBy("users.id1")
    ->get();

This query will give you result something like this

[
    {
        id1: 1,
        name: abaa,
        pwd: 12345,
        distinct_areas: 2,3
    },
    {
        ...
    }
]

I think this will help you.

Upvotes: 2

Related Questions