Ok, so i have 2 tables, i want to join them on an id. so i have this:

<?php
/**
* function to get all current pos data by a given id
* @param $gid
*       int - given id, ether corp or item
* @param $type
*       string - database field for the id we're loading on.
* @return Object
*       object of joined data as returned by fetchAll()
*/
function mmi_pos_core_load_all_by_givenid($gid, $type = 'itemID'){
   
$posdata = db_select('mmi_pos_core_towerlist', 'tl');
   
$posdata->join('mmi_pos_core_towerdata', 'td', 'tl.itemID = td.itemID');
   
$posdata->fields('tl')
            ->
fields('td', array(
               
'usageFlags',
               
'deployFlags',
               
'allowCorporationMembers'
               
))
            ->
condition('tl.'.$type, $gid)
            ->
execute()
            ->
fetchAll();
   
watchdog('load all by id', $posdata);
   
mmi_watchdog('load all by id result', $gid, $posdata);
    return
$posdata;
}
?>

When the above runs, i get no results (as checked by mmi_watchdog), desipite having data in both tables. The query being run (as returned by watchdog):

SELECT tl.*, td.usageFlags AS usageFlags, td.deployFlags AS deployFlags, td.allowCorporationMembers AS allowCorporationMembers FROM {mmi_pos_core_towerlist} tl INNER JOIN {mmi_pos_core_towerdata} td ON tl.itemID = td.itemID WHERE (tl.itemID = :db_condition_placeholder_0)

db_condition_placeholder_0 contains the id (it is set, i checked).

When i run the query output in watchdog, directly against the database (using mysql workbench), i get the expected results. So, whats going on?

Something else i noticed, specifying ->fields('td')->fields('tl') without an array of fields for the second argument, malforms the query, it becomes:

select td.*, tl.*, from a_table inner join <some more query here>

Note the trailing comma after tl.* that shouldnt be there.

So, are joins broken at the moment? or am i missing something

Comments

Still stuck with this :/

----

Sent from my S4

RTFM!
Issue solved? Add [Solved] to the issue title.
For further support or projects contact me.


The Communications Group

Just incase anyone else gets stuck with this, unlike other db_select queries, you have to assign the query object to a new var after ->join, rather than just continuing as normal.

<?php
/**
* function to get all current pos data by a given id
* @param $gid
*       int - given id, ether corp or item
* @param $type
*       string - database field for the id we're loading on.
* @return Object
*       object of joined data as returned by fetchAll()
*/
function mmi_pos_core_load_all_by_givenid($gid, $type = 'itemID'){
   
$posdata = db_select('mmi_pos_core_towerlist', 'tl');
   
$posdata->join('mmi_pos_core_towerdata', 'td', 'tl.itemID = td.itemID');
   
$posdata = $posdata
           
->fields('tl')
            ->
fields('td')
            ->
condition('tl.'.$type, $gid)
            ->
execute()
            ->
fetchAssoc();
    return
$posdata;
}
?>

i also changed the call to return an array, as its only ever going to be one row.

RTFM!
Issue solved? Add [Solved] to the issue title.
For further support or projects contact me.


The Communications Group