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
Still stuck with this
Still stuck with this :/
----
Sent from my S4
RTFM!
For further support or projects contact me.
Source Control - Web Design, Development and Hosting Oxfordshire, UK
Just incase anyone else gets
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.
i also changed the call to return an array, as its only ever going to be one row.
RTFM!
For further support or projects contact me.
Source Control - Web Design, Development and Hosting Oxfordshire, UK