John Davidson

Message:

My controller of a socials table outputs the following :

   [

        {

            "id": 4,

            "value": "FaAlgolia",

            "name": "zaefzaef",

            "profile_id": 4,

            "order": 9000,

            "created_at": "2021-05-17 20:25:03",

            "updated_at": "2021-05-17 20:25:03"

        },

        {

            "id": 5,

            "value": "FaAlipay",

            "name": "azdadzzad",

            "profile_id": 4,

            "order": 9000,

            "created_at": "2021-05-17 20:41:53",

            "updated_at": "2021-05-17 20:41:53"

        }

    ]

Each of those elements belongs to a profile.

Profiles table :

Socials table :

In profiles table I have a URL column. I would want to return it in my array so the output will look like this.

   [

        {

            "id": 4,

            "value": "FaAlgolia",

            "name": "zaefzaef",

            "profile_id": 4,

            "order": 9000,

            "created_at": "2021-05-17 20:25:03",

            "updated_at": "2021-05-17 20:25:03"

            "url": the url from profiles table

        },

        {

            "id": 5,

            "value": "FaAlipay",

            "name": "azdadzzad",

            "profile_id": 4,

            "order": 9000,

            "created_at": "2021-05-17 20:41:53",

            "updated_at": "2021-05-17 20:41:53"

            "url": the url from profiles table

        }

    ]

I tried making a join like the following but it returns both elements with all url. So the output is the first array with both urls then second element with both urls:

public function profile($id)

{

    $data = DB::table('socials')->where('profile_id','=', $id)

    ->join('profiles', 'url', '=', 'profiles.url')

    ->groupBy('profile_id')

    ->get();

    return $data; 

}

---

My Answer:
To achieve the desired output, you can modify your query to select the specific columns you want from both tables and then use the `select` method to specify the columns you want to retrieve. Here's an example of how you can modify your query:

php
public function profile($id)
{
$data = DB::table('socials')
->select('socials.id', 'socials.value', 'socials.name', 'socials.profile_id', 'socials.order', 'socials.created_at', 'socials.updated_at', 'profiles.url')
->where('socials.profile_id', $id)
->join('profiles', 'socials.profile_id', '=', 'profiles.id')
->get();

return $data;
}


In this modified query, we are selecting specific columns from both the `socials` and `profiles` tables and joining them based on the `profile_id` and `id` columns. This should give you the desired output where each element in the array includes the URL from the `profiles` table.