Newbie Q - controller/view code for mixed query

[eluser]vinofilus[/eluser]
Sorry, that title doesn't even make sense to me. Here's what I'm trying to do

I have a table called Tasks (id, title, date, details),
and a table called Comments (id, task_id, date, details).

Each task is unique, and many Comments can refer to one task.

I'd like to show a table with the following columns:
Task Title | Task Details | Task Date | Number of Comments | Link to add comments

I can't figure out how to get the "Number of Comments" part to work.

The index function of my controller has:
$data['query'] = $this->db->get('Tasks');
$this->load->view('projects_view', $data);

and my projects_view has a foreach loop and link using code like:
<?php foreach($query->result() as $row): ?>
<tr><td>&lt;?=$row->title?&gt;</td><td>&lt;?=$row->details?&gt;</td><td>&lt;?=$row->date?&gt;</td>
<td>PROBLEM</td>
<td>&lt;?=anchor('projects/comments/'.$row->id, 'Comments');?&gt;</td>
</tr>
&lt;?php endforeach; ?&gt;

The comments()function in my controller shows all the comments related to a particular task just fine, with this:
$this->db->where('entry_id', $this->uri->segment(3));
$data['query'] = $this->db->get('Comments');
$this->load->view('comment_view', $data);

All of that works fine except for the PROBLEM cell on the Tasks where I want to show the number of comments.
I can figure out the sql on my command line to show me the number of comments for each task_id using COUNT(Comments.task_id) and JOINing Comments.task_id = Task.id, but I can not figure out how to have the controller also do that sql work and pass it along to the view.

I might be thinking about this totally illogically. I welcome any feedback, solutions, re-direction, or further questions.
You can even make me start over.
This is more about learning how to do this than just getting it done, so I welcome your thoughts.

Many thanks.
Les...

[eluser]John_Betong_002[/eluser]
Play around with this:

Code:

SELECT
   `comments`.*, `tasks`.`*`
FROM
    (`comments`)
JOIN
   `tasks` ON `comments`.`task_id` = `tasks`.`id`
ORDER BY
   `comments`.`id` desc

&nbsp;
&nbsp;
&nbsp;

[eluser]cideveloper[/eluser]
and please post your code in code tags so it looks like

Code:

this

Its just easier to read

[eluser]smithy000[/eluser]
[quote author="cideveloper" date="1308215842"]and please post your code in code tags so it looks like

Code:

this

Its just easier to read[/quote]
I agree with u.

[eluser]boltsabre[/eluser]
For starters you should be doing this in your model, not in your controller.

And what is this:

$this->db->where(‘entry_id’, $this->uri->segment(3));

in your comments function - from your 2 db table structures you posted I cannot see any field/column called entry_id.

But just play around with what John_Betong_002 has posted, and check the online documentation regarding db queries and count.

[eluser]vinofilus[/eluser]
[quote author="John_Betong_002" date="1308212576"]Play around with this:

Code:

SELECT
   `comments`.*, `tasks`.`*`
FROM
    (`comments`)
JOIN
   `tasks` ON `comments`.`task_id` = `tasks`.`id`
ORDER BY
   `comments`.`id` desc

[/quote]

Thanks for the pointer. I'll try to do better.

[eluser]vinofilus[/eluser]
[quote author="boltsabre" date="1308234328"]For starters you should be doing this in your model, not in your controller.

And what is this:

$this->db->where(‘entry_id’, $this->uri->segment(3));

in your comments function - from your 2 db table structures you posted I cannot see any field/column called entry_id.

But just play around with what John_Betong_002 has posted, and check the online documentation regarding db queries and count.[/quote]

Oops. I was copying from several test files. entry_id should have been task_id.

But more importantly, as a complete newbie to CodeIgniter/MVC world, I'll take your comment about doing this in my model to heart. Based on information on the site (maybe a tutorial) that said the model could be used but didn't need to be, I haven't explored that yet. But I will.

When I last did PHP/mysql stuff a few years ago, it was old school and manual - write queries and then write php to process, loop, display, etc., all in a few main files. It was admittedly messy. I'm eager to make the transition, but keep finding myself thinking the old way.

For instance in my initial question, my thought pattern was still to write some queries in the controller, pass the variables to the view, and then process them. But then I was stuck with how to concatenate all the data into the variable array I was passing, and just really wanted to make a second query in my view file.

Perhaps my smartest next steps are a) do some discovery about proper use of models, and b) find some properly written CodeIgniter projects and just spend time understanding them.

Any learning recommendations are still welcome.

Thanks again for your responses.
Les...

[eluser]InsiteFX[/eluser]
CodeIgniter User Guide - Database Active Record

InsiteFX

[eluser]Nick_MyShuitings[/eluser]
If you feel comfortable with SQL, then skip the use of Active Record and use query bindings. Still read the user guide as InsiteFX mentioned... Active Record is for if you (or future code maintainers) can't wrap their heads around SQL. (and its moderately convenient for Inserts and Updates... but for Selects... bah)

[eluser]osci[/eluser]
@Nick_MyShuitings

I feel comfortable with sql but I don't see why I shouldn't use active record. It's easy, debugging mostly needs the profiler enabled to see your generated queries and if you can wrap your head around sql you can fix it. And AR doesn't do that much more (it's just a wrapper with security), I don't think it's degrading speed that much (mentioned in another recent post of your's in a similar comment with the above).

It might have some flaws like OR vs. AND in Active Record LIKE query but still by using it we give back and a better product is created. And when you can't find a way with AR then hardcode your sql like you say.