Laravel : Join returning all elements

Question

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; 
}

user_2760986 · Answer

The Laravel'sk way is to use Eloquent models to solve your problem.

Your models would look like so.

use Illuminate\Database\Eloquent\Model;

class Social extends Model
{
    public function profile()
    {
        return $this->belongsTo(Profile::class);
    }
}

class Profile extends Model
{
}

From here there is two solutions, the simple is to just include the profile using with(),

return Social::with('profile')->get();

The following structure would be presented.

    {
        "id": 4,
        ...
        "profile": {
            "url": the url from profiles table
        }
    },

That is not exactly what you wanted, instead you can use an getter in Laravel. On your Social model add the following. For the getter to work, it needs to follow the getColumnAttribute function naming. To add the column to the model transformation, add it to the $appends array.

class Social extends Model
{
    protected $appends = ['issues'];

    public function getUrlAttribute()
    {
        return $this->profile->url;
    }
}

Which would produce the expected results, you still need to have included the Profile model for performance optimizations.

    return Social::with('profile')->get();

    {
        "id": 4,
        "url": the url from profiles table
        "profile": { ... }
    },

Laravel : Join returning all elements

1 Answers