Help With This Conditional Select Statement Please

Hi folks,

I am currently trying to create a dropdown menu for a table, and I cant seem to get my head around this.


$employees = CHtml::listData(RefEmployee::model()->findAll("company_id = {$model->company_id}"), 'id', 'employee_name');

That’s all well a good but I am trying to create this on an association table and I do not want to bring back options for data that is already associated. To explain this further, the dropdown is used to populate the employee_id name on a table that has these fields: -


ID - company_id - employee_name - employee_id

The table itself is called norm_company_employee (with the model name NormCompanyEmployee) and it contains the relation: -


'employee' => array(self::BELONGS_TO, 'RefEmployee', 'employee_id'),

The ref_employee table is simple and looks like this: -


ID - company_id - employee_name

All relations are fine - they have been tested.

As of now, there are still a few ‘employee_id’ fields in norm_company_employee that are NULL, and these are the fields I am trying to populate. I have unique attributes validator in place which conforms to the SQL index but I would like - for convenience - to only have the dropdown only show employees that are not yet associated. So, the code needs to check the relational ship and only SELECT from ref_employee (RefEmployee) where the ID of the ref_employee record is not already linked to the norm_company_employee.employee_id column.

I hope that makes sense?

I am sure the solution is probably not that difficult but I cant seem to get my head around it.

Thanks in advance.

Hi U4EA,

apply a scope before loading the relation, like this:

RefEmployee


public function scopes()

{

    return array(

        'unassignedEmployees' => array(

            'condition' => 'normCompanyEmployee.employee_id IS NULL',

            'with' => 'normCompanyEmployee', // HAS_MANY Relation which you need to setup in relations()

        ),

    );

}



Now you can simply apply the scope in your code above like this


$employees = CHtml::listData(RefEmployee::model()->unassignedEmployees()->findAll("company_id = {$model->company_id}"), 'id', 'employee_name');

Coksnuss,

Good God that is useful! Thank you very much, worked perfectly!

Coksnuss,

I am hoping a can get a little bit more help here.

Do you know how I can modify this scope so it will only bring back results to a certain norm_company_employee.company_id? I’ve tried to do this but it never seems to bring back the right results.

So, simply put, I was the query to bring back the same results with ‘WHERE norm_company_employee.company_id = 1’.

The scope itself will evolve into a parameterized scope but if I can just get the static scope working now, I should be able to do that part myself.

The condition should just change to the one that you already specified.

I cant seem to get it to work.

Using this scope: -




public function unassociatedCompanyEmployees()

{

            $this->getDbCriteria()->mergeWith(array(

                'with' => 'normCompanySports',

                'condition' => "normCompanyEmployees.sport_id IS NULL AND normCompanyEmployees.feed_id = 1",

            ));

            return $this;

        }



This SQL statement is produced.


SELECT `t`.`id` AS `t0_c0`, `t`.`employee_name` AS `t0_c1`, `t`.`selected` AS `t0_c2`, `normCompanyEmployees`.`id` AS `t1_c0`, `normCompanyEmployees`.`company_id` AS `t1_c1`, `normCompanyEmployees`.`employee_name` AS `t1_c2`, `normCompanyEmployees`.`employee_id` AS `t1_c3` FROM `ref_employee` `t` LEFT OUTER JOIN `norm_company_employee` `normCompanyEmployees` ON (`normCompanyEmployees`.`employee_id`=`t`.`id`) WHERE (normCompanyEmployees.employee_id IS NULL AND normCompanyEmployees.company_id = 1)

It looks to me like the SQL statement is trying to create the JOIN based on the two criteria rather than JOIN based on the "IS NULL" condition AND based on the ".company_id = 1" condition rather than performing the JOIN on the former and filtering the foreign table on the latter?

Should I be performing the .company_id as a subquery on the right table? If so, how do I go about doing this?

Anyone?