Re: [development] node_load using cck fields

24 Mar 2011

      Searching more I found http://drupal.org/node/360541 which was nearly
what I was looking for. I changed that function because it was
returning a "node"; so now it returns an array of nids and then I can
use node_load() to load them (in my case just one). It also uses
db_rewrite_sql() to ensure you can only load nodes accessible to you.

The final function is:

function _content_node_loads($param = NULL) {
  if (!is_array($param)) {
    return FALSE;
  }
  $arguments = array();
  $cck_tables = array();
  $cck_cond = array();
  $cond = array();
  $i = 1;
  foreach ($param as $key => $value) {
    if (substr($key, 0, 6) != 'field_') {
      $cond[] = 'n.'. db_escape_table($key) ." = '%s'";
      $arguments[] = $value;
    }
    else{
      $db_info = content_database_info(content_fields($key));
      // If the field is from another table, increment sequence
      if(!isset($cck_tables[$db_info['table']])){
        $cck_tables[$db_info['table']] = 'cck'. $i;
      }
      $i++;
      if($db_info['columns']['nid']){
        // For nodereference fields
        $cck_cond[] = $cck_tables[$db_info['table']] .'.'.
db_escape_table($db_info['columns']['nid']['column']) ." = '%s'";
      } else if ($db_info['columns']['value']) {
        $cck_cond[] = $cck_tables[$db_info['table']] .'.'.
db_escape_table($db_info['columns']['value']['column']) ." = '%s'";
      } else if ($db_info['columns']['uid']) {
        $cck_cond[] = $cck_tables[$db_info['table']] .'.'.
db_escape_table($db_info['columns']['uid']['column']) ." = '%s'";
      }else if ($db_info['columns'][$key]['column']) {
        $cck_cond[] = $cck_tables[$db_info['table']] .'.'.
db_escape_table($db_info['columns'][$key]['column']) ." = '%s'";
      }
      $arguments[] = $value;
    }
  }
  $cond = (!empty($cond) ? implode(' AND ', $cond) : '' );
  if (!empty($cck_cond)) {
    $cond .= (!empty($cond) ? ' AND ' : ''). implode(' AND ', $cck_cond);
    foreach ($cck_tables as $table => $nick) {
      $cck_join .= ' INNER JOIN {'. $table .'} '. $nick .' ON '. $nick
.'.nid = n.nid';
    }
  }
  $sql = "SELECT n.nid FROM {node} n ".$cck_join." WHERE ".$cond;
  $res = db_query(db_rewrite_sql($sql), $param);
  $nids = array();
  while ($nid = db_result($res)) {
    $nids[$nid] = $nid;
  }
  return (empty($nids) ? FALSE : $nids);
}

2011/3/23 Lluís Forns <enboig@gmail.com>:
...
I finished my function, I just added support for nodereference fields
because this is what I want; maybe when I need more support I will add
it. I post the code here, any comment is wellcome.
function node_load_cck($param = array()) {
 if (is_numeric($param)) {
   return node_load($param);
 }
 $select = "SELECT n.nid FROM {node} n ";
 $join = '';
 $where = ' WHERE 1 ';
 $data = array();
 //contains array( 'table' => 'alias');
 $tables = array();
 $i = 0;
 foreach($param as $field_code => $field_value) {
   $data[] = $field_value;
   if (substr($field_code, 0, 6) == 'field_') {
     $field = content_fields($field_code);
     switch ($field['type']) {
       case 'nodereference':
         $db_info = content_database_info($field);
         if (!in_array($db_info['table'], $tables)) {
           $tables[$db_info['table']] = 't'.$i;
           $join .= ' LEFT JOIN {'.$db_info['table'].'} '.'t'.$i.' ON
n.vid = '.'t'.$i.'.'.vid.' ';
         }
         $where .= " AND
".$tables[$db_info['table']].'.'.$db_info['columns']['nid']['column']."
= '%s' ";
       break;
       default:
         //TODO
     }
   }
   else {
     $where .= " AND n.".$field_code." = '%s' ";
   }
   $i++;
 }
 $sql = $select . ' ' . $join . ' ' . $where;
 $nid = db_result(db_query($sql, $data));
 $node = node_load($nid);
 return $node;
}
2011/3/23 Lluís Forns <enboig@gmail.com>:
...
Sorry, I didn't explain myself. I have read the article and I am
writing a "query builder" that loads a node matching received $params.
My concert right now is that following the example
content_database_info should return something like:
array(2) {
 ["table"]=>
 string(17) "content_type_date"
 ["columns"]=>
 array(2) {
   ["value"]=>
   array(6) {
     ["type"]=>
     string(7) "varchar"
     ["length"]=>
     int(20)
     ["not null"]=>
     bool(false)
     ["sortable"]=>
     bool(true)
     ["views"]=>
     bool(true)
     ["column"]=>
     string(16) "field_date_value"
   }
   ["value2"]=>
   array(6) {
     ["type"]=>
     string(7) "varchar"
     ["length"]=>
     int(20)
     ["not null"]=>
     bool(false)
     ["sortable"]=>
     bool(true)
     ["views"]=>
     bool(false)
     ["column"]=>
     string(17) "field_date_value2"
   }
 }
}
but in my case it returns:
field_alumne => Array
(
   [table] => content_field_alumne
   [columns] => Array
       (
           [nid] => Array
               (
                   [type] => int
                   [unsigned] => 1
                   [not null] =>
                   [index] => 1
                   [column] => field_alumne_nid
               )
       )
)
I assume the difference is because I access a related node and not data itself.
2011/3/22 Michael Prasuhn <mike@mikeyp.net>:
...
It's pretty clear that you did not read the linked article. It is perfectly
safe to use CCKs API to find out what tables a field's data is stored in.
How do you think that Views is able to get at the correct table?
-Mike
Guillaume ! wrote:
...
That will work in most cases, but if you ever re-use one of the fields
of the date content type with another content type, the field will be
moved to its own table (content_field_date). The safe way is really of
using views, or node_load, but there are obvious scalability issues
with foreach($a = node_load($nid++)), because they are really
independant of the content_type current structure. See
http://groups.drupal.org/node/10129 for more details.
On 11-03-22 09:47 AM, Steve Edwards wrote:
...
Here is the magic incantation:
http://drewish.com/content/2010/06/correctly_accessing_cck_fields_in_sql_que...
--
__________________
Michael Prasuhn
503.512.0822 office
mike@mikeyp.net
http://mikeyp.net
--
*Les normes hi són perquè hi pensis abans de saltar-te-les
*La vida és com una taronja, què esperes a exprimir-la?
*Si creus que l'educació és cara, prova la ignorància.
*La vida és com una moneda, la pots gastar en el que vulguis però
només una vegada.
*Abans d'imprimir aquest missatge, pensa en el medi ambient.
--
*Les normes hi són perquè hi pensis abans de saltar-te-les
*La vida és com una taronja, què esperes a exprimir-la?
*Si creus que l'educació és cara, prova la ignorància.
*La vida és com una moneda, la pots gastar en el que vulguis però
només una vegada.
*Abans d'imprimir aquest missatge, pensa en el medi ambient.
-- 
*Les normes hi són perquè hi pensis abans de saltar-te-les
*La vida és com una taronja, què esperes a exprimir-la?
*Si creus que l'educació és cara, prova la ignorància.
*La vida és com una moneda, la pots gastar en el que vulguis però
només una vegada.
*Abans d'imprimir aquest missatge, pensa en el medi ambient.