Query With Subquery As A Scope?

Hi folks,

This is really a 2 part question. The first part is really a pure SQL question that can’t seem to answer and the second part a question on how to create a scope from the answer to part 1 (providing it can be answered).

I currently have two tables: -

company_department_name is a normalisation table and ref_departments the reference table as the FK/PK shows.

In my create/update form, I have a dropdown to provide a list in the departmant_id field of the ref_departments ID and department_name. That’s the easy bit.

However, I want to filter the data in the dropdown a bit more. Specifically, I want to filter it so that is only brings back options in the department_id field for those that are not already in use. The two fields it needs to filter the data on are company_id and department_id. For company_id, the company_id will be taken from $model->company_id. For the department_id, it needs to select where the department_id IS NOT NULL.

I am currently trying to produce the SQL statement with db::createCommand and have this so far: -




"SELECT RD.id, RD.department_name FROM ref_departments RD 

LEFT OUTER JOIN company_department_norm CDN

ON (CDN.department_id = RD.id) 

WHERE (CDN.department_id IS NULL)"



This itself takes care of filtering the right table (company_department_norm) to make sure only rows from ref_departments whose PK is NULL in company_department_norm.department_id are returned. However, no matter what I try, I cannot seem to filter on the 2nd condition - that the company ID is matched so the query returns only rows where the the company_department_norm.department_id is NULL AND company_department_norm.company_id is a given value. For testing purposes, I have been using a company_id of integer 1, but no matter what I do it does not seem to filter the date.

I attempted this: -


LEFT OUTER JOIN (SELECT * FROM company_department_norm WHERE company_department_norm.company_id = 1) CDN

… but to no avail.

I assumed some kind of subquery is what is needed here but I have little or no experience with them and I am a bit stuck here. If/when I have a solution to the above, I will then try to create a named scope from it, which will make life a bit easier and my code a bit cleaner.

Can anyone help?

I think i would solve it with CDbCritera.

If you have your relations setup in your model, you can reference the other table like this:

$criteria->compare(‘departmentName.id’, “=”.$id, “AND”);

(i’d have to look up how to do !NULL)

Thanks for your suggestion.

However, I really want to learn the logic/SQL syntax for this problem first then see if I can solve it with scopes from there on. Scopes are so damn easy to use :)

Anyone else got any thoughts on this? Or at least could someone help me to solve the straight SQL query?

Anyone?

Hi U4EA,

It’s not very clear to me. Is your intention like the following?




company_id = 1 ... nothing

company_id = 2 ... 1:Accounts and 2:HR



Or something like this?




company_id = 1, department_id = 1 ... 2:HR

company_id = 1, department_id = 2 ... 1:Account

company_id = 2, department_id = NULL ... 1:Account + 2:HR



Hi softark,

The idea is to limit the options available in the dropdown based on what has already been selected.

The idea of the table schema is to allow association of company department names to normalise them. So each company with have certain departments - human resource, accounts, IT etc.

If we can imagine for the sake on simplicity that there is no ‘company_id’ column in company_department_norm…

So, in this case, I would only want to bring back normalisation options from ref_departments where the key is not already found in the department_id column. So, in filtering the data like that, it will only give the option to associate to an option that is not already taken.

So, in the case of company_id of 1, no options would be given as they currently taken by way of them being associated to PKs in the reference table. However, for company_id 2, options for both id of 1 and id of 2 from the reference table would be returned in the query. If department_name of company_id is populated with an ID of 1, then the association options for that company would no longer have that available to it.

I can get it working with the scenario of only returning values based on this filter, but I do not know how to further filter it so that it will only bring back value options filtered by company_id to. That is to say that I need it to say not just "SELECT where company_department_name.department_id is NULL" but also "SELECT where company_department_name.department_id = NULL WHERE company_id = :company_id".

Please note that, for testing purposes, an assumed company_id of 1 is being used.

Does that make sense?

Seems like I might have solved the SQL syntax. I will get back to you.

Working SQL syntax is below. Just need to create a parameterized scope from it.




SELECT RD.id, RD.department_name 

FROM ref_departments RD

WHERE 

    NOT EXISTS (

    SELECT 1 FROM company_department_norm 

    WHERE 

        company_id = 2 

    AND department_id = RD.id

)



Anyone have any idea as to how to build that into a parameterized scope? I literally dont even know where to begin!

try something like this


function myScope($company_id)

{

	$crit = $this->getDbCriteria();


	$crit->addCondition("

		NOT EXISTS (

		    SELECT 1 FROM company_department_norm 

		    WHERE 

		        company_id = :company 

		    AND department_id = RD.id

		)

	");

	$crit->params[':company'] = $company_id; // <== not sure about this, never used parameterized queries in this way.


	return $this;

}