I have a front end list view for a table called #_students and a separate front end list view for a table called #_sessions. The two tables have no shared foreign key and the two data sets are completely unrelated.
I want to display the data from both tables in the same list view, in the following manner:
- session 1
- student 1
- student 2
- student 3
- session 2
- student 1
- student 2
- student 3
- session 3
- student 1
- student 2
- student 3
The point of this is that eventually, I want to add radio buttons next to each student name under each session name and allow the front end user to assign certain students to certain sessions. Upon clicking a 'submit' button, the results will be written to a third table, #_orders.
The trouble is, all of the articles I can find on joining two list views assume there is a shared key between the two tables and suggest using an SQL join. I do not have a shared key. Should I look into SQL join or can I do two separate SQL queries within the same model?
Here are the key functions from models/sessions.php:
public function __construct($config = array())
{
if (empty($config['filter_fields']))
{
$config['filter_fields'] = array(
'id', 'a.id',
'ordering', 'a.ordering',
'state', 'a.state',
'created_by', 'a.created_by',
'modified_by', 'a.modified_by',
'date', 'a.date',
'time', 'a.time',
'title', 'a.title',
'teacher', 'a.teacher',
'lvl', 'a.lvl',
'notes', 'a.notes',
'jr', 'a.jr',
'cap', 'a.cap',
'att', 'a.att',
'sid', 'a.sid',
);
}
parent::__construct($config);
}
protected function getListQuery()
{
// Create a new query object.
$db = $this->getDbo();
$query = $db->getQuery(true);
// Select the required fields from the table.
$query
->select(
$this->getState(
'list.select', 'DISTINCT a.*'
)
);
$query->from('`#__merakiac_sessions` AS a');
// Join over the users for the checked out user.
$query->select('uc.name AS uEditor');
$query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out');
// Join over the created by field 'created_by'
$query->join('LEFT', '#__users AS created_by ON created_by.id = a.created_by');
// Join over the created by field 'modified_by'
$query->join('LEFT', '#__users AS modified_by ON modified_by.id = a.modified_by');
return $query;
}
public function getItems()
{
$items = parent::getItems();
return $items;
}
Here are the key functions from models/students.php:
public function __construct($config = array())
{
if (empty($config['filter_fields']))
{
$config['filter_fields'] = array(
'id', 'a.id',
'ordering', 'a.ordering',
'state', 'a.state',
'created_by', 'a.created_by',
'modified_by', 'a.modified_by',
'parent', 'a.parent',
'name', 'a.name',
'lvl', 'a.lvl',
'jr', 'a.jr',
);
}
parent::__construct($config);
}
protected function getListQuery()
{
// Create a new query object.
$db = $this->getDbo();
$query = $db->getQuery(true);
// Select the required fields from the table.
$query
->select(
$this->getState(
'list.select', 'DISTINCT a.*'
)
);
$query->from('`#__merakiac_student` AS a');
// Join over the users for the checked out user.
$query->select('uc.name AS uEditor');
$query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out');
// Join over the created by field 'created_by'
$query->join('LEFT', '#__users AS created_by ON created_by.id = a.created_by');
// Join over the created by field 'modified_by'
$query->join('LEFT', '#__users AS modified_by ON modified_by.id = a.modified_by');
// Join over the created by field 'parent'
$query->join('LEFT', '#__users AS parent ON parent.id = a.parent');
return $query;
}
public function getItems()
{
$items = parent::getItems();
return $items;
}
And here is the site/views/sessions/tmpl/default.php (and it still needs work as I will need to somehow nest students' foreach loop within sessions' foreach loop):
<form action="<?php echo JRoute::_('index.php?option=com_merakiac&view=sessions'); ?>" method="post"
name="adminForm" id="adminForm">
<table>
<?php foreach ($this->items as $i => $item) : ?>
<?php echo $item->fieldnames; ?>
<?php endforeach; ?>
</table>
</form>
<form action="<?php echo JRoute::_('index.php?option=com_merakiac&view=students'); ?>" method="post"
name="adminForm" id="adminForm">
<table>
<?php foreach ($this->items as $i => $item) : ?>
<?php echo $item->fieldnames; ?>
<?php endforeach; ?>
</table>
</form>
Can somebody help me out with how to combine students.php's SQL query into sessions.php's SQL query and display the results of both in sessions/default.php? Is it even the SQL query which I must modify or the getItems()
function? Do I even need the __construct($config = array())
function? I am new to Joomla and its SQL syntax. Any help would be greatly appreciated. Thank you!