1

On my site, I have venues, and reviews of the venues, related with an entity reference field. I'm creating a computed field to store the UID of the first person to create a review for each venue. To do so I've written the below query. Now, it works fine when I enter the simplified sql directly into PHPmyadmin, and substitute $entity->nid with an actual nid, but for some reason it just will not work when I add it into the computed code field, with the added db_query and fetch portions. Any ideas?

if (!$entity->nid) node_save($entity);

$entity_field[0]['value'] = 
db_query (
    SELECT entity_id
    FROM (
        db_query (
            SELECT * 
            FROM field_data_field_review_venue_reference AS ReviewRow
            WHERE field_review_venue_reference_target_id = $entity->nid
        )->fetchrow()
    ) AS uid
    LIMIT 1
)->fetchfield();
1

What you wrote is not PHP code, which is what a computed field is expecting.
I would rather use the following code.

$entity_field[0]['value'] = db_query("SELECT entity_id FROM {field_data_field_review_venue_reference} vr WHERE vr.field_review_venue_reference_target_id = :id", array(':id' => $entity->nid))
  ->fetchField();
| improve this answer | |
  • That did indeed work. I guess I need to actually learn what languages are what. Learning on my own has been a little rough. If you have a minute, would you mind explaining how the "vr" pieces function? I think I understand the rest of it. Also, if I wasn't writing PHP, was I writing anything that could be considered anything, or was it just kind of gibberish that happened to work in PHPmyadmin? – Mrweiner Dec 10 '12 at 0:34
  • That is an alias for the table name; I prefer using it for when the query is about more than one table, even if in this case it was not necessary. You mixed PHP with SQL. PHP doesn't recognize SELECT as instruction; it needs to pass it as string to an SQL server. – kiamlaluno Dec 10 '12 at 0:56
  • To add to this, I realized that this was only the first part of the query. I needed to add a second part in order to grab the uid of the author of the node. It wasn't working until I added the vr, and ended up as $entity_field[0]['value'] = db_query(" SELECT uid FROM {node} vr WHERE vr.nid = '$nids'")->fetchfield(); (where $nids is the first query). Why was the vr alias necessary in this case? – Mrweiner Dec 10 '12 at 1:14
  • The alias was not necessary in the query I wrote because it involves just a database table. I normally use aliases for queries I know I could expand in future, just to avoid I write a query that is wrong. I guess you needed db_query('SELECT n.uid FROM {node} n INNER JOIN {field_data_field_review_venue_reference} vr ON vr.entity_id = n.nid WHERE vr.field_review_venue_reference_target_id = :id', array(':id' => $entity->nid)). – kiamlaluno Dec 10 '12 at 1:36
  • I find strange that in a table named field_data_field_review_venue_reference, one of the table fields is named field_review_venue_reference_target_id. It's not impossible, but it sounds really strange. – kiamlaluno Dec 10 '12 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.