poll_update_7001 does not work

When someone has the time, can you explain what I'm doing wrong?

I understand what

db_query("UPDATE {poll_votes} SET chid = (SELECT chid FROM {poll_choices} c WHERE {poll_votes}.chorder = c.chorder AND {poll_votes}.nid = c.nid)");

does but I'm trying to convert it to the new db functions and am having problems. All I've managed to come up with so far is

$result = db_select('poll_choices','pc')
      ->addField('pc', 'chid')
      ->join('poll_votes', 'pv', 'pc.chorder = pv.chorder AND pc.nid = pv.nid')
      ->condition('pc.chorder', 'pv.chorder')
      ->condition('pc.nid', 'pv.nid');
    db_update('poll_votes')
      ->fields(array('chid' => $result))
      ->condition('pc.chorder', 'pv.chorder')
      ->condition('pc.nid', 'pv.nid')
      ->execute();

Still get errors with " join on a non-object" so I know i'm missing something. It's be easy with a single db table but I can't find anywhere how to set up a query using 2 or more tables. The only thing I can think of, I haven't tried, is using the ->where() clause but that because I haven't figured that out either. Can't seem to locate anything in the docs or when looking at the db api.

aaronbauman:
Thanks for pointing that out. strange how you miss stuff and try to get too complicated.

Figured it out, here's a patch

new patch against head. comment removed

$query = db_select('poll_choices','c')
->fields('c', array('chid'))
->join('poll_votes', 'v', 'c.nid = v.nid')
->condition('chorder', 'v.chorder')
->condition('nid', 'v.nid');

Can some please explain what I'm doing wrong, I keep coming up with call to member function ????? on non-object.

Unless I'm missing something I'm selecting the table "poll_choices" and assigning it the alias "c", then I want to select "chid" using the fields() function so it can continue the chain. I then want to inner join "poll_votes" assigning it an alias of "v" with only the rows that match "pole_choices.nid = pole.votes.nid" be used then based on the condition that "chorder" form pole_choices equals v.chorder (pole_votes.chorder using the alias "v" for the table "pole_votes) and also requestion that the select also match "nid" from pole_choices and v.nid

I've tried all kinds of various things including using where() but I always come up with this error. This is why I submitted the patch in #6 that has multiple queries.

catch,

Changing tag back to update system since the change require modification to the system.install file and not poll.install

Tried #10 and many different variations of it today. All of them fatal-ed on ->execute()

aaronbauman: Thanks for pointing out expression()

->expression('chid' => '(' . $subquery . ')'));

it has one too many ending ) though and table names are plural. per the docs I tried a couple of things

1st - ->expression('chid' , '(' . $subquery . ')');
2nd - ->expression(array('chid' => '(' . $subquery . ')'));

neither worked. I attempted to display the $subquery results before running the update query with

  $subquery = db_select('poll_choices','pc')
      ->fields('pc', array('chid'))
      // Have to use where() instead of condition() since we're using column names, not values
      ->where('pc.chorder = poll_votes.chorder')
      ->where('pc.nid = poll_votes.nid');
      ->execute() ; print_r($subquery): die;

the update bombed out on remaining system updates then picks up around sequence #113 out of #117. Also tried it after execution of the update query with same results.

#20 works and it doesn't.

If the subquery finds a match then it completes, if no match is found the update query fails and bombs out. This is how I found the issue initially. Probably one of those edge case scenarios that dates back to databases in existence since 4.7 and updated. The particular database I use is one of these edge cases, v4.7 initially solwly updated through 5.22 then updated to 6.17.

Getting 6.17 -> 7 may be a edge case in this circumstance but i'm sure others will have the same situation so i've purposely used it. I really don't use the poll module at all any more but left over tables I kept since the module is still active. It is a small database set with maybe 12 entries in poll_choices on only 2 in poll_votes.

The original values in poll_votes for chorder are -1

no match to chorder in poll_choices, update fails and further system updates stop around sequence #28 then update.php starts again around sequence # 90 or greater.

If I change one of the chorder column values in poll_votes to match then db_update is happy and update.php runs it course as it should except for #826640: Update results page missing session data, causing "Undefined index: update_success" notice and "aborted prematurely" message which i've ignored as a nuisance until update.php is debugged

#6 works period.

haven't tried chx's patch in #17 yet, some time today i'll give it a shot but think it will do the same thing, could be wrong.

I'm not sure we want to delete the votes. In my case these were from anonymous users and are still votes. visit http://authentic-empowerment.net/node/1209

The two votes are displayed properly. If we delete them then poll voting is useless and inaccurate.

Here's patch #25 without the deleting the votes.

Stevel:"but this could be a left-over from an older version."

Yep, these are from somewhere in 5.x, currently 5.22 is one of my production sites which this came from. I updated this to 6.17 which I'm using to run 6 -> 7 update.

My Bad.

I looked at the tables and saw 0 poll_vote. Did not bother looking further. Disregard #29 even with no rows in poll_vote after the update it is still picking up the votes in poll_choice. RTBC #25

How this look

Hmmm,

stupid copy/paste try again

sorry forgot to change status and shortened name

Worked on my local copy, strange.

I don't believe you Mr. Bot, see if you like this one

Re-rolled with comment changes

for some strange reason testing bot did not like poll_update 7001 when I left the db_rename_table functions as they were originally, when I placed then inside the if statement it passed. Seems back in #582948: Improve safety of schema manipulation this was raised and on a failed modify it throws an exception. It worked that way on my local machine, might be a bot issue.

re-rolled as damien suggested. Ran a fresh install of HEAD and new update. everything worked as expected, let's see what the testing bot thinks this time.

Last attempt. Double checked, triple checked. Bot on prior failss have bunked me at 1794 so left the system description as was and comment was place inside function. Let's see what happens with this one.

@Damien: I guess I need to learn netbeans better. I make changes there, copy the files to my drupal cvs folder then use toroiseCVS to run diffs, copy /paste into editpad to make sure it's in unix format. Know there's got to be a easier way just haven't figured it out yet. Blame it on my old line item editor days I guess using the old skool green screen terminals I grew up on. Sheesh you think I'd have learned a better way by now.

Due to a recent commit to table prefixing in databse.inc this update is broke again. If damien or chx is around please look at database.inc and see if it is in the prefixTables function recently committed. The line that gets caught is 399 and has to do with $this->prefixes part of the line. Can't remember the complete error. If this is the proper way to do it then any suggestions as to how to fix chx's fix on the sql statement

db_update('poll_votes')
    ->expression('chid', DatabaseConnection::prefixTables('COALESCE((SELECT chid FROM {poll_choices} c WHERE {poll_votes}.chorder = c.chorder AND {poll_votes}.nid = c.nid), 0)'))
    ->execute();

would be appreciated. I also received database exceptions for db_add_field so will need to re-roll with the if db_table_exists statements.

Works for me