clqms-be/app/Models/ValueSet/ValueSetDefModel.php

<?php

namespace App\Models\ValueSet;

use App\Models\BaseModel;

class ValueSetDefModel extends BaseModel {
  protected $table      = 'valuesetdef';
  protected $primaryKey = 'VSetID';
  protected $allowedFields = ['SiteID', 'VSName', 'VSDesc', 'CreateDate', 'EndDate']; 
	
	protected $useTimestamps = true;
  protected $createdField = 'CreateDate';
  protected $updatedField = '';
	protected $useSoftDeletes = true;
	protected $deletedField = 'EndDate';

  public function getValueSetDefs($param = null) {
    // Get item counts subquery
    $itemCounts = $this->db->table('valueset')
        ->select('VSetID, COUNT(*) as ItemCount')
        ->where('EndDate IS NULL')
        ->groupBy('VSetID');
    
    $builder = $this->db->table('valuesetdef vd');
    $builder->select('vd.*, COALESCE(ic.ItemCount, 0) as ItemCount');
    $builder->join("({$itemCounts->getCompiledSelect()}) ic", 'vd.VSetID = ic.VSetID', 'LEFT');
    $builder->where('vd.EndDate IS NULL');
    
    if ($param !== null) {
      $builder->groupStart()
          ->like('vd.VSName', $param, 'both')
          ->orLike('vd.VSDesc', $param, 'both')
          ->groupEnd();
    }
    
    $rows = $builder->get()->getResultArray();
    return $rows;
  }
  
  
}