Hi, I have created an app which has two many to many module relations. The problem I have is that when I go to /show/id to add the associated records, the modal dropdown is displaying duplicates of one of the records three times. I have checked the database and the record only occurs once in the table.

Hi DaddyJohn. Can you give us some idea of the table structure of your app? I'm guessing that this has something to do with the configuration of your table 'joins', the mechanism by which the database determines what results to return when comparing data in more than one table.

Hi, the app is for a GP surgery I am creating. There are three main table and two associated tables for the many to many relationships.

Priority Table, with id, title and description (this table allows users to create different levels of priority for how to deal with patients).
Conditions Table, with id, title and description (this table allows doctors to create a range of conditions/ illnesses).
Body Parts Table, with id, title and description (this table allows doctors to create a body part e.g. eyes, heart etc.

The Conditions have a many to many with the Priority table and so we have a table associated_priorities_and_conditions
The Body Parts have a many to many with Conditions and so we have a table associated_body_parts_and_conditions

It is the Body Parts dropdown that I have the issue with.

I hope that is clear.

Have you turned on Debug in the model.php file in the engine folder to see your query's?
If so you can copy your query's and post to help sort this out.

OK, so let's try to break it down a little more.

I've made a number of assumptions, the foremost being that you have a 'patients' table of some kind - to embody each patient as an entity in the system. So, in your system:

1. A patient can be associated with a single 'priority' (by some diagnosis process). I've assumed a patient can only have one 'priority' at any given time. An example here would be: John Doe is set as a 'high priority' patient - his priority is set independently of any 'conditions' he has (but is obviously loosely related to those conditions).

2. A patient can be associated with one or more afflicted 'body parts'. For example, Patient 'John Doe' has a body part 'hand' that has an associated condition 'arthritis'; but he doesn't have an associated 'nose' record (because his nose is not afflicted with any condition).

3. A patient's associated body part record can then be associated with a condition, e.g. John Doe's body part 'hand' is associated with the condition 'arthritis'.

Close,

there is no patient table. Under GDPR we are not allowed to add in the patient details.

The app is meant to be an aid to the team who take calls from patients.

They will be able to view the app which will help them to decide how to triage the patient i.e. Priority 1,2,3,4 or 5.

The doctors will 'prime' the app by providing the data.

1) There are a long list of conditions.
2) Each condition could be associated with one or more body parts.
3) Each condition can be a priority 1, 2, 3,4 or 5

Most conditions will actually only ever have one priority, but there maybe times when they do need more, hence this has a many to many relationship as well.

So in summary,

Priority table, Conditions table, Body Parts table.
Priority to Conditions - many to many
Conditions to Body Parts - many to many.

Ah OK. That's outside my comfort zone in complexity. Good luck with it.

Using associations is trial and error getting the right join ons.
I would suggest creating another app and trying different many to many joins till you see what works

Do you need to create the many to many?
What if you just used a dropdown in the controller and pass it to the form?
function create() {
$data['author_options'] = $this->_get_author_options($data['author_id']);
$data['view_file'] = 'create';
$this->template('admin', $data);
}
function _get_author_options($selected_author_id) {
$rows = $this->model->get('last_name', 'authors');

if ($selected_author_id == '') {
$options[''] = 'Select author...';
}

foreach($rows as $row) {
$options[$row->id] = $row->first_name.' '.$row->last_name;
}

return $options;
}
echo form_label('Author');
echo form_dropdown('author_id', $author_options, $author_id);
Adjust to fit your table

Hi John,

I'm coming in a bit late on this thread, but I would suggest adding a DISTINCT keyword to your select SQL for the Body Parts dropdown. See this link at W3 Schools https://www.w3schools.com/sql/sql_distinct.asp

It should eliminate the repeating conditions in your dropdown.

If not could you please share the SQL of the Body Parts dropdown and a screenshot of what you see?