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!
