0

a table:

CREATE TABLE `a` (`id` int(10));INSERT INTO `a` (`id`) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

I need to make a cross join tables in drupal using db_select:

$query = db_select('num', 'n1');    
$query -> fields('n1', array('i'));    
$query -> innerJoin('num', '10');    
$query -> fields('n10', array('i'));    
watchdog(__FUNCTION__, $query -> __toString(), $query -> getArguments());    
$result = $query -> execute();    
$records = $result -> fetchAllAssoc('i'); 

get as a result:

Array ( [0] => stdClass token Object ( [i] => 0 [n10_i] => 9 ) [1] => stdClass token Object ( [i] => 1 [n10_i] => 9 ) [2] => stdClass token Object ( [i] => 2 [n10_i] => 9 ) [3] => stdClass token Object ( [i] => 3 [n10_i] => 9 ) [4] => stdClass token Object ( [i] => 4 [n10_i] => 9 ) [5] => stdClass token Object ( [i] => 5 [n10_i] => 9 ) [6] => stdClass token Object ( [i] => 6 [n10_i] => 9 ) [7] => stdClass token Object ( [i] => 7 [n10_i] => 9 ) [8] => stdClass token Object ( [i] => 8 [n10_i] => 9 ) [9] => stdClass token Object ( [i] => 9 [n10_i] => 9 ) )

watchdog: SELECT n1.i AS i, n10.i AS n10_i FROM {num} n1 INNER JOIN {num} n10

this query in phpmyadmin gives the "multiplication" of tables: 100 rows that I deserve.

why not works through db_select in drupal?

db_select need to connect this query to a different sample already on tables Drupal, db_query would not help me =/

  • 1
    Please post the SQL query string that your current code produces, and the query string that you want to be produced – Clive Feb 21 '14 at 12:11
  • watchdog recorded query in the log true, this for me. as I wrote, if you run it, for example through phpmyadmin, the result we get a (en.wikipedia.org/wiki/Join_(SQL)#Cross_join). the query that I need to get: SELECT n1.i AS i, n10.i AS n10_i FROM {num} n1 INNER JOIN {num} n10 if you look a result that throws db_select, we will see that the first table is selected completely, and from the 2nd "attached" only the last line. – masdzen Feb 21 '14 at 12:41
  • I should have been more clear - please edit the question, and add these two pieces of information: 1. The exact SQL query that you are trying to get, and 2. The exact SQL query you are currently getting with the code you've already provided. Linking to the Wikipedia page for cross joins is no good, we need to see your code if you want to get an answer – Clive Feb 21 '14 at 13:03
  • Could you also elaborate on what you mean by "db_query would not help me"? db_query runs an arbitrary SQL statement against the database, so as long your SQL query string is correct, db_query will definitely work. When you add your desired SQL output to the question we can tackle that part too – Clive Feb 21 '14 at 13:07
0

innerJoin takes 3 args: table, table_alias, and condition. ex:

$query->innerjoin('node', 'n', 'c.entity_id=n.nid')

The above would produce "...INNER JOIN node n ON c.entity_id = n.nid ..." You can add the fields form node by doing:

$query->fields('n', array(...));

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.