Building a task listing for a task manager

Question

I'm super new to CodeIgniter 3 and I'm using it to build a task manager (Claritask.com). Also, I'm just starting to learn how to program, so forgive me beforehand if I'm not making sense and I'll try to clarify in comments.

As I'm building my specific Project page that lists specifc tasks, I was curious to know if I'm doing it right.

What I need on my view is: tasks belonging to a specific Project & the name of the project. I can get this to work fine, however I want to learn if my way of doing it correct.

I'm using two various methods

The first method I'm using, deals with having two functions on my Model, triggering two queries:

// Show project tasks
function get_tasks($project_id) {
 $this->db->from('tasks');
 $this->db->where('tasks.project_id', $project_id);
 $this->db->order_by('tasks.created_date', 'DESC');
 return $this->db->get()->result_array(); 
}
// Show single project name
function get_single_project($project_id) {
 $this->db->from('projects');
 $this->db->where('projects.id', $project_id);
 return $this->db->get()->row_array(); 
}

Then on my controller

$data['tasks'] = $this->project_model->get_tasks($project_id);
$data['project_name'] = $this->project_model->get_single_project($project_id)['project_name'];

This is working as expected, and producing an elegant array (especially on projects without tasks).

array (
 'tasks' => 
 array (
 ),
 'project_name' => 'Default name',
)

However I didn't like triggering two different queries, so I decided to JOIN projects with tasks and have one hit to the database, as such:

// Show project tasks
function get_project_tasks($project_id) {
 $this->db->select("tasks.*, projects.project_name");
 $this->db->from('projects');
 $this->db->where('projects.id', $project_id);
 $this->db->join('tasks', 'tasks.project_id = projects.id', "LEFT");
 $this->db->order_by('tasks.created_date', 'DESC');
 return $this->db->get()->result_array(); 
}

and then my controller:

$data['tasks'] = $this->project_model->get_project_tasks($project_id);
$data['project_name'] = $data['tasks'][0]['project_name'];

While the second approach is using one query only, It's also producing an "empty" array (below) on projects without any tasks and causing to print at least one empty line within a 'foreach'.

array (
 'tasks' => 
 array (
 0 => 
 array (
 'id' => NULL,
 'project_id' => NULL,
 'list_id' => NULL,
 'task_text' => NULL,
 'task_due_date' => NULL,
 'created_by' => NULL,
 'created_date' => NULL,
 'project_name' => 'Website design',
 ),
 ),
 'project_name' => 'Website design',
)

Even though, I'm mitigating that, with the following:

<?php foreach($tasks as $task) { ?>
 <?php if(isset($task['id'])): ?>
 <div><?php echo $task['task_text']; ?></div>
 <?php endif; ?>
<?php } ?>

Even though there are thousands of ways to skin a cat, I would like to really know which approach is the correct one: Having a specific query for each element and printing an elegant output, or optimizing the outcome.

• October Eleven: hey @MathiasEttinger thanks for clarifying. I've changed the title to be more specific about my question. Also, how do I note that it's a cross post — I didn't want to remove the first one, since there are some comments in it (could be useful for someone)
• October Eleven: Ok / tried again :) Hope this title helps
• October Eleven: Hi everyone this is a cross post stackoverflow.com/questions/45845814/… / left the original since there are some comments there that could be helpful for a complete noob like me

Answer 1

This is really a SQL question, I'd encourage you to keep learning true SQL because it's a skill that's invaluable and it's increasingly not learnt thanks to the popularisation of noSQL solutions and abstraction heavy ORMs

To answer your question swap the direction of the query from tasks to projects instead of from projects to tasks, this will ensure you only get projects that have tasks, alternatively you could have added an extra condition in your where clause to exclude NULL tasks on the left join

or you can simply swap the left join to a normal join

$this->db->select("tasks.*, projects.project_name");
$this->db->from('tasks');
$this->db->where('projects.id', $project_id);
$this->db->join('projects', 'tasks.project_id = projects.id', "LEFT");
$this->db->order_by('tasks.created_date', 'DESC');
return $this->db->get()->result_array(); 

• October Eleven: I've tried this @arcanine / though if you have no tasks, no project_id will be returned.
• arcanine: Oh I see you are looking to keep both and display them separately? If so two queries is an acceptable approach, one query per "component" on the page seems acceptable to me performance wise
• October Eleven: Did you mean "...two queries is an unacceptable..." (not acceptable)? @arcanine
• October Eleven: I did it by the way with two queries / and some other problems cleared out as well / is this the right way to do it @arcanine?
• arcanine: yes I believe so a screenshot of how you intend to display the data might help confirm it, but from what you've said so far, yes