Yii Framework Forum: Help With This Conditional Select Statement Please - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Help With This Conditional Select Statement Please Rate Topic: -----

#1 User is offline   U4EA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 146
  • Joined: 04-November 12

Posted 11 February 2013 - 11:59 AM

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.
0

#2 User is offline   Coksnuss 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 140
  • Joined: 14-May 09

Posted 11 February 2013 - 12:51 PM

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');

1

#3 User is offline   U4EA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 146
  • Joined: 04-November 12

Posted 11 February 2013 - 01:13 PM

Coksnuss,

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

#4 User is offline   U4EA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 146
  • Joined: 04-November 12

Posted 11 February 2013 - 06:41 PM

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.
0

#5 User is offline   Coksnuss 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 140
  • Joined: 14-May 09

Posted 12 February 2013 - 03:22 AM

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

#6 User is offline   U4EA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 146
  • Joined: 04-November 12

Posted 12 February 2013 - 05:47 AM

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?
0

#7 User is offline   U4EA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 146
  • Joined: 04-November 12

Posted 12 February 2013 - 07:30 AM

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

#8 User is offline   U4EA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 146
  • Joined: 04-November 12

Posted 13 February 2013 - 08:28 AM

Anyone?
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users