How to query by row's column value?

@zyberg

Can show your desired SQL? Can't understand what you meant by completed: group|type|user_id

@laracoft It means that there is a table named completed which has columns group, type, user_id.

My desired SQL would be a hwhere statement completed.group = currentTable.group AND completed.user_id = user_id

@zyberg

DB::table('completed')
->join('currentTable', function ($join) use ($user) {
    $join->on('currentTable.group', 'completed.group')
        ->on('currentTable.user_id', 'completed.user_id);
})->count();

@laracoft , thanks, but I need this computation done by row basis. As in

completed: ('group1', 'currentTable', 1) currentTable: (1, 'group1'), (1, 'group1')

Should give me something like ['1' => 2] (user_id => count).

I've tried having a relationship

public function completed()
{
        return $this
            ->hasMany(Grade::class, 'group', 'group');
}

But it does not work to do

$query->withCount(['completed' => function ($query) use ($user) {
      $query->where('user_id', $user->id);
 }]);

Because I also need to add constraint hasMany(...)->where('type', $this->type) and I obviously can't access $this in SQL query.

Edit: I can't have types as variable like I do with $user because they are the binding thing, just like 'group' is

@zyberg

Can you provide the full desired SQL?

@laracoft I can't because I am not very proficient with SQL :)

Let me try to describe it to you: table goals (or completed as per our earlier discussion). Has these entries: (user_id, group, type), (1, 'group1' 'firstTableType'), (1, 'group1' 'firstTableType'), (1, 'group1' 'secondTableType'), (2, 'group1' 'firstTableType')

Then there are two tables: 'firstTableType' and 'secondTableType'. They both have structure group|user_id.

I need to make a conditional extention to an existing query in a manner where this would be filled in:

$query->withCount(['completed' => function ($query) use ($user) {
                $query->where('user_id', $user->id)->where('group', ????/*maybe something like 'goal.group?'*/]);
}]);

Edit: in the end the query would return me a collection of goals with a key 'completed' whose value would be the completed goals for a certain user and a certain group.

@zyberg Sorry, it is really hard to visualize your description. Off hand, what I can see is that you have a polymorphic field. (read: complex table design)

1. Either describe your requirements in plain human logic, i.e. firstTableType doesn't tell me anything about your intent
2. OR provide desired SQL and I just do the conversion to eloquent

No problem! Sorry for using up your time, just found a solution myself. Your example really helped :)

$query->withCount(['completed' => function ($query) use ($user) {
        $query->where('user_id', $user->id)->whereRaw('goals.group = firstTypeTable.group');
    }]);

@zyberg

Glad your issue is solved!