Hi all,

I am really interested in the following functionality that is provided by the module:
SELECT n.title FROM node n WHERE n.nid [IN | NOT IN] (SELECT n2.nid FROM node n2 WHERE ...)

In particular, I have:
- an entity type "Resources" that are set up with ECK module
- View 1 that provides a list of Resources, let's call the output "List 1"
SELECT r.id FROM resources WHERE "conditions for view 1"
- View 2 that provides another list of Resources, let's call the output "List 2"
SELECT r.id FROM resources WHERE "conditions for view 2"

What I need is View 3 that provides a list of Resources (List 3) that is equal to "List 1" minus "List 2".

Based on the module explanation, I'm planning to create View 3 as clone of View 1 and add the NOT IN "List 2" condition.
SELECT r.id FROM resources WHERE "conditions for view 1"
AND r.id NOT IN (SELECT r.id FROM resources WHERE "conditions for view 2")

Can it be achieved? How?

Really thank you in advance for the help.

Comments

drupaljunior created an issue.

Fabito’s picture

Fabito commented

Hi,

I've a similar case. And i can produce your result but that don't work properly.
I explain : I've 2 content type ("participant" and "assignation"). When i create an "assignation",
i can added some participant via an Entity Reference field ("field_occupants"). This field load a view (list1).
I configure this in the fields parameters under "mode" > "Views" (not "Simple").
List1 (Entity reference) listed all participants in the user id context (context filter->(author)User:Uid + content:author in relation).
At this moment, i filter only on "content:publish (yes)" + "content:type(=participant)".

That i want, is load in this list1, only the "participant" not already added/associated when i create "assignation" content. List1 minus List2.
So, I create a second view (list2/Entity Reference), where I configure like :

Field->Content:Nid
Filter-> Content:publish(yes)
Filter-> Content:type(=participant)
Context filter-> (author)User:Uid
Relation 1->Content:author (use for the context filter)
Relation 2->Entity Reference:content reference content from field_occupants
Relation 3-> Entity Reference:content referenced by field_occupants

If i test, i obtain the list of "participant" already added by the user via "assignation" content.
For example, user create "participant" (Nid = 1, 2 and 3) and one "assignation" for Nid 1 and 2.
List2 list Nid 1 and 2. It's correct.

I save. I return in the view (list1) and i add filter "content:Nid (reference filter)".
I select the operator : "Not part of", and i select my view (list2).

But...
When i test the view (list1), the sub-request (NOT IN) include all Nid, not just "participant" with Nid 1 and 2.

The genered query:

SELECT node.nid AS nid, node.title AS node_title
FROM 
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
WHERE (( (users_node.uid = '1' ) )AND(( (node.status = '1') AND (node.type IN  ('participant')) AND (node.nid NOT IN  ('1', '2', '3')) )))
ORDER BY node_title DESC
LIMIT 10 OFFSET 0

After "NOT IN", there are the three Nid, not just ('1','2') ?!!

I don't know why, but maybe this example could put you on the way... and resolve my problem.
In my case, your "List 3" is my "List1".
Have you display the query for your "List 3", especially the r.id return from the subquery ?
Maybe.

Fabien.