Converting a query to D7
Before I pull any more hair out, can someone properly convert this to a D7 select query, please: SELECT ubr.rid, ubb.* FROM {user_badges_roles} ubr INNER JOIN {user_badges_badges} ubb ON ubb.bid = ubr.bid WHERE ubr.rid = %d It didn't seem all that difficult, but I keep getting errors. Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
http://upgrade.boombatower.com/tools/sql/inline db_select('user_badges_roles', 'ubr') ->fields('ubr', array('rid', 'ubb')) ->join('user_badges_badges', 'ubb', 'ubb.bid = ubr.bid') ->condition('ubr.rid', '@todo Set the appropriate argument.') ->execute(); ---------------------------------- Dipen Chaudhary Founder, QED42 : We build beautiful and scalable web strategies ( www.qed42.com ) Blog: dipenchaudhary.com Twitter: http://twitter.com/dipench On Tue, Oct 11, 2011 at 9:05 PM, Ms. Nancy Wichmann <nan_wich@bellsouth.net>wrote:
Before I pull any more hair out, can someone properly convert this to a D7 select query, please: SELECT ubr.rid, ubb.* FROM {user_badges_roles} ubr INNER JOIN {user_badges_badges} ubb ON ubb.bid = ubr.bid WHERE ubr.rid = %d
It didn't seem all that difficult, but I keep getting errors.
*Nancy*
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
-- [ Drupal support list | http://lists.drupal.org/ ]
Dipen wrote:
http://upgrade.boombatower.com/tools/sql/inline
db_select('user_badges_roles', 'ubr') ->fields('ubr', array('rid', 'ubb')) ->join('user_badges_badges', 'ubb', 'ubb.bid = ubr.bid') ->condition('ubr.rid', '@todo Set the appropriate argument.') ->execute();
1. the fields section is plainly incorrect as it won't select any fields from user_badges_badges and has an erroneous field from user_badges_roles 2. Join just returns the tables alias, not a select query object, so it's not chainable. Vaibhav Jain wrote:
You can use this query like this $query = db_select('user_badges_roles', 'ubr') ->fields('ubr', array('rid', 'ubb')) ->join('user_badges_badges', 'ubb', 'ubb.bid = ubr.bid');
$query = condition('ubr.rid', '@todo Set the appropriate argument.') ->execute();
Same problem as number 1 above, and what on earth is the condition function? I know of no procedural function in Drupal by that name. Nancy, here's what you want: $query = db_select('user_badges_roles', 'ubr'); $query->innerJoin('{user_badges_badges', 'ubb', 'ubb.bid = ubr.bid'); $result = $query->fields('ubr', 'rid') ->fields('ubb') ->condition('ubr.rid' $YOUR_ARGUMENT) ->execute(); There may be a more elegant way to get structure the query, but this will get you the results you're looking for. -Mike __________________ Michael Prasuhn mike@mikeyp.net
Hey Michael, $query = condition('ubr.rid', '@todo Set the appropriate argument.') Definitely my bad over here. should have been $query->condition('ubr.rid', '@todo Set the appropriate argument.') On Wed, Oct 12, 2011 at 1:06 PM, Michael Prasuhn <mike@mikeyp.net> wrote:
Dipen wrote:
http://upgrade.boombatower.com/tools/sql/inline
db_select('user_badges_roles', 'ubr') ->fields('ubr', array('rid', 'ubb')) ->join('user_badges_badges', 'ubb', 'ubb.bid = ubr.bid') ->condition('ubr.rid', '@todo Set the appropriate argument.') ->execute();
1. the fields section is plainly incorrect as it won't select any fields from user_badges_badges and has an erroneous field from user_badges_roles
2. Join just returns the tables alias, not a select query object, so it's not chainable.
Vaibhav Jain wrote:
You can use this query like this $query = db_select('user_badges_roles', 'ubr') ->fields('ubr', array('rid', 'ubb')) ->join('user_badges_badges', 'ubb', 'ubb.bid = ubr.bid');
$query = condition('ubr.rid', '@todo Set the appropriate argument.') ->execute();
Same problem as number 1 above, and what on earth is the condition function? I know of no procedural function in Drupal by that name.
Nancy, here's what you want:
$query = db_select('user_badges_roles', 'ubr'); $query->innerJoin('{user_badges_badges', 'ubb', 'ubb.bid = ubr.bid'); $result = $query->fields('ubr', 'rid') ->fields('ubb') ->condition('ubr.rid' $YOUR_ARGUMENT) ->execute();
There may be a more elegant way to get structure the query, but this will get you the results you're looking for.
-Mike
__________________ Michael Prasuhn mike@mikeyp.net
-- [ Drupal support list | http://lists.drupal.org/ ]
-- Regards, Vaibhav Jain
The documentation here is useful http://drupal.org/developing/api/database SELECT queries still work in the old style (Static queries), but placeholders are formatted differently, see http://drupal.org/node/310072 Carl Wiedemann Website design and development consulting carl.wiedemann@gmail.com | skype: c4rlww
On Tue, Oct 11, 2011 at 9:05 PM, Ms. Nancy Wichmann <nan_wich@bellsouth.net> wrote:
Before I pull any more hair out, can someone properly convert this to a D7 select query, please: SELECT ubr.rid, ubb.* FROM {user_badges_roles} ubr INNER JOIN {user_badges_badges} ubb ON ubb.bid = ubr.bid WHERE ubr.rid = %d
I use this online service http://jonduell.com/sql-parser. It always worked good for my needs. -- Sivaji http://sivaji.drupalgardens.com @sivaji_ganesh in twitter sivaji2009@gmail.com Gtalk / Skype: sivaji2009 Mobile: +91 9941 571 690
Same as Boobatower's. Not working for me. Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
________________________________ From: sivaji j.g <sivaji2009@gmail.com> To: support@drupal.org; Ms. Nancy Wichmann <nan_wich@bellsouth.net> Sent: Tuesday, October 11, 2011 1:05 PM Subject: Re: [support] Converting a query to D7
On Tue, Oct 11, 2011 at 9:05 PM, Ms. Nancy Wichmann <nan_wich@bellsouth.net> wrote:
Before I pull any more hair out, can someone properly convert this to a D7 select query, please: SELECT ubr.rid, ubb.* FROM {user_badges_roles} ubr INNER JOIN {user_badges_badges} ubb ON ubb.bid = ubr.bid WHERE ubr.rid = %d
I use this online service http://jonduell.com/sql-parser. It always worked good for my needs.
Nancy, I believe this can be because of some functions that cannot be chained, join is one of them, and that might be the reason you are facing issues. Check this for chaining - http://drupal.org/node/1060924. You can use this query like this $query = db_select('user_badges_roles', 'ubr') ->fields('ubr', array('rid', 'ubb')) ->join('user_badges_badges', 'ubb', 'ubb.bid = ubr.bid'); $query = condition('ubr.rid', '@todo Set the appropriate argument.') ->execute(); Try this, and you might get some success On Tue, Oct 11, 2011 at 10:45 PM, Ms. Nancy Wichmann <nan_wich@bellsouth.net
wrote:
Same as Boobatower's. Not working for me.
*Nancy*
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
------------------------------ *From:* sivaji j.g <sivaji2009@gmail.com> *To:* support@drupal.org; Ms. Nancy Wichmann <nan_wich@bellsouth.net> *Sent:* Tuesday, October 11, 2011 1:05 PM *Subject:* Re: [support] Converting a query to D7
On Tue, Oct 11, 2011 at 9:05 PM, Ms. Nancy Wichmann <nan_wich@bellsouth.net> wrote:
Before I pull any more hair out, can someone properly convert this to a D7 select query, please: SELECT ubr.rid, ubb.* FROM {user_badges_roles} ubr INNER JOIN {user_badges_badges} ubb ON ubb.bid = ubr.bid WHERE ubr.rid = %d
I use this online service http://jonduell.com/sql-parser. It always worked good for my needs.
-- [ Drupal support list | http://lists.drupal.org/ ]
-- Regards, Vaibhav Jain
participants (6)
-
Carl Wiedemann -
Dipen -
Michael Prasuhn -
Ms. Nancy Wichmann -
sivaji j.g -
Vaibhav Jain