Yii Framework Forum: Query With Subquery As A Scope? - Yii Framework Forum

Jump to content

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

Query With Subquery As A Scope? Rate Topic: -----

#1 User is offline   U4EA 

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

Posted 14 February 2013 - 12:40 PM

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: -

Posted Image

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

#2 User is offline   Peter Hayman 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 61
  • Joined: 11-June 12
  • Location:Wake Forest

Posted 14 February 2013 - 02:02 PM

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

#3 User is offline   U4EA 

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

Posted 14 February 2013 - 06:21 PM

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

#4 User is offline   U4EA 

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

Posted 15 February 2013 - 07:57 AM

Anyone?
0

#5 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 15 February 2013 - 08:59 AM

Hi U4EA,

View PostU4EA, on 14 February 2013 - 12:40 PM, said:

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.

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

0

#6 User is offline   U4EA 

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

Posted 15 February 2013 - 09:45 AM

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

#7 User is offline   U4EA 

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

Posted 15 February 2013 - 11:17 AM

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

#8 User is offline   U4EA 

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

Posted 15 February 2013 - 12:16 PM

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
)

0

#9 User is offline   U4EA 

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

Posted 15 February 2013 - 12:34 PM

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

#10 User is offline   Cstdenis 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 22-March 11

Posted 18 February 2013 - 04:06 AM

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;
}

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