getting the value of an extra pivot table column laravel

Question 1

I have a phone_models, phone_problems, and a phone_model_phone_problem pivot table. The pivot table has an extra column 'price'.

PhoneModel:

class PhoneModel extends Eloquent
{
    public function problems()
    {
        return $this->belongsToMany('RLPhonesEntitiesPhoneProblem')->withPivot('price');
    }
}

PhoneProblem:

class PhoneProblem extends Eloquent
{
    public function models()
    {
        return $this->belongsToMany('PhoneModel')->withPivot('price');
    }
}

What I'm trying to do is get the price of a specific phone with a specific problem.

This is how I have it now but I feel like Laravel has a built in Eloquent feature I can't find to do this in a much simpler way:

$model = $this->phoneService->getModelFromSlug($model_slug);
$problem = $this->phoneService->getProblemFromSlug($problem_slug);

all this does is select the specific model and problem from their slug.

then what I do is with those credentials I get the price like so:

$row = DB::table('phone_model_phone_problem')
->where('phone_model_id', '=', $model->id)
->where('phone_problem', '=', $problem->id)
->first();

so now I can get the price like so $row->price but I feel like there needs to be a much easier and more 'Laravel' way to do this.

Question 2

When using Many to Many relationships with Eloquent, the resulting model automatically gets a pivot attribute assigned. Through that attribute you're able to access pivot table columns. Although by default there are only the keys in the pivot object. To get your columns in there too, you need to specify them when defining the relationship:

return $this->belongsToMany('Role')->withPivot('foo', 'bar');

Official Docs

If you need more help the task of configuring the relationships with Eloquent, let me know.

Edit

To query the price do this

$model->problems()->where('phone_problem', $problem->id)->first()->pivot->price

Question 3

You can't do that using with, because it executes separate query.

What you need is simple join. Just translate the query you have to something like:

Posts::join('comments as c', 'posts.id', '=', 'c.id')
    ->selectRaw('posts.*, count(distinct c.id) as numComments')
    ->groupBy('posts.id', 'posts.post_title')
    ->with('user', 'vote', 'tags')
    ->get();

then each post in the collection will have count attribute:

$post->numComments;

However you can make it easier with relations like below:

Though first solution is better in terms of performance (might not be noticeable unless you have big data)

// helper relation
public function commentsCount()
{
    return $this->hasOne('Comment')->selectRaw('posts_id, count(*) as aggregate')->groupBy('posts_id');
}

// accessor for convenience
public function getCommentsCountAttribute()
{
    // if relation not loaded already, let's load it now
    if ( ! array_key_exists('commentsCount', $this->relations)) $this->load('commentsCount');

    return $this->getRelation('commentsCount')->aggregate;
}

This will allow you to do this:

$posts = Posts::with('commentsCount', 'tags', ....)->get();
// then each post:
$post->commentsCount;

And for many to many relations:

public function tagsCount()
{
    return $this->belongsToMany('Tag')->selectRaw('count(tags.id) as aggregate')->groupBy('pivot_posts_id');
}

public function getTagsCountAttribute()
{
    if ( ! array_key_exists('tagsCount', $this->relations)) $this->load('tagsCount');

    $related = $this->getRelation('tagsCount')->first();

    return ($related) ? $related->aggregate : 0;
}

More examples like this can be found here http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/

Question 4

Get all the roles of the user, then in a loop you get all tools of the role and merge them to an array where you store all tools.

$tools = array();
foreach(User::find(1)->roles as $role)
    $tools = array_merge($tools, $role->tools->toArray());

This runs a query for every iteration, so for better performance you should use eager loading.

$tools = array();
foreach (User::find(1)->load('roles.tools')->roles as $role) {
    $tools = array_merge($tools, $role->tools->toArray());
}

Now you can place this to a function called tools() in your User model.

public function tools()
{
    $tools = array();   
    foreach ($this->load('roles.tools')->roles as $role) {
        $tools = array_merge($tools, $role->tools->toArray());
    }
    return $tools;
}

You can call it like this: User::find(1)->tools().

I don't think that the framework has a better solution. One other method is to use the Fluent Query Builder and create your own query but I don't see how that would be better.

Question 5

Try to use the sync() function

Product::find($product_id)->categories()->sync($array_of_categories_id)

Question 6

Basically you need to create 2 lists of DISTINCT values, a vertical list of unique User IDs, and a horizontal list of unique Questions. There are various ways to do that with an array formula - look here for example.

Fortunately, since you don't mind using a pivot table, you can also "cheat" and just let the pivot fill the content with aggregated nonsense, then just grab the headers with a simple reference formula (=A1, =A2 and so on).

Ultimately you goal is to achieve:

First question... | Second question... | And another question... | ...
621546
621547
621548
...

Then you can use an IF statement in each "content" cell to grab the correct result:

{=OFFSET(Source!$C$1,MAX(IF(($A1=Source!$A$1:$A$100)*(B$2=Source!$B$1:$B$100),ROW($1:$100)))-1,0)}

That's an array formula, so make sure you Ctrl+Shift+Enter. What it does is conditionally (on matching both user and question) returns the row number of the match, aggregates the returned values (1 number and a lot of false values) into the single row number value, and then use that to access the response itself.

getting the value of an extra pivot table column laravel

Answers

However you can make it easier with relations like below:

php,mysql,laravel,many-to-many,pivot-table