DBRL and table joins

Hi all,

on my forms for displaying upcoming events and event details I am facing the following prob:
on eventlist upcoming events are listed with the event name linked to the respective event details.

<a href="<?php echo JRoute::_('index.php?option=com_chronoforms&chronoform=eventdetails&token='.$detail['cf_uid'].'&Itemid='. JRequest::getInt('Itemid')); ?>"><?php echo $detail['event_name']; ?></a>

event details should display all data of the event including details of the venue.
the following query should be met:

SELECT e.*, v.*
FROM jos_cf_data_events AS e
INNER JOIN jos_cf_data_venues AS v ON e.locid = v.cf_id
WHERE e.cf_uid = $token

currently I have:
DBMRL1:
DB Field:cf_uid
table: jos_cf_data_events
Request Param: token
Model ID: Event_Details
Load Data / Enable Associations: yes
Associated Models: Venues
WHERE statement: Event_Details.locid = Venues.cf_id
Array Fields Sets: perf_select

DBMRL2:
table: jos_cf_data_venues
Model ID: Venues
Load Data / Enable Associations: no

problem is that the token appears to being ignored as the details of more than 1 event is being displayed (I have 1 upcoming, 1 historic event)

either there is something wrong about the above setup or I would need associations enabled for the DBRL.

could somebody pls help me out?

Hi gabrielah,

I don't think that either the DB Record Loader or the DB Multi-Record Loader support compound queries :-(

You can probably do it with a Custom Code box.

Bob

Hi Bob,

is compound queries the same as queries with table joins ?
if so when displaying a list of records the DBMRL is working perfectly fine for that purpose. all it takes is setting up the associations.

the DBMRL cannot seem to display a specific record only (while associations work fine) while the DBRL can display a single record but does not allow for associations

Hi gabrielaH,

Ah. I didn't know that.

What happens if you set the Limit to 1 ?

Bob

with pagination enabled it will display 1 item per page
with pagination disabled it is being ignored

LIMIT 1 in the WHERE statement is working - currently.

debugging reveals the following queries:

SELECT * FROM `jos_chronoforms` WHERE `name` = 'eventdetails' AND `published` = '1'
SELECT * FROM `jos_chronoform_actions` WHERE `chronoform_id` = '11' ORDER BY `order` SHOW FIELDS FROM jos_chronoforms_data_events SHOW FIELDS FROM jos_chronoforms_data_venues
SELECT `Event_Details`.*, `Venues`.*
FROM `jos_chronoforms_data_events` AS `Event_Details`
INNER JOIN `jos_chronoforms_data_venues` AS `Venues`
WHERE Event_Details.locid = Venues.cf_id

the token/cf_uid is not included

part of the prob might be related to this snippet in the CfactionDbMultiRecordLoader class (its either the where statement or the request param/db field):

$table_field = $params->get('dbfield', '');
if(trim($actiondata->content1)){
	$where = " WHERE ".$this->_processWhere(trim($actiondata->content1), $form);
}else if(!empty($table_field)){
	$where = " WHERE `".$params->get('dbfield', '')."` = '".$form->data[$params->get('request_param', '')]."'";
	}else{
		$where = "";
	}