Murlidhar Fichadia Murlidhar Fichadia - 1 year ago 63
PHP Question

Get data from single cell in laravel by querying

Json data



[
{
"id":2,
"firstname":"abc",
"lastname":"def",
"email":"[email protected]",
"role":1,
"university":1,
"school_dept":5,
"year":2,
"photo":"URL",
"bio":"ObxBJIDO6IfOU0DIw8a5",
"search_status":"available",
"created_at":null,
"updated_at":null,
"languages":[
{
"id":3,
"language":"Spanish",
"pivot":{
"user_id":2,
"language_id":3,
"type":"native"
}
},
{
"id":4,
"language":"Greek",
"pivot":{
"user_id":2,
"language_id":4,
"type":"learn"
}
}
]
}
]


I would like to get the language_id of the loggedin user.
if user logged in as id = 2(auth::user()->id).

Then for above data I would like to receive output as:

$learn = 4(language_id)

$native= 3(language_id)


I tried to do it like:

$learn = User::with('languages')->where([['languages.pivot.user_id',Auth::user()->id],['languages.pivot.type','learn']])

$teach = User::with('languages')->where([['languages.pivot.user_id',Auth::user()->id],['languages.pivot.type','native']])


the relationship between user and languages is many to many with extra field called type(native or learn) as pivot.

Table format for User, Language_User, Language



User_table
id
name
....

Language_User
id
user_id
language_id
type('learn','native')

Language_table
id
language

Answer Source

One way to do what you're after is to constrain the relationship e.g.:

$learn = User::with(['languages' => function (Builder $q) {
    $q->wherePivot('type', 'learn');
}])->where('id', Auth::user()->id);

If it's for the authenticated user you could just do:

$learn = Auth::user()->languages()->wherePivot('type', 'learn')->get();

Or if you could even define it as a relationship:

public function languagesToLearn()
{
   return $this->languages()->wherePivot('type', 'learn');
}

Then you could do:

$learn = User::with('languagesToLearn')->where('id', Auth::user()->id);

or

$learn = Auth::user()->languagesToLearn;

If you want to just get the languages you could then do:

Auth::user()->languagesToLearn()->pluck('language')

https://laravel.com/docs/5.3/queries#aggregates

Hope this helps!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download