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

/**
* 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

N1ghteyes’s picture

Still stuck with this :/

----

Sent from my S4

N1ghteyes’s picture

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.

/**
* 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.