Using:

$results = db_query('SELECT q1.entity_id, q1.field_category_question_1_value
                    FROM field_data_field_category_question_1 AS q1
                    UNION ALL
                    SELECT q2.entity_id, q2.field_category_question_2_value
                    FROM field_data_field_category_question_2 AS q2')->fetchAll();

results in:

Array
(
    [0] => stdClass Object
        (
            [entity_id] => 20
            [field_category_question_1_value] => This is question 1 for historical hits?
        )

    [1] => stdClass Object
        (
            [entity_id] => 20
            [field_category_question_1_value] => This is question 2 for historical hits?
        )

)

The second object should have the property name field_category_question_2_value, instead of field_category_question_1_value, right?

Ideally, I would like to merge the properties into one object where the entity_ids are the same. Is there in anyway to do this using some fetch method?

A little more explanation: A content type called category has two questions fields (field_category_question_1 and field_category_question_2). With two categories (Historical hits and It's a Mystery) in the DB, the results are:

Array
(
    [0] => stdClass Object
        (
            [entity_id] => 20
            [field_category_question_1_value] => This is question 1 for historical hits?
        )

    [1] => stdClass Object
        (
            [entity_id] => 28
            [field_category_question_1_value] => Question 1 for It's a Mystery?
        )

    [2] => stdClass Object
        (
            [entity_id] => 20
            [field_category_question_1_value] => This is question 2 for historical hits?
        )

    [3] => stdClass Object
        (
            [entity_id] => 28
            [field_category_question_1_value] => Questions 2 for It's a Mystery?
        )

)

And my current workaround for this is:

    $results_halfway = count($results) / 2;
    foreach($results as $index=>$result) {
      if ($index < $results_halfway) {
        $categories[$result->entity_id] = array(
          $result->field_category_question_1_value,
          $results[$results_halfway + $index]->field_category_question_1_value,
        );
      } else {
        break;
      }
    }

Comments

posulliv’s picture

posulliv CreditAttribution: posulliv commented

This is not an issue with db_query per se. With a UNION query, the column names from the first SELECT statement are used as the column names for the results returned. For more information, see the MySQL docs.

As for your question on your query, an alternative query might be:

SELECT
  q1.entity_id AS entity_id, 
  q1.field_category_question_1_value AS field_category_question_1_value,
  q2.field_category_question_2_value AS field_category_question_2_value
FROM
  field_data_field_category_question_1 AS q1, 
  field_data_field_category_question_2 AS q2
WHERE 
 q1.entity_id = q2.entity_id

Example of doing this with dummy tables and data:

 mysql> select entity_id, what from t1 union all select entity_id, what from t2;
+-----------+-----------------------------------------+
| entity_id | what                                    |
+-----------+-----------------------------------------+
|        20 | This is question 1 for historical hits? |
|        28 | Question 1 for It's a Mystery?          |
|        20 | This is question 2 for historical hits? |
|        28 | Questions 2 for It's a Mystery?         |
+-----------+-----------------------------------------+
4 rows in set (0.00 sec)

mysql> select t1.entity_id, t1.what, t2.what from t1, t2 where t1.entity_id = t2.entity_id;
+-----------+-----------------------------------------+-----------------------------------------+
| entity_id | what                                    | what                                    |
+-----------+-----------------------------------------+-----------------------------------------+
|        20 | This is question 1 for historical hits? | This is question 2 for historical hits? |
|        28 | Question 1 for It's a Mystery?          | Questions 2 for It's a Mystery?         |
+-----------+-----------------------------------------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+-----------+-----------------------------------------+
| entity_id | what                                    |
+-----------+-----------------------------------------+
|        20 | This is question 1 for historical hits? |
|        28 | Question 1 for It's a Mystery?          |
+-----------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+-----------+-----------------------------------------+
| entity_id | what                                    |
+-----------+-----------------------------------------+
|        20 | This is question 2 for historical hits? |
|        28 | Questions 2 for It's a Mystery?         |
+-----------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql>

So then your PHP code would look something like:

    $results = db_query(. .. );
    foreach($results as $index=>$result) {
      $categories[$result->entity_id] = array(
        $result->field_category_question_1_value,
        $result->field_category_question_2_value
      );
    }

Version: 7.17 » 7.x-dev

Core issues are now filed against the dev versions where changes will be made. Document the specific release you are using in your issue comment. More information about choosing a version.