Retrieve a certain column (category_id) to use in a WHERE clause

Viewed 36

Expected Result: use a WHERE clause to filter out a group list containing one of two values

Here is the table in use:

1

and the list of categories:

2

basically if I click on edit and select groups for each category, the group list needs to be anything listed as category_id = 1 and their own individual category_id

Here is my function in use:

   public static function getBbrGroups($category_id, $search, $fields = false, $limit = null, $offset = null) : object
{        
    $query = self::selectRaw((is_array($fields)?implode(", ",$fields):$fields))
        ->where(function($q) use($search){
        if($search){
            return $q->where(['group_name' => $search]);
        }
    });
}

In order for my intended feature to work, I need to substitute the second value here:

public static function getBbrGroups($category_id, $search, $fields = false, $limit = null, $offset = null) : object
{        
    $query = self::selectRaw((is_array($fields)?implode(", ",$fields):$fields)) 
        ->whereIn('category_id', [1,14])
        ->where(function($q) use($search){
        if($search){
            return $q->where(['group_name' => $search]);
        }
    });
}

In this case, I need the value '14' to be a category_id variable, of the selected row like:

 $abc = retrieve category_id of current row;
 >whereIn('category_id', [1, $abc])

I tried syntax such as:

 $aa = ['category_id' => $search];
->whereIn('category_id', [1,$aa])

But I only get results for category_id = 1 as seen in the table on the 1st screenshot (distinct)

3

Here is the Controller for additional background for the issue:

public function datatable(Request $request)
{
    $category_id = $request->get('category_id');
    $status = $request->get('status');
    $limit = $request->get('length');
    $offset = $request->get('start');
    
    $search = [
        'category_id' => $category_id,
        'status' => $status,
        'limit' => $limit,
        'offset' => $offset,
        'jid' => $request->get('jid') ?? Auth::user()->jid
    ];

    return $this->bbrGroupConfiguration->datatable($search);
}

public function fetchGroups(Request $request, $category_id=-1)
{
    $search = $request->get('search') ?? null;
    $page = $request->get('page') ?? 1;
    $limit = 10;
    $offset = ((int)$page - 1) * $limit;
    $count = $this->bbrGroupConfiguration->getBbrGroupsCount((int)$category_id, $search, ['id', 'group_id', 'group_name' ,'category_id']);
    $endCount = $offset + $limit;
    $morePages = $count > $endCount;
    
    $result = $this->bbrGroupConfiguration->getBbrGroups((int)$category_id, $search, ['id', 'group_id', 'group_name' ,'category_id'], $limit, $offset);

    if(!$result){
        return response()->json([
            'status' => 400,
            'success' => FALSE,
            'message' => 'Could not fetch bbr groups'
        ]);
    }

    return response()->json([
        'status' => 200,
        'success' => TRUE,
        'message' => 'BBR Groups fetched successfully!',
        'data' => $result,
        'pagination' => ['more' => $morePages]
    ]);
}

as you can see, there is an array for $result, this comes out in the console as:

"data":[{"id":64,"group_id":64,"group_name":"Group B","category_id":14}

category_id changes as you select each row (2nd screenshot)

I need to somehow retrieve that category_id value being used in the response on $result for my WhereIn to work. I would like some suggestions for this.

Feel free to ask for clarification, I just need this to work.

0 Answers
Related