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.

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();