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.
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?
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.
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
)
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;
}