By john_the_second on

Hi,

i need all nodes from a certain type ("customtype") where a field (A node reference with unlimited allowed values) includes a given value.

The working query for all nodes from this special type:

$qry = "SELECT n.nid FROM {node} n WHERE n.type = 'customtype' ";

And i want it to be soemthing like:

$qry = "SELECT n.nid FROM {node} n WHERE n.type = 'customtype' AND n.fieldname LIKE '$nodeid'. ";

I do have some basic mysql knowledge, but everything i tried failed.

Thx !

EDIT:Actually, i would already be happy if i knew how to get all node ids where a given nid is stored in a node reference field.

⋅ Categories: Drupal 6.x

Comments

j_ten_man’s picture

j_ten_man commented

Yeah, this isn't as straightforward as you might think. CCK creates its own tables for custom fields that you add on. You will need to look in the database to find the name of the table you are looking for. The table name will be something like content_field_THE_FIELD_NAME. You will then need to write a join query to get this to work. If you update the name of the table and the name of the field (n.fieldname in your query above, but it will be named by cck inside the table), I would be glad to help you with the query.

john_the_second’s picture

john_the_second commented

Thx - we just got closer !

My investigation so far: All other values are stored in content_type_TYPE, but our node reference is in content_field_TYPE_FIELDNAME (content_field_museum_category).

There we find nid and field_museum_category_nid. In nid is the id from the node (=the museum) we are looking for, and in field_museum_category_nid are the associated categories.

So i could get a list from the nodes i need by selecting all nid in content_field_museum_category where field_museum_category_nid is nid_searched :)

I´m really looking forward to see how you will JOIN these wo tables.

j_ten_man’s picture

j_ten_man commented 
SELECT * 
FROM {node} n 
INNER JOIN {content_type_museum} ctm ON n.nid=ctm.nid
INNER JOIN {content_field_museum_category} cfmg ON ctm.nid=cfmg.nid 
WHERE n.type = 'museum'
AND cfmg.field_museum_category_nid = %d

Then obviously %d is the nid searched. I think that is what you were going for with your query.

Having written that, I might recommend using views if you haven't considered it. I recommend it because we had issues writing custom queries like this where the fields change which tables they're in and so forth. Views handles this without any problems, but your custom queries obviously won't. I don't know what you are doing exactly, but from the scenario you provided (a person searches for a museum category) Views seems like a great solution. Anyways, hope the query helps. Let me know if you have any other questions.

john_the_second’s picture

john_the_second commented

(1) Ty. Your help is appreciated!

(2) It actually works, but there are a lot of multiple entrys. I have to figure out why first..

(3) I can´t use views because my script is needed to display these informations in a unusual way on the screen - and i need x/y coordinates for the drawing. For everything else, views should do the trick.

@nevets: I´ll look into that too.

john_the_second’s picture

john_the_second commented

(2) It actually works, but there are a lot of multiple entrys. I have to figure out why first..

I dont really understand the DB, there are multiple entries once a field has 2+ values. However, it seems to work using DISTINCT, so i´m not going further into it ;)

Warm Regards, John

nevets’s picture

nevets commented

It sounds like you want to use one of the modules that provide a reverse node reference. There are several (at least 2) that do this with different approaches.