+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: New to Joins and codeigniter (/showthread.php?tid=22219)
New to Joins and codeigniter - El Forum - 09-01-2009
[eluser]liam1412[/eluser]
I am new to joins; I have always coded the clumsy way with loads of queries, and using MVC this isn't so easy without having loads of code in your view.
I have 3 tables
categories
forums
posts
What I need is a multi dimensional array that would hold a list of categories and then within each category a list of forums that reside in that category and then within each forum, a post count/thread count.
With my old procedural way I can easy acheive what I want without using arrays, but with MVC I am struggling.
can anyone help me out.
Thanks from a newb to OOP/Mvc/COdeigniter all at once :-)
New to Joins and codeigniter - El Forum - 09-01-2009
[eluser]alboyd[/eluser]
This isn't a question about MVC - simply create your query no?
Code:
SELECT cats.category_name, f.forum_name, COUNT(p.post_id) FROM categories cats
JOIN forums f ON cats.category_id = f.category_id
JOIN posts p ON f.forum_id = p.forum_id
WHERE p.deleted IS NULL
GROUP BY cats.category_name, f.forum_nameObviously I have just completely made up what the field names would be but this would be the kinda idea of what you want isn't it?
New to Joins and codeigniter - El Forum - 09-01-2009
[eluser]jedd[/eluser]
First, post your schema rather than just your table names.
[quote author="liam1412" date="1251864552"]
What I need is a multi dimensional array that would hold a list of categories and then within each category a list of forums that reside in that category and then within each forum, a post count/thread count.
With my old procedural way I can easy acheive what I want without using arrays, but with MVC I am struggling.
[/quote]
I'd be fascinated to see how you juggled such hierarchical information absent a single array - can you post your extant code for pulling these data in?
New to Joins and codeigniter - El Forum - 09-01-2009
[eluser]liam1412[/eluser]
Here are the tables as requested.
forums
forum_id
forum_name
category_id
display_rank
forum_categories
category_id
category_name
category_text
display_rank
forum_posts
post_id
post_title
post_text
forum_id
response_to
time_posted
New to Joins and codeigniter - El Forum - 09-01-2009
[eluser]alboyd[/eluser]
[quote author="liam1412" date="1251885338"]Here are the tables as requested.
forums
forum_id
forum_name
category_id
display_rank
forum_categories
category_id
category_name
category_text
display_rank
forum_posts
post_id
post_title
post_text
forum_id
response_to
time_posted[/quote]
wow looks like my query would work straight out of the box for that schema
So was that helpful or totally not what you were asking?
New to Joins and codeigniter - El Forum - 09-01-2009
[eluser]liam1412[/eluser]
No it was exactly what I wanted, I modified it slightly to fit my schema but I get an empty result set, apart from all the other bumf codeign gives.
Code:
$data['result'] = $this->db->query('
SELECT cats.category_name, f.forum_name, COUNT(p.post_id) FROM forum_categories cats
JOIN forums f ON cats.category_id = f.category_id
JOIN forum_posts p ON f.forum_id = p.forum_id
GROUP BY cats.category_name, f.forum_name
');
$this->load->view('homepageview',$data);Gives
Code:
object(CI_DB_mysql_result)#12 (7) {
["conn_id"]=>
resource(28) of type (mysql link persistent)
["result_id"]=>
resource(29) of type (mysql result)
["result_array"]=>
array(0) {
}
["result_object"]=>
array(0) {
}
["current_row"]=>
int(0)
["num_rows"]=>
int(0)
["row_data"]=>
NULL
}New to Joins and codeigniter - El Forum - 09-01-2009
[eluser]alboyd[/eluser]
hey mate - break out the query in a query window in like Yog or whatever you use and figure it out from there.
You might need to change the join directions etc?
New to Joins and codeigniter - El Forum - 09-01-2009
[eluser]liam1412[/eluser]
I took out the posts bit but that doesn't seem like I would be able to use that in an easy way. It gives
Code:
object(CI_DB_mysql_result)#12 (7) {
["conn_id"]=>
resource(28) of type (mysql link persistent)
["result_id"]=>
resource(29) of type (mysql result)
["result_array"]=>
array(0) {
}
["result_object"]=>
array(5) {
[0]=>
object(stdClass)#13 (2) {
["category_name"]=>
string(16) "Off Topic Forums"
["forum_name"]=>
string(11) "Off Topic 1"
}
[1]=>
object(stdClass)#14 (2) {
["category_name"]=>
string(11) "Site Forums"
["forum_name"]=>
string(9) "Site News"
}
[2]=>
object(stdClass)#15 (2) {
["category_name"]=>
string(11) "Site Forums"
["forum_name"]=>
string(10) "Site Rules"
}
[3]=>
object(stdClass)#16 (2) {
["category_name"]=>
string(14) "Subject Forums"
["forum_name"]=>
string(15) "Subject Forum 1"
}
[4]=>
object(stdClass)#17 (2) {
["category_name"]=>
string(14) "Subject Forums"
["forum_name"]=>
string(15) "Subject Forum 2"
}
}
["current_row"]=>
int(0)
["num_rows"]=>
int(5)
["row_data"]=>
NULL
}I had another crud class where you could declare a relationship and it would bring this sort of result.
Code:
Array
(
[0] => Array
(
[cat_id] => 1
[cat_name] => Category 1
[cat_desc] => This is category 1
[display_rank] => 0
[private] => 0
[forum_forums] => Array
(
[0] => Array
(
[forum_id] => 2
[forum_cat_id] => 1
[forum_name] => Forum 1
[forum_desc] => This is forum 1
[display_rank] => 0
[lastpost_by] => 0
[lastpost_time] => 0
)
[1] => Array
(
[forum_id] => 5
[forum_cat_id] => 1
[forum_name] => This is another forum
[forum_desc] => This forum is about stuff
[display_rank] => 0
[lastpost_by] => 0
[lastpost_time] => 0
)
)
)
[1] => Array
(
[cat_id] => 2
[cat_name] => Category 2
[cat_desc] => This is category 2
[display_rank] => 0
[private] => 0
[forum_forums] => Array
(
[0] => Array
(
[forum_id] => 3
[forum_cat_id] => 2
[forum_name] => Forum 2
[forum_desc] => This is forum 2
[display_rank] => 0
[lastpost_by] => 0
[lastpost_time] => 0
)
)
)
[2] => Array
(
[cat_id] => 3
[cat_name] => Category 3
[cat_desc] => This is category 3
[display_rank] => 0
[private] => 0
[forum_forums] => Array
(
[0] => Array
(
[forum_id] => 4
[forum_cat_id] => 3
[forum_name] => Forum 3
[forum_desc] => This is forum 3
[display_rank] => 0
[lastpost_by] => 0
[lastpost_time] => 0
)
)
)
)With this I could use a foreach loop to get the categories, then within that foreach I could use a foreach to get the forums with that category.
Thanks for looking
New to Joins and codeigniter - El Forum - 09-02-2009
[eluser]garycocs[/eluser]
Hi Lads,
Sorry to thread hop here, I'm kinda new to the join queries as well and I was just wondering how you would write an Active Record query that lets say:
Got the information from one table and the averages of a number of rows from another table?