Welcome Guest, Not a member yet? Register   Sign In
Returning one data array from multiple MySQL queries
#1

[eluser]Funky Larma[/eluser]
After watching most of the CodeIgniter posts over at NetTuts I decided to give it a go and try to create a project management application.
So far I've got two controllers; Projects and Tasks. Each have a model that retrieves data from a MySQL database and matching views to display the results.

The Project model has a primary key/ID column and then all the details including a status enum. The Tasks model also has a primary key/ID but also a project ID column that references the project ID.

From my Projects controller I get all the projects with a status of 'In Progress' and collect in a array. Normally I pass this array through to the view and use a foreach loop to display each Project on a page.
What I'm stuck on now is how to display a count of all tasks under a Project.

I can't seem to get it straight in my head how best to approach this and get it working.
Is it better practice to create a function in the model that will return the task count with the project details to the controller? Or should I loop through the project details in the controller and try to obtain the task count this way?

Any help or ideas greatly received!
#2

[eluser]Ener1[/eluser]
You should check for "join" in the active record in the user guide

Cheers
#3

[eluser]Burak Guzel[/eluser]
I will give a raw SQL example.

So you are doing something like this to get projects in progress:

Code:
SELECT * FROM projects WHERE status = 'in progress'

But you also want to include the number of tasks each of those projects have. Here is how you do it:

Code:
SELECT projects.*, COUNT(*) AS num_tasks FROM projects
LEFT JOIN tasks ON(projects.id = tasks.project_id)
WHERE projects.status = 'in progress'
GROUP BY projects.id

You will get the same array, but this time there will also be a 'num_tasks' value for each project.
#4

[eluser]Ener1[/eluser]
Or using active record samething like this:

Code:
$this->db->select('projects.*');
$this->db->from('projects');
$this->db->join('tasks', 'projects.id = tasks.project_id','left');
$this->db->where('projects.status','in progress');
$this->db->groupby(projects.id);
$query = $this->db->get();
$quantity = $this->db->count_all_results();


Cheers
#5

[eluser]Funky Larma[/eluser]
Thank you for your comments, will try these and see how I get on
#6

[eluser]Funky Larma[/eluser]
Strangely a join is returning results that claim every project has one task. The task table only has one entry, so only one project should be returning this, rest as NULL I guess.
#7

[eluser]Ener1[/eluser]
Try changing this line
$this->db->join('tasks', 'projects.id = tasks.project_id','left');
for this one
$this->db->join('tasks', 'projects.id = tasks.project_id');


Cheers
#8

[eluser]suba[/eluser]
hi ener1,
small change in group by

Code:
$this->db->select('projects.*');
$this->db->from('projects');
$this->db->join('tasks', 'projects.id = tasks.project_id','left');
$this->db->where('projects.status','in progress');
$this->db->group_by(projects.id);// this is correct
$this->db->groupby(projects.id);// this is not correct.
$query = $this->db->get();
$quantity = $this->db->count_all_results();

Join query gives correct solution to your question.
#9

[eluser]Ener1[/eluser]
Hey suba,

Talking about a syntax error thanks a lot!!! I wrote it on the fly my bad ;-)

cheers




Theme © iAndrew 2016 - Forum software by © MyBB